T-SQL Where Clause Оптимизация бизнес-операций (дополнительные параметры для StoredProc)

Я уже некоторое время сражаюсь с этим. У меня есть сохраненный proc, который принимает 3 параметра, которые используются для фильтрации. Если определенное значение передано, я хочу отфильтровать его. Если -1 передано, дайте мне все.

Я пробовал это двумя способами:

Первый способ:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
parm1 = CASE WHEN @PARM1= -1 THEN parm1  ELSE @PARM1 END  
AND parm2 = CASE WHEN @PARM2 = -1 THEN parm2  ELSE @PARM2 END  
AND parm3 = CASE WHEN @PARM3 = -1 THEN parm3  ELSE @PARM3 END

Второй способ:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
(@PARM1 = -1 OR parm1 = @PARM1)  
AND (@PARM2 = -1 OR parm2 = @PARM2)  
AND (@PARM3 = -1 OR parm3 = @PARM3)

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

Основная таблица, из которой выбирается этот вид, содержит около 1,5 миллионов записей, и представление продолжает объединяться примерно в 15 других таблицах, чтобы собрать кучу другой информации.

Оба этих метода медленны... отнимая меня от момента до любой точки от 2-40 секунд, что в моей ситуации совершенно неприемлемо.

Есть ли лучший способ, который не включает разбиение его на каждый отдельный случай конкретных vs -1?

Любая помощь приветствуется. Спасибо.

Ответ 1

Я где-то читал, что второй способ будет короткое замыкание и никогда не будет оценивать вторую часть, если это правда. Мой администратор баз данных сказал, что он вызывает сканирование таблицы.

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

Первый вариант примерно такой же хороший, как и он. Ваши возможности улучшить ситуацию - это динамический sql или длинная хранимая процедура с любой возможной комбинацией столбцов фильтра, поэтому вы получаете независимые планы запросов. Вы также можете попробовать использовать параметр "WITH RECOMPILE", но я не думаю, что это вам поможет.

Ответ 2

Если вы используете SQL Server 2005 или выше, вы можете использовать IF, чтобы сделать несколько версий запроса с надлежащим WHERE, чтобы можно было использовать индекс. Каждый план запроса будет помещен в кеш запросов.

также, вот очень полная статья по этой теме:

Динамические условия поиска в T-SQL от Erland Sommarskog

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

вот оглавление:

  Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

Ответ 3

Если вы передадите нулевое значение, когда хотите все, тогда вы можете написать предложение where как

   Where colName = IsNull(@Paramater, ColName)

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

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

Ответ 4

Нет другого пути, о котором я могу думать:

где

(MyCase IS NULL ИЛИ MyCase = @MyCaseParameter) И....

Второй, более простой и понятный для разработчиков, если вы спросите меня.

Ответ 5

SQL 2008 и более поздние версии делают некоторые улучшения в оптимизации для таких вещей, как (MyCase IS NULL OR MyCase = @MyCaseParameter) AND ....

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

http://blogs.msdn.com/b/bartd/archive/2009/05/03/sometimes-the-simplest-solution-isn-t-the-best-solution-the-all-in-one-search-query.aspx