Параметризация запросов. Ваш лучший друг?

добавлено: 26 авг 12
понравилось:0
просмотров: 3486
комментов: 18

теги:

Автор: SamMan

Первая статья из цикла о параметризации запросов, описывающая "светлые стороны" данной технологии. Описывается общая идея параметризации и ее главная цель. Подробно и с примерами разбираются два автоматических способа ее реализации и три ручных. Указываются счетчики системного монитора с помощью которых можно следить за интенсивностью автоматической параметризации. Подробно освещаются сопутствующие технологии без которых параметризация теряет всякий смысл, вроде кэширования планов и их повторного использования. Приводятся примеры ожидаемого эффекта от внедрения параметризации запросов для готового решения, а так же дается предупреждение о возможной двойственности такого эффекта. Уровень материала - 300.

Перейти к статье.

Комментарии


  • Спасибо, что систематизировали и изложили на русском.
    Неплохо бы указать источники. Вот тут почти все ваши эксперименты.
    http://daleburnett.com/2011/08/how-to-tell-if-your-query-has-been-auto-parameterized-and-why-wasnt-it/
    http://daleburnett.com/2011/08/spy-vs-spy-or-auto-parameterization-vs-forced-parameterization/
    http://daleburnett.com/2011/08/forced-parameterization/

    Вот тут пояснения разработчиков и документация:
    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx
    http://msdn.microsoft.com/en-us/library/ms175037(SQL.105).aspx

    Не ленитесь добавлять такие ссылки. Удачи.

  • Сори, пишу с прокси, с перекрытого инета.
    Ссылки вот на эти ресурсы daleburnett.com и SQL Programmability & API Development Team Blog, поправьте, если можно.

  • Ну, как бы пока не вижу "поинта" в добавлении таких ссылок. Что вы хотите ими подчеркнуть? Что тему можно продолжить изучать и дальше? Или просто копирайт зафиксировать? Так я считаю свои статьи полностью авторскими, это не перевод, не пересказ, не изложение текста другой статьи в иной стилистике. Это оригинальный текст. То что "мысли сходятся" - ну тут извините, SQL Server один на всех. Похожий код дает идентичные результаты что ведет к аналогичным выводам. У того же daleburnett.com (раз уж вы его помянули) мысли и выводы "подозрительно" похожи на таковые с blogs.msdn.com - и ничего, вполне нормально.

  • Я объясню поинт развернуто, если вы хотите.
    Например, читая про список фич когда запросы не параметризуются, мне хочется видеть либо подтверждение кодом каждого пункта, либо ссылку где вы это прочитали. Иначе я склонен думать что это ваши домыслы. К этому подталкивает их огромное количество в статье.

    Например: "В общем, прикинув так и эдак, создатели SQL Server сделали однозначный выбор..." - вы залезли в голову к разработчикам? вы сами разработчик? Если нет, то почему вы преподносите это не в виде догадки, а в виде исторического факта?

    Или вот: "В этом случае оптимизатор сходу обнаруживает соответствующий shell-план и ему не нужно заниматься параметризацией запроса и не нужно проводить в кэше второй поиск для этого параметризированного варианта."
    Есть в программировании такой оператор "сходу"? Я-то всегда думал, что оптимизатору нужно сделать анализ и выполнить поиск в кэше, разница в том, что сам план строить не нужно.

    Или вот: "Дело тут в том, что счетчик последнего считает не исполнения, а число shell-планов содержащих ссылку на него". Ок, для опровержения, в вашем же примере, при помощи dbcc freeproccache(@planhandle) уберите из кэша все шел планы ссылающиеся на реальный и посмотрите, уменьшится ли счетчик? Я вам скажу что нет, не уменьшится, и все потому, что он считает не ссылки, а то, что отражает название - use count - кол-во использований, в данном случае, сколько раз он был использован при построении другого плана. "Ссылки" - очередной домысел представленный в виде факта, причем неверный.

    Вот это: "Во-вторых, и что еще более важно, характер и общая «заточенность» хранимой процедуры sp_executesql предполагают что у нас есть статическая строка запроса и статическая же строка с именами/типами параметров для него." С чего вы взяли, что у этой процедуры есть какая-то заточенность? Опять варианты, либо вы из команды разработки, либо прочитали где-то, либо вам так кажется. И кажется вам неверно. В вашем же эксперименте сравнения с эдхок запросом из первой части, замените запрос в sp_executesql на
    SET @query1 = N'SELECT ColID, Col2, Col3 FROM T1 WHERE Col3=''ab'''
    SET @query2 = N'select ColID, Col2, Col3 FROM T1 WHERE Col3=''ab'''
    и посмотрите. Все прекрасно параметризуется, никакая псевдо "заточенность" не мешает.

    Касательно индекса, тоже вопрос. Почему так важен факт его наличия, а не его форма и форма запроса? Сделайте либо индекс, либо запрос покрывающим - удидите, что все прекрасно параметризуется и при включенном индексе.
    create nonclustered index iT1 on T1 (Col3) include (Col2);
    Либо сделайте индекс уникальным:
    create unique nonclustered index iT1 on T1 (Col3);
    Увидите что (сюрприз) все параметризуется.
    Это то что бросается в глаза с первого взгляда.

    Таким образом, если убрать все домыслы, рассуждения и воду (некоторые из которых ошибочны) - имхо, будет материал, чуть более чем полностью совпадающий с приведенными выше ссылками. По этому, я просто попросил их привести, ведь соль материала - "если мы видим в плане литералы, замененные параметрами, это не значит что запрос параметризован" - достаточно интересна и нетривиальна, но не всем может быть охота фильтровать факты от домыслов.
    Надеюсь, вы не обижаетесь на критику.

  • >>Например, читая про список фич когда запросы не параметризуются,...либо ссылку где вы это прочитали.
    Вот В ЭТОМ месте - согласен, линк на официальный список не помешал бы...

    >>К этому подталкивает их огромное количество в статье.
    Их - кого? Домыслов? Хм...

    >>а в виде исторического факта?
    Ну, то что выбор был сделан и именно такой это факт абсолютный, или вы имеете иную его интерпретацию?

    >>не в виде догадки
    Потому что как я это вижу - это ФАКТ. Если имеются альтернативные объяснения - излагайте. Кроме того, никто не говорит что в моих статьях нет и не может быть ошибок (и я буду благодарен на указание конкретных ТЕХНИЧЕСКИХ своих "ляпов" типа "код не работает" или "резалт-сет не совпадает с показанным") - уж на что BOL проверяют-перепроверяют, а и то ошибок полно...

    >>в данном случае, сколько раз он был использован при построении другого плана
    Я имел в виду именно это, но пытался донести эту мысль с помощью термина "ссылка". Если получилось коряво - простите. Кстати - а где можно ознакомится с вашими, "рассово-выверенными" техническими текстами? У меня ж перед глазами нет примера для подражания вот и лажаю порой...

    >>"Ссылки" - очередной домысел
    Shell-планы НЕ являются ссылками? Уверены?

    >>С чего вы взяли, что у этой процедуры есть какая-то заточенность?
    Потому что это мое МНЕНИЕ, а блог - авторский (это если вы не заметили), для ЕГО высказывания. Не согласны с мнением? Доказывайте! А еще лучше обоснуйте свое контр-мнение в контр-статье с контр-примерами. С коей, опять же, я с удовольствием ознакомлюсь. И, полагаю, не только я...

    >>В вашем же эксперименте сравнения с эдхок запросом из первой части, замените запрос в sp_executesql на
    Ничего не понял - кто на ком стоял? Приведите полный текст запроса что я должен запустить/просмотреть!

    >>Почему так важен факт его наличия
    У... Так вы еще и читаете не внимательно. :( Важен не индекс, а наличие (из-за него!) альтернатив в построении плана. Что и подчеркивается специально в тексте.

    >>прекрасно параметризуется и при включенном индексе
    Странно, да? И как вы это объясняете?

    >>либо сделайте индекс уникальным
    И тоже все заработает?? Что вы говорите... Так а от меня, конкретно, ВОТ В ЭТОМ месте - вы что хотите?

    >>но не всем может быть охота фильтровать факты от домыслов
    Всем "охочим до фактов" рекомендую читать BOL, BOL и ничего кроме BOL-а. Любая не-BOL статья это по определению рассуждения/обсуждения/мысли автора/его догадки(не обязательно, но совершенно не исключено, по крайней мере никто не может запретить их ему высказывать).

    >>Надеюсь, вы не обижаетесь на критику.
    На критику - ни разу, только на критиканство. Давайте КОНКРЕТНЫЕ примеры ТЕХНИЧЕСКИХ ошибок статьи - "написано, что работает так - а оно вот так". Ну или хотя бы на пару синтаксических ошибок укажите, как другие читатели делают - и то польза.

  • А - и, да:
    >>...если убрать все домыслы, рассуждения и воду...
    ...из любой не-BOL статьи, то получится BOL-статья. Со столь любимыми вами фактами. Если не секрет - что заставило вас оторваться от увлекательного BOL-чтения?

  • Если вы объясняете ваши домыслы в виде фактов тем, что «блог авторский – пишу что хочу», то я вынужден и относится к нему также, читать как сказку. Если вы претендуете на другое то, имхо, было бы неплохо авторские мысли обрамлять фразами: «я думаю это потому что…», «я полагаю что…», «мне кажется…» и т.д.
    Примеры хороших статей – я вам в самом первом посте привел линки.
    Конкретные технические ляпсусы, о которых вы возопите, я вам уже озвучил, причем несколько. Если вы не поняли о чем я, перечитайте свою статью и коммент. Повторно объяснять не буду, а тем более вылавливать все ляпсусы, а не только очевидные. Это уже тянет на хорошее ревью и заниматься этим у меня нет никакой мотивации.
    А отвлекся от чтения других материалов я только потому, что после таких вот статей, как раз и появляется куча мифов типа «sp_executesql заточен под что-то» или «use count хранит либо счетчик использований, либо количество ссылок на план из других планов» и т.д. Мои комменты призваны обратить на это внимание, и, кстати, спасибо, что вы их не трете.

  • Наконец-то восстановил нормальный инет.

  • Это пропустил:
    >>прекрасно параметризуется и при включенном индексе
    >Странно, да? И как вы это объясняете?

    У меня есть объяснение, точнее предположение, но я его высказывать не хочу, потому что это именно что предположение (правда частично подтвердилось экспериментально).

  • >>Примеры хороших статей – я вам в самом первом посте привел линки.

    Так, подождите... у меня в глазах двоится. Первый пост у нас от hotbitsql, развитие сюжета - от него же. Вы, насколько я вижу - SomewhereSomehow. Кто вы? Сколько вас? Я с кем сейчас говорю?

  • Я ж написал в первом посте: "Сори, пишу с прокси, с перекрытого инета." Только к обеду вчера все наладили и я смог нормально отвечать и на форуме, и тут.

    А сюжет, собственно, простой, очень уж лихо вы подменяете факты своими догадками, чем сами же порождаете мифы. Про "разработчики подумали и решили", "число ссылок" и "заточенность sp_executesql" - имхо, вовсе уж феерично.

  • Хорошо, собеседник один, я понял...

    >>авторские мысли обрамлять фразами: «я думаю это потому что», «я полагаю что», «мне кажется»

    Не нужно, потому что объем статей и так не детский, а то что любая фраза ПРЕДПОЛАГАЕТ такой "довесок" - самоочевидно. Любой автор излагает факты, и ему они кажутся таковыми, пока не будет ему носом ткнуто, что он ошибается. И, да - что бы значительно сократить объем нашей переписки - я понял, что выбранная мной стилистика подачи текста Вам - не нравтся. Принимаю как факт - не нравится. Примите и вы как факт - менятся она не будет, потому что это мой выбор для МОЕГО блога. Точно так же, стилистика BOL не нравится очень и очень многим (мне в том числе). И ничего, читают/читаю. И не требую у авторов BOL их стиль менять - безполезно, а и, скорее всего, (да - снова мои домыслы, я люблю мыслить, знаете ли) они по другому и не умеют. Так вот я - определенно не лучше их.Так что предлагаю переключится на ИСКЛЮЧИТЕЛЬНО технические вопросы. Пишите - какой код работает не так как указано, и, если не затруднит, пишите сразу в чем была ТЕХНИЧЕСКАЯ ошибка автора, то есть моя.

    >>Конкретные технические ляпсусы, о которых вы возопите, я вам уже озвучил, причем несколько.

    Еще раз, ваш сумбурный текст в полтора предложения не является никаким озвучанием. А является тем чем он является, комком невнятных мыслей. Если хотите не критиканствовать, а действительно помочь автору в исправлении техн. ошибок - возьмите, будьте любезны, на себя некий труд. Оформите код, желательно запускабельный, который продемонстрирует ошибку. По возможности (если момент тонкий) опишите чем вызван "фейл" автора, что он понимает не так. В общем, учитесь дискутировать технически и "на уровне". Как это делают иные посетители блога.

    >>Это уже тянет на хорошее ревью и заниматься этим у меня нет никакой мотивации.

    Я, почему-то, это предполагал. Не подскажете, из каких источников черпают подобную мотивацию иные читатели моего блога? Может у вас с ними изначально цели разные? Собственно - ваше право оставить меня с моим блогом и с моими ошибками. Но уж тогда, по крайней мере, будьте последовательны - оставьте вообще ВСЕ как есть. Ну то есть решите уже, что вас это не касается. Или уж решите что касается. А то мотивация фигачить пол-экранные комменты "о воде и вообще..." у вас странным образом обнаруживается. Ваше с позволения сказать "ревью" было бы вчетверо короче, а пользы от него - на порядок больше.

    >>что после таких вот статей, как раз и появляется куча мифов

    С моей точки зрения, мифов становится меньше. И - да, я понял, что вы считаете иначе. Спасибо за мнение. Еще раз говорю, примите тот факт, что моя стилистика ВАМ - не подходит. Вы слишком прониклись "шершавой строкой" BOL-а. Вы просто не понимаете что SQL-литература может быть без "прадного мундира" и оставаться при этом вполне серьезной.

    >>и, кстати, спасибо, что вы их не трете

    Упаси Бог! Не стер ни одного коммента ни на sql.ru, ни на sqlCMD.ru. Последние, кстати говоря, могут оказаться ВАМ полезными, если вы меня понимаете...

    >>У меня есть объяснение, точнее предположение, но я его высказывать не хочу.

    Вот в этом между нами и разница. Вы не хотите высказывать свои идеи/догадки, потому что... а собственно - не важно почему. Не хотите, и точка. А я - хочу. И высказываю. И у меня с читателями из-за того получаются довольно интересные диалоги-дискуссии и даже споры. Надеюсь, от того факта что нам с ними хорошо и интересно вам хуже не становится?

    P.S. Напоминаю, что с этого поста все НЕ ТЕХНИЧЕСКИЕ вопросы/замечания от SomewhereSomehow/hotbitsql мною игнорируются. Без обид, но в этом аспекте (стилистика, оформление мыслей и пр.) нам сойтись не суждено...

  • Утверждение vs Предположение не вопрос стиля. Жаль, если вы не поняли. Также я не могу отвечать за мотивацию ваших читателей. Цели я свои озвучил выше, опять же не знаю, насколько они совпадают с целями ваших читателей.

    >Еще раз, ваш сумбурный текст в полтора предложения не является никаким озвучанием

    Я не думал, что нужно вам все разжевывать. Но раз вы настаиваете.

    >Дело тут в том, что счетчик последнего считает не исполнения, а число shell-планов содержащих ссылку на него.

    Допустим, что это так. Тогда, при уменьшении числа shell-планов, счетчик должен уменьшаться (он ведь считает, сколько шел планов содержит ссылку). Проведем простой эксперимент. Выполним самый первый запрос с отключенным индексом. Узрим 4 строки:
    Adhoc 1, Adhoc 3, Adhoc 1, Prepared 3. При помощи dbcc freeproccahce(@plan_handle), удалим sell планы, и выполним запрос к кэшу. Ожидаемое значение счетчика для строки Prepared либо 0, либо этой строки вообще нет, т.к. удален последний ссылавшийся на нее план. Результат Prepared 3. Следовательно, допущение неверно. Я надеюсь, вы это сможете проделать сами, без того чтоб я копипастил код?

    >Помните наш эксперимент в первой части с двумя SELECT-ами набранных в различных регистрах? (…) Попробуем тоже самое с обсуждаемой хранимой процедурой(…)Увы, но это будут два разных плана, никакой нормализации текста запроса не случается.(…)

    Давайте заменим в этом эксперименте это
    SET @query1 = N'SELECT ColID, Col2, Col3 FROM T1 WHERE Col3 IN (@my_param1, @my_param2)'
    SET @query2 = N'select ColID, Col2, Col3 FROM T1 WHERE Col3 IN (@my_param1, @my_param2)'
    SET @param = N'@my_param1 varchar(2), @my_param2 varchar(2)'
    EXEC sp_executesql @query1, @param, @my_param1 = 'ab', @my_param2 = 'ef'
    EXEC sp_executesql @query2, @param, @my_param1 = 'ab', @my_param2 = 'ef'

    На это:
    SET @query1 = N'SELECT ColID, Col2, Col3 FROM T1 WHERE Col3=''ab'''
    SET @query2 = N'select ColID, Col2, Col3 FROM T1 WHERE Col3=''ab'''
    EXEC sp_executesql @query1
    EXEC sp_executesql @query2

    Результат: Adhoc 1,Adhoc 1,Prepared 2
    Как вы видите, на параметризацию не повлияло то, что запрос был выполнен в некой заточенной процедуре sp_executesql.

    На тему индекса я уже высказался. Касательно «возьмите на себя труд» - с чего вдруг? Я бы мог, если б это было не интересно, но увы.

    > я понял, что выбранная мной стилистика подачи текста Вам - не нравтся
    > Еще раз говорю, примите тот факт, что моя стилистика ВАМ - не подходит

    Это снова ваш домысел. Например, вот эту статью я прочитал с удовольствием, и, думаю, еще к ней буду обращаться по мере надобности: «Как перестать называть журнал транзакций SQL Server лог-файлом и прекратить борьбу за его размер».

  • >>Утверждение vs Предположение не вопрос стиля.
    Ignore.

    >>Я не думал, что нужно вам все разжевывать.
    Разумеется, надо ворваться как комета, намекнуть что все не правильно, и ошибок куча, и вообще оно как-то... Нет, уважаемый вы наш, критика это тоже труд. Это критиканство отдых.

    >>Дело тут в том, что счетчик последнего считает не исполнения, а число shell-планов содержащих ссылку на него.
    Во втором же своем ответе я признал свою корявость данного определения, и сказал, что имелся в виду подсчет ссылок от шелл-планов КОГДА-ЛИБО использоваших данный Prepared - план. Вам мало этого признания? Что вы хотите? Не знаете? А я знаю. Я хочу прочесть ХОТЯ бы данный абзац в вашем изложении. Ну просто что бы было с чем сопоставить.

    >>Помните наш эксперимент в первой части с двумя SELECT-ами
    1. В ПРИМЕРЕ СТАТЬИ ошибки нет. Указанный пример работает так как он описан, движок считает команды разными.
    2. Ваш альтернативный пример понятен, но общий раздел, куда входит "проблемный" (с вашей точки зрения) под-раздел "Параметризация ручная, с помощью sp_executesql." посвящен РУЧНОЙ параметризации. То есть я, как автор текста, произвольно и можно сказать волюнтаристски постановил: вот с этого места статьи мы рассматриваем ТОЛЬКО(!!) РУЧНУЮ(!!) параметризацию. Просто потому, что статья должна же когда-то заканчиваться. Ваш любимый БОЛ, кстати, черезвычайно выдержан в этом смысле - текст идет прямой как рельс, ни малейших отклонений от заданной темы. Вам это должно импонировать. Так вот, задание: показать в вашем альтернативном коде подчеркивающем, как вы считаете, мою ошибку, то его место, где программист данного кода занимается РУЧНОЙ параметризацией.

    >>На тему индекса я уже высказался.
    Я же, со своей стороны, ответил.

    >>Касательно «возьмите на себя труд» - с чего вдруг?
    Со "вдруг", что есть правила КОНСТРУКТИВНОЙ дискусси. А так же правила для людей желающих что-то улучшить, например чужой блог/текст. Это критиканство растет как бурьян под забором, в любую сторону.

    >>Я бы мог, если б это было не интересно
    Если вам это НЕ интересно, и мой блог вам НЕ интересен - с чего такое пристальное внимание и комментарии по пол-экрана? Вы противоречите. Причем сами себе.

    >>думаю, еще к ней буду обращаться по мере надобности
    Ваше право, хотя, как мне представляется, это будет довольно странно. В указанной статье есть все тоже самое, что вам так не нравится. Вы же не думаете, что за полгода я стал писать совсем в ином ключе, стиле, "разделять Утверждение vs Предположение" (как вы это называете) и все такое? Да нет - как писал, так и пишу. Как умею. И, да - как МНЕ нравится.

  • SamMan
    Я не буду придираться к словам. Вы во втором ответе приняли что сваляли херню (корявость). Но "подсчет ссылок от шелл-планов КОГДА-ЛИБО использоваших данный Prepared - план" - тоже неверно, увы!
    Мне пох на признание. Абзац в моем изложении?...возможно, я думаю над этим.
    По второму пункту, оправдания выглядят жалкими. После того как я указал на них. Вы скатываетесь в нюансы, тогда как в оригинале их не было. Резюмируя - sp_executesql - никак не "заточена". Face it!!!

    >Если вам это НЕ интересно, и мой блог вам НЕ интересен (...)
    Сказал же. В целом, интересен. У вас хорошие статьи.

    >Ваше право, хотя, как мне представляется, это будет довольно странно. В указанной статье есть все тоже самое, что вам так не нравится. Вы же не думаете, что за полгода я стал писать совсем в ином ключе, стиле, "разделять Утверждение vs Предположение" (как вы это называете) и все такое? Да нет - как писал, так и пишу. Как умею. И, да - как МНЕ нравится

    Повторяю!!! Мне нравятся ваши статьи. Но пишите про то что вы сами(!!!) знаете и с чем работаете. Не надо пересказывать чужие статьи со своими комментами (Да я верю что статьи авторские, но осадочек будь здоров). Хрен ли писать про оптимизатор, если вы не понимаете как он работает? Расскажите мне про LPE. Что это? Как оно работает? Является ли частью qp? Или это независимый компонент? Что представляет из себя план в реале? Что раньше: упрощение, тривиальный план или lpe? А может lpe часть qp?!

  • >>Вы во втором ответе приняли что сваляли херню
    Следующий ваш коммент обязан быть с извинениями. До тех пор Вы, молодой человек, отправляетесь по известному Вам адресу, а ваши комменты (без извинений) временно блокируются (т.е. будут удаляться). Хамить и разговаривать "по понятиям" будете в другом месте и с другими людьми.

  • Ну если Вас эта безобидная фраза как-то обидела, то искренне извиняюсь, у меня не было цели Вас обижать.
    А коментов от меня больше не будет, ибо чувствую что бьюсь головой о стену, бесполезно.

  • >>то искренне извиняюсь,
    OK, принято.

    >>ибо чувствую что бьюсь головой о стену, бесполезно
    Да, будет лучше если каждый из нас просто пойдет своей дорогой...



Необходимо войти на сайт, чтобы оставлять комментарии