Действительно ли динамическая sql хранимая процедура является плохой вещью для большого количества записей?

Я был бы точно так же, как, чтобы указать на это, если Вы используете этот стиль дополнительных параметров:

AND (@EarliestDate is Null OR PublishedDate < @EarliestDate)

Оптимизатор запросов будет понятия не иметь, является ли параметр там или не, когда он производит план запросов. Я видел случаи, где оптимизатор делает плохой выбор в этих случаях. Лучшее решение состоит в том, чтобы создать sql, который использует только параметры, в которых Вы нуждаетесь. Оптимизатор сделает самый эффективный план выполнения в этих случаях. Обязательно используйте параметризированные запросы так, чтобы они были допускающими повторное использование в кэше плана.

Единственной разницей между "динамическим" и "статическим" SQL является фаза парсинга/оптимизации. После того как они сделаны, запрос будет работать тождественно.

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

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

Я сфокусировался бы на оптимизации самого запроса, включая, возможно, денормализацию, если Вы чувствуете, что это является соответствующим, хотя я не сделал бы, это на первом обходит меня.

Иногда денормализация может быть сделана во "время выполнения" в приложении с помощью кэшируемых справочных таблиц, например, вместо того, чтобы поддержать этот o база данных.

Как предыдущий ответ, проверьте свои индексы и план.

Вопрос состоит в том, используете ли Вы хранимую процедуру. Это не очевидно из способа, которым Вы сформулировали его. Хранимая процедура создает план запросов, когда выполнено и сохраняет тот план, пока не перекомпилировано. С переменным SQL можно застрять с плохим планом запросов. Вы могли сделать несколько вещей:

1) Добавьте С, ПЕРЕКОМПИЛИРОВАЛИ к определению SP, которое заставит новый план быть сгенерированным с каждым выполнением. Это включает немного служебные, который может быть приемлемым.

2) Используйте отдельный SP, в зависимости от обеспеченных параметров. Это позволит лучшее кэширование плана запросов

3) Используйте клиент, сгенерированный SQL. Это создаст план запросов каждый раз. При использовании параметризированных запросов это может позволить Вам использовать кэшируемые планы запросов.

Не вентилятор динамического Sql, но если Вы застреваете с ним, необходимо, вероятно, прочитать эту статью: http://www.sommarskog.se/dynamic_sql.html Он действительно идет подробно на лучших способах использовать динамический SQL и проблемы с помощью него, может создать.

Как другие сказали, индексация является наиболее вероятным преступником. В индексации одна вещь, которую люди часто забывают делать, помещается индекс на полях FK. Так как PK создает индекс автоматически, многие предполагают, что FK будет также. К сожалению, создание FK делает nto, создают индекс. Поэтому удостоверьтесь, что индексируются любые поля, на которых Вы присоединяетесь.

Могут быть лучшие способы создать Ваш динамический SQL, но не видя код трудно сказать. Я, по крайней мере, надеялся бы видеть, использует ли это подзапросы, и замените их полученными соединениями таблицы вместо этого. Также любой динамический SQL, который использует курсор, обязан быть медленным.

Если параметры являются дополнительными, прием, это часто используется, должен создать процедуру как это:

CREATE PROCEDURE GetArticlesByAuthor (
    @AuthorId int,
    @EarliestDate datetime = Null )
AS
   SELECT  * --not in production code!
   FROM Articles
   WHERE AuthorId = @AuthorId
   AND (@EarliestDate is Null OR PublishedDate < @EarliestDate)

Существуют некоторые хорошие примеры запросов с дополнительными критериями поиска здесь: Как я создаю хранимую процедуру, которая будет дополнительно искать столбцы?

Как отмечено при выполнении значительного запроса Индексы являются первым узким местом, которое посмотрит на. Удостоверьтесь, что в большой степени запрошенные столбцы индексируются. Кроме того, удостоверьтесь, что Ваш запрос проверяет все индексируемые параметры, прежде чем он проверит неиндексируемые параметры. Это удостоверяется, что результаты отфильтровываются с помощью индексов сначала, и затем делает медленный линейный поиск, только если он имеет к. Таким образом, если col2 индексируется, но col1 не, это должно посмотреть следующим образом:

WHERE col2 = @col2 AND col1 = @col1

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

Я избегаю динамических запросов, если я могу по двум причинам. Один, они не сохраняют план запросов, таким образом, оператор компилируется каждый раз. Другой то, что ими трудно управлять, протестируйте, и поиск и устранение неисправностей. (Они просто выглядят ужасными).

Мне нравится ответ Dave Kemp выше.

У меня был некоторый успех (в ограниченном количестве случаев) со следующей логикой:

CREATE PROCEDURE GetArticlesByAuthor (    
    @AuthorId int,    
    @EarliestDate datetime = Null 
    ) AS   

SELECT SomeColumn
FROM Articles   
WHERE AuthorId = @AuthorId   
AND @EarliestDate is Null
UNION
SELECT SomeColumn
FROM Articles   
WHERE AuthorId = @AuthorId   
AND PublishedDate < @EarliestDate