sp_executesql работает медленно с параметрами

Я использую dapper-dot-net в качестве ORM, и он создает следующий медленно выполняющийся (1700 мс) код SQL.

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (@id1,@id2) AND SensorId = @sensor AND SensorValue != -32768 AND SensorValue != -32767',N'@id1 bigint,@id2 bigint,@sensor int',@id1=139,@id2=726,@sensor=178

Когда я изменяю этот код, удаляя параметры, запрос выполняется невероятно быстро (20 мс). Должно ли отсутствие этих параметров иметь такое большое значение и почему?

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (139,726) AND SensorId = 178 AND SensorValue != -32768 AND SensorValue != -32767'

sql-server dapper
person m__    schedule 07.06.2012    source источник
comment
Насколько большой стол? Какие у него индексы?   -  person Philip Kelley    schedule 07.06.2012
comment
Я ответил на аналогичный вопрос здесь.   -  person Christoffer Lette    schedule 18.03.2013

Ответы (2)


arrow_upward
33
arrow_downward

Добавьте OPTION (RECOMPILE) в конец

... AND SensorValue != -32767 OPTION (RECOMPILE)

Я подозреваю, что вы испытываете "обнюхивание параметров"

Если это так, мы можем оставить ВАРИАНТ или рассмотреть альтернативы.

Обновление 1

В следующей статье вы познакомитесь с "анализом параметров" http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

Я советую вам узнать все входы и выходы, потому что это поможет вам лучше понять внутренности сервера sql (это может укусить).

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

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

Обновление 2

Лучшее, что я прочитал на эту тему, было в главе 32 под названием "Обнюхивание параметров: ваш лучший друг... за исключением случаев, когда это не автор" ГРАНТ ФРИЧИ.

Это рекомендуется.

Подробное изучение SQL Server MVP, том 2

person buckley    schedule 07.06.2012
comment
Конечно, что-то связанное с индексацией. - person Philip Kelley; 07.06.2012
comment
похоже, это помогло, мне нужно сохранять это для каждого исключения или только один раз, чтобы перекомпилировать его? (приму ваши ответы после того, как проведу дополнительные тесты, спасибо) - person m__; 07.06.2012
comment
Я лично оставляю опцию перекомпиляции в моем sql, если нет проблем с производительностью. Вы имели в виду, что обычно не используете опцию перекомпиляции, если нет проблем с производительностью? Я не думаю, что это хорошая практика (не говоря уже о совете) всегда использовать OPTION RECOMPILE по умолчанию, если это не вызывает проблемы. - person Aaron Bertrand; 07.06.2012
comment
@AaronBertrand Приятно снова тебя слышать. Как только я диагностирую, что основной причиной является сниффинг, я добавлю опцию перекомпиляции, проведу несколько стресс-тестов, и если производительность не ухудшится, я оставлю опцию перекомпиляции. Если повторная компиляция является проблемой, я перепишу ее, используя известные методы (вы помните), которые заставляют оператор обычно терять свое предназначение и делают его менее удобным для сопровождения. Я не начинаю с опции перекомпиляции, если вы так думали. Кэширование планов выполнения — это вообще хорошо. - person buckley; 07.06.2012
comment
@buckley Спасибо за разъяснение. Я бы уточнил это в ответе. Я так понял, что не всегда пишу запросы, но когда пишу, использую OPTION RECOMPILE. - person Aaron Bertrand; 07.06.2012
comment
@AaronBertrand Переписал последний абзац - person buckley; 07.06.2012
comment
Спасибо за ссылку на сниффинг параметров. Блог указал на возможность создания руководства плана. Это решило это для меня, поскольку я не мог изменить сам запрос. - person kinstephen; 12.03.2015

arrow_upward
8
arrow_downward

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

Это улучшило время выполнения SQL, но когда dapper выполнял запрос, он все еще был медленным, фактически истекал тайм-аут.

Затем я понял, что запрос, сгенерированный dapper, передавал параметр как nvarchar(4000), где, поскольку мой столбец таблицы БД был varchar(80), это заставляло его выполнять сканирование индекса вместо поиска (Я предлагаю вам прочитать об индексах, если это не имеет для вас смысла.). поняв это, я обновил свой щеголеватый оператор where следующим образом:

ГДЕ Ссылка = convert(varchar(80),@Reference)

Выполнение с оператором where, приведенным выше, привело к поиску по индексу и повышению производительности на 100%.

Просто добавить: опция (перекомпилировать) у меня не сработала.

И после всех этих песен и танцев есть способ сказать dapper сделать это за вас по умолчанию:

Dapper.SqlMapper.AddTypeMap(typeof(string), System.Data.DbType.AnsiString);

Это по умолчанию сопоставляет любые строковые параметры с varchar (4000), а не с nvarchar (4000). Если вам нужно сравнение строк Unicode, вы можете явно выполнить преобразование параметра.

person Dev_Corps    schedule 17.07.2015