Реализация функциональности поиска с несколькими дополнительными параметрами против таблицы базы данных
Я хотел бы проверить, существует ли предпочтительный шаблон разработки для реализации функциональности поиска с несколькими дополнительными параметрами против таблицы базы данных, где доступ к базе данных должен быть только с помощью хранимых процедур.
Целевой платформой является .NET с SQL 2005, 2008 бэкенд, но я думаю, что это - довольно универсальная проблема.
Например, у нас есть клиентская таблица, и мы хотим предоставить функциональность поиска UI для различных параметров, как клиентский Тип, клиентское состояние, клиентская Zip, и т.д., и все они являются дополнительными и могут быть выбраны в любых комбинациях. Другими словами, пользователь может искать customerType только или customerType, customerZIp или любыми другими возможными комбинациями. Существует несколько доступных подходов дизайна, но у всех них есть некоторые недостатки, и я хотел бы спросить, существует ли предпочтительный дизайн среди них или если существует другой подход.
Генерируйте sql, куда пункт sql оператор динамично в бизнес-уровне, на основе поискового запроса от UI, и передает его хранимой процедуре как параметр. Что-то как @Where =, ‘где CustomerZip = 111111’ В хранимой процедуре генерируют динамический sql оператор и выполняют его с sp_executesql. Недостаток: динамический sql, внедрение SQL
Реализуйте хранимую процедуру с несколькими входными параметрами, представив поля поиска от UI, и используйте следующую конструкцию для выбора записей только для требуемых полей в где оператор.
ГДЕ
(CustomerType = @CustomerType OR @CustomerType is null )
AND (CustomerZip = @CustomerZip OR @CustomerZip is null )
AND …………………………………………
Недостаток: возможная проблема производительности для sql.
3. Реализация отдельная хранимая процедура для каждого поискового параметра комбинации. Недостаток: количество хранимых процедур увеличится быстро с увеличением поисковых параметров, повторенного кода.
3 ответа
Метод 1: динамический SQL может принимать параметры, это довольно тривиально сделать и в значительной степени исключает риск внедрения SQL-кода. Лучший аргумент против динамического SQL - это то, что нетривиальные операторы могут потребовать некоторой сложной логики для генерации, хотя это тоже не проблема, если вы используете достойный ORM.
NHiberante и LinqToSql создают динамический SQL за кулисами, и они не пронизаны дырами в безопасности. На мой взгляд, вам лучше рассмотреть одну из этих двух технологий, прежде чем запускать собственный DAL.
Метод 2: Раньше я лично без проблем использовал второй метод. Вы прокомментировали «возможную проблему производительности для sql», но профилировали ли вы? Сравнивали планы выполнения? По моему собственному опыту, использование подхода @param is null ИЛИ col = @param практически не повлияло на производительность. Помните, что если вам требуется 10 часов времени разработчика на оптимизацию кода, чтобы сэкономить 10 микросекунд в год времени выполнения, ваша чистая экономия все равно составит почти -10 часов.
Метод 3: Комбинаторный взрыв. Избегайте любой ценой.
Я разместил это как комментарий, но понял, что это, вероятно, должен быть ответ.
Нехорошо писать предикаты в виде WHERE @Param IS NULL OR Column = @Param , потому что оптимизатор обычно считает, что это несаргируемый.
Попробуйте этот эксперимент: возьмите свою наиболее заполненную таблицу и попробуйте запросить только для поля первичного ключа, которое должно быть вашим кластеризованным индексом:
DECLARE @PrimaryKey int
SET @PrimaryKey = 1
SELECT CoveredColumn
FROM Table
WHERE @PrimaryKey IS NULL
OR PrimaryKeyColumn = @PrimaryKey
SELECT CoveredColumn
FROM Table
WHERE PrimaryKeyColumn >= ISNULL(@PrimaryKey, 0)
AND PrimaryKeyColumn <= ISNULL(@PrimaryKey, 2147483647)
Оба эти SELECT операторы дадут идентичные результаты, если предположить, что столбец PK является неотрицательным int . Но откройте план выполнения для этого, и вы увидите огромную разницу в стоимости. Первый SELECT выполняет полное сканирование индекса и обычно занимает около 90% стоимости запроса.
Если вы хотите иметь дополнительные условия поиска в SQL и не можете использовать динамический SQL, для повышения производительности лучше всего превратить его в запрос диапазона вместо использования ISNULL . Даже если диапазон огромный (здесь буквально половина диапазона int ), оптимизатор все равно вычислит его, когда используется необязательный параметр .