Sp_executesql медленнее с параметрами

Я использую dapper-dot-net как ORM, и он производит следующий, медленный (1700ms), 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'

Ответ 1

Добавить опцию (RECOMPILE) до конца

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

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

Если это случай, мы можем оставить его с помощью OPTION или рассмотреть альтернативы

Обновление 1

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

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

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

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

Обновление 2

Лучшее, что я прочитал по этому вопросу, было в главе 32, названной "Параметр нюхает: ваш лучший друг... кроме случаев, когда он не является" GRANT FRITCHEY

Ответ 2

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

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

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

WHERE Reference = convert (varchar (80), @Reference)

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

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

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

Dapper.SqlMapper.AddTypeMap(TypeOf (строка), System.Data.DbType.AnsiString);

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