Почему 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

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


sql sql-server stored-procedures
person Victor Rodrigues    schedule 24.11.2008    source источник

Ответы (7)


arrow_upward
11
arrow_downward

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

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

person Ben R    schedule 24.11.2008
comment
В Oracle, если бы диапазон всегда был небольшим, все было бы хорошо. Он выберет план на основе первых переменных. Если он всегда маленький, у вас всегда все в порядке. ЭТО случаи, которые колеблются между крайностями. Я думаю, что СС недавно добавили что-то вроде подглядывания... может быть, '05 или '08. - person ; 25.11.2008

arrow_upward
27
arrow_downward

OK,

  1. Вы — оптимизатор, а план запроса — средство.
  2. Я дам вам запрос, и вы должны выбрать автомобиль.
  3. Все книги в библиотеке имеют порядковый номер

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

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

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

Иди в библиотеку и возьми все книги между @x и @y.

Выберите транспортное средство.

Вперед, продолжать.

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

person Community    schedule 24.11.2008
comment
обновлены сохраненные процедуры и добавлено option recompile в конец - person atoms; 07.10.2015
comment
Это прекрасное объяснение того, что Microsoft делает неправильно. Реальная жизнь: задайте значения переменных, а затем выберите подходящее транспортное средство. - person norgematos; 06.09.2016

arrow_upward
3
arrow_downward

Забавно, что этот код тоже будет быстрым:

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)

person DiGi    schedule 24.11.2008

arrow_upward
3
arrow_downward

Если эти переменные являются входными переменными для хранимого процесса, вы можете столкнуться с проблемой прослушивания параметров. http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

person HLGEM    schedule 24.11.2008

arrow_upward
1
arrow_downward

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

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

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

person Sam Saffron    schedule 18.12.2008

arrow_upward
0
arrow_downward

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

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

person Robert Wagner    schedule 24.11.2008

arrow_upward
0
arrow_downward

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

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

WHERE id BETWEEN @firstId and @lastId

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

person Community    schedule 22.07.2009