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

Более вероятно, что ваша индексация (или ее отсутствие) вызывает медлительность, а не динамический SQL.

Как выглядит план выполнения? Является ли тот же запрос медленным при выполнении в SSMS? А как насчет хранимой процедуры?

Если ваша таблица представляет собой неиндексированную кучу, она будет работать плохо по мере роста количества записей — это не зависит от запроса, и динамический запрос может фактически работать лучше, поскольку природа таблицы изменяется, потому что динамический запрос, скорее всего, будет иметь свой запрос. план переоценивается, когда его нет в кеше. Обычно это не проблема (и я бы не классифицировал это как конструктивное преимущество динамических запросов), за исключением ранних стадий системы, когда SP не были перекомпилированы, а статистика и планы запросов устарели, а объем данные просто резко изменились.

Еще не статика. У меня с динамическим запросом, но он не дает никаких оптимизаций. Если бы я запустил его со статическим запросом, и он дал бы предложения, повлияет ли их применение на динамический запрос? – Xaisoft (41 мин. назад)

Да, динамический запрос (EXEC (@sql)) вероятно не будет проанализирован, если вы не проанализировали файл рабочей нагрузки. — Кейд Ру (33 минуты назад)

Когда у вас есть поисковый запрос по нескольким объединенным таблицам, столбцы с индексами должны быть столбцами поиска, а также столбцами первичного/внешнего ключа, но это зависит от мощности различных таблиц. Анализатор настройки должен показать это. — Кейд Ру (22 минуты назад)

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

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

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

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

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

1) Добавьте WITH RECOMPILE в определение SP, что приведет к созданию нового плана при каждом выполнении. Это включает в себя некоторые накладные расходы, которые могут быть приемлемыми.

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

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

Единственная разница между «динамическим» и «статическим» SQL заключается в фазе синтаксического анализа/оптимизации. Как только это будет сделано, запрос будет выполняться идентично.

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

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

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

Иногда денормализация может выполняться «во время выполнения» в приложении с использованием, например, кэшированных таблиц поиска, а не в базе данных.

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

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

Могут быть лучшие способы создания динамического 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)

Здесь есть несколько хороших примеров запросов с необязательными критериями поиска: search-columns">Как мне создать хранимую процедуру, которая будет дополнительно искать столбцы?

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

WHERE col2 = @col2 AND col1 = @col1

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

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

Мне нравится Дэйва Кемпа. ответ выше.

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

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

Если вы пытаетесь оптимизировать до диапазона ниже 1 с, может быть важно приблизительно оценить, сколько времени требуется для синтаксического анализа и компиляции динамического sql относительно фактического времени выполнения запроса:

 SET STATISTICS TIME ON;

а затем выполните динамическую строку SQL «статически» и проверьте вкладку «Сообщения». Я был удивлен этими результатами для динамического SQL-запроса ~ 10 строк, который возвращает две строки из таблицы строк 1M:

 SQL Server parse and compile time: 
    CPU time = 199 ms, elapsed time = 199 ms.

 (2 row(s) affected)

 SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 4 ms.

Оптимизация индекса вряд ли значительно сдвинет барьер в 199 мс (за исключением, возможно, некоторого анализа/оптимизации, включенного во время компиляции).

Однако, если динамический SQL использует параметры или повторяется, то результаты компиляции могут кэшироваться в соответствии с: см. планы кэширования запросов что сократит время компиляции. Было бы интересно узнать, как долго живут записи в кеше, размер, совместное использование между сеансами и т. д.