MS SQL, почему медленно выполняется запрос когда передаю много параметров?

Question

[Pantuchi]

Суть проблемы, есть запрос к одной таблице, из этой таблицы вытягиваю разные данные и выпрямляю их через inner. Чтобы вытащить эти разные данные передаю больше 10+ параметров отбора. Все проиндексировано и установленные ключи. Когда я проверяю запрос в редакторе MSSQL он выполняется до 2 сек. Когда переношу запрос в C# и передаю параметры через SQLCommand.Parameters.add(); запрос выполняется до 27 сек. Если я уберу параметры и запишу их в текстовке к запросу в шапке перед выборкой declare @param1 int = ... запрос выполняется так же быстро как и в MSSQL. Наверное некоторые скажут у тебя закешился неправильный план запроса, нет, все это я чистил и даже на тестовом сервере делал перезагрузку. Пришел к выводу что черезмерное большое количество переданных параметров через SQLCommand.Parameters.add(); душит запрос, нежели я эти параметры захардю в шапке текстовки запроса. Привести пример не смогу так как запрос очень большой в sql текстовке, а на C# сейчас крутится именно вторая версия запроса где параметры забиты в текстовке sql в шапке перед отбором. На счет инъекций все пучком, пользовательского ввода в запросе нет.
Хотелось бы услышать ваши рассуждения почему через C# когда передаю все эти параметры через SQLCommand.Parameters.add() запрос начинает душится, е если я эти параметры встрою в шапке команды declare @param1 int = ... перед selec, то запрос выполняется быстро.

Answer 1

[firstmixon]

command.Parameters.AddWithValue("@name_param", value_param);

Comments

[Pantuchi]

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

[firstmixon]

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

[mayton2019]

Не знаю как работает оптимизатор MSSQL. Возможно в варианте с хардкодом констант
оптимизатор видел более правильный план чем с bind-variables. Я такой эффект наблюдал
на Oracle.

[Pantuchi]

firstmixon, это разовый запрос. Запрос писал в MS SQL прогнал, выполнился быстро. Перенес текстовку на c# в метод, обернул юзингами, добавил параметры в SQLCommand, запустил, результат получил через ~27 секунд вместо 4 секунд в MSSQL думал может я праметры передал каряво что он их динамически типизирует, ладно указал типы для параметров это проблемы не решило, глазами парсил листинг так ничего не увидел, подсмотрел как делает запросы начальник. Перенял подход некоторые параметры вшивать прямо в листинг перед select. Повторил запос и ахренел как быстро он выполнился. Пришел к выводу, что черезмерное большое количество параметров переданных через SQLCommand каким то образом душит запрос или анализатор (не знаю). Я так и не понял почему запрос так медленно выполнялся, я даже развернул базу на домашнем пк и результат был такой же медленный, прям загадка. Как только не вертел запрос, но единственный лучший вариант был это вшить некоторые параметры int, decimal и даже string в листинг запроса, а DateTime я так и оставил в параметрах SQLCommand. После этих манипуляций запрос выполнился ожидаемо быстро. [для меня это пока загадка]

[Pantuchi]

mayton2019, может быть

[mayton2019]

Pantuchi, смотри. Разница между 4 и 27 секунд важна не для твоего кода а для бизнеса.
Спроси бизнес как они хотят?

Вариант где ты в StringBuilder/SQLBuilder делаешь текст запроса - нормальный вариант.
Я так тоже делал. Единственное - проверь на SQL-инжекции вручную до подстановки параметров.

Go-Go

[Pantuchi]

mayton2019,

Pantuchi, смотри. Разница между 4 и 27 секунд важна не для твоего кода а для бизнеса.
Спроси бизнес как они хотят?

Ни кто не любит ждать)
от инъекций перестраховался, пользовательский ввод не попадает

[mayton2019]

Pantuchi, ну тогда все ОК. Внедряй.

[firstmixon]

Pantuchi, Если стоит задача время\деньги, то рекомендовал бы перенести запрос на сторону SQL сервера, табличные функции и размещенные процедуры Вам в помощь, также для повышения скорости стоит озадачится индексами.

[Pantuchi]

firstmixon, составной кластеризованный индекс имеется усечение выборки идет как раз по индексированным полям. Перестроение индексов ранее выполнялась, дефрагментация менее 10%. В запросе так же участвуют процедуры.

Answer 2

[Nik Faraday]

Вероятнее всего проблема может быть связанна с данными, которые передаются. Если вы передаёте сам запрос на выполнение в БД, вам нужен другой вариант. Если вы утверждаете, что запрос в БД выполняется 2 секунды, а через C# 27, выход - используйте stored procedure

Comments

[Pantuchi]

это не выход так как условия отбора динамические, решение в общем уже найдено путем встраивания (постоянных) параметров в шапку текстовки запроса переде select, а (динамические) параметры и параметры по типу DateTime или тек которые подвержены инъекции все также передаю через command.Parameters. После таких манипуляций вышел на +/- 4 секунды
*постоянные тек которыми не управляет пользователь
*динамические те что выбирает пользователь для отбора конечного

Вероятнее всего проблема может быть связанна с данными

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