Почему SQL Server работает медленнее при использовании переменных?

У меня есть запрос sql, который работает очень быстро, около одной секунды, когда не использует переменные, например:

WHERE id BETWEEN 5461094 and 5461097

Но когда у меня есть:

declare @firstId int
declare @lastId int

set @firstId = 5461094
set @lastId = 5461097

...
    WHERE id BETWEEN @firstId and @lastId

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

Ответы

Ответ 1

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

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

Ответ 2

ОК,

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

Мой запрос: Идите в библиотеку и получите мне все книги между 3 и 5

Вы бы выбрали велосипед правильно, быстро, дешево, эффективно и достаточно, чтобы отнести 3 книги.

Новый запрос.

Перейдите в библиотеку и получите все книги между @x и @y.

Выберите автомобиль.

Вперед.

Что происходит. Вы выбираете самосвал на случай, если я попрошу книги между 1 и Maxvalue? Это переполнение, если x = 3 и y = 5. SQL должен выбрать план, прежде чем он увидит числа.

Ответ 4

Смешно, что этот код тоже будет быстрым:

DECLARE @sql VARCHAR(8000)

SET @sql = 'SELECT * FROM table_x WHERE id BETWEEN ' + CAST(@firstId AS VARCHAR) + ' AND ' + CAST(@lastId AS VARCHAR)

EXEC (@sql)

(MSSQL 2000)

Ответ 5

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

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

Попробуйте включить параметр WITH RECOMPILE на сохраненном proc. Если он решает проблему, и вы довольны тем, что процесс перекомпилируется каждый раз, когда он выполняется (вы получите удар производительности), оставьте его там. Если вы все еще недовольны работой, рассмотрите вопрос о повторной архитектуре proc, чтобы она не нуждалась в перекомпиляции.

Ответ 6

Идентификатор в индексе (например, первичный ключ)? Если нет, попробуйте добавить его.

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

Ответ 7

На самом деле, на него ответили очень хорошо, я просто напишу об этом обходное решение, так как это сработало для меня:

Создать хранимую процедуру с помощью SQL

WHERE id BETWEEN @firstId and @lastId

После этого вызовите сохраненную процедуру с параметрами @firstId и @lastId, и она ускорится. Я все еще не 100%, почему он работает, но он работает.