Логическое короткое замыкание в SQL Server 2008, похоже, не работает для полнотекстового предиката CONTAINS

У меня довольно сложный запрос, который иногда очень медленно возвращает результаты. С самого начала было ясно, что виновником является часть предложения WHERE, связанная с полнотекстовым поиском. Поэтому я изолировал его и протестировал. В результате этого тестирования выяснилось, когда это произойдет, но я пока не понял, как это исправить. Вот в чем проблема:

Мне нужно отфильтровать один, два или три столбца с полнотекстовым индексом в зависимости от того, что хочет сделать пользователь. Поскольку кажется, что список столбцов в предикате CONTAINS не может быть представлен как переменная, следующее лучшее, что я мог придумать, - это использовать некоторую стандартную логическую логику, например:

(((@SearchInName = 0 AND @SearchInShortDescr = 0 AND @SearchInHTMLDescr = 0) OR @SearchExpression = '""')
OR (@SearchInName = 1 AND @SearchInShortDescr = 0 AND @SearchInHTMLDescr = 0 AND CONTAINS(ProductName, @SearchExpression))
OR (@SearchInName = 0 AND @SearchInShortDescr = 1 AND @SearchInHTMLDescr = 0 AND CONTAINS(ProductShortDescr, @SearchExpression))
OR (@SearchInName = 0 AND @SearchInShortDescr = 0 AND @SearchInHTMLDescr = 1 AND CONTAINS(ProductDescrHTML, @SearchExpression))
OR (@SearchInName = 1 AND @SearchInShortDescr = 1 AND @SearchInHTMLDescr = 0 AND CONTAINS((ProductName, ProductShortDescr), @SearchExpression))
OR (@SearchInName = 1 AND @SearchInShortDescr = 0 AND @SearchInHTMLDescr = 1 AND CONTAINS((ProductName, ProductDescrHTML), @SearchExpression))
OR (@SearchInName = 0 AND @SearchInShortDescr = 1 AND @SearchInHTMLDescr = 1 AND CONTAINS((ProductShortDescr, ProductDescrHTML), @SearchExpression))
OR (@SearchInName = 1 AND @SearchInShortDescr = 1 AND @SearchInHTMLDescr = 1 AND CONTAINS((ProductName, ProductShortDescr, ProductDescrHTML), @SearchExpression))

Это было бы хорошо и работало бы так, как ожидалось (быстро), пока фактическое допустимое условие совпадает с последним разделом ИЛИ. Таким образом, в приведенном выше примере это будет, когда @SearchInName = 1 И @SearchInShortDescr = 1 И @SearchInHTMLDescr = 1 (пользователь хочет искать во всех 3 столбцах). Другие условия ТАКЖЕ быстро возвращаются, если они помещаются последними в этом блоке кода. Однако, как только фактическое допустимое условие выше, чем последнее, кажется, что все последующие за ним операторы CONTAINS также запускаются (хотя и не обязательно включаются в результаты, что является правильным), что занимает время выполнения от менее 1 с до 6. секунд или более в зависимости от того, насколько высок допустимый оператор и, следовательно, сколько других предикатов CONTAINS следует за ним. Очевидный вывод из этого состоит в том, что по какой-то причине следующие логические операторы не могут выполнить короткое замыкание и запустить предикат CONTAINS, несмотря ни на что.

Я понимаю, что порядок выполнения не всегда определяется фактическим кодом и что после оптимизации SQL некоторые короткие замыкания могут работать не так, как задумано, благодаря ему, что, похоже, именно в этом случае. Обычно предлагается использовать оператор CASE для обеспечения порядка, но, к сожалению, оператор CONTAINS, похоже, вообще не подходит для оператора CASE.

Поэтому я думаю, что, вероятно, знаю, почему это происходит (оптимизация SQL), но не могу понять, как это исправить. Кто-нибудь может помочь?


sql sql-server contains full-text-search
person Marek    schedule 13.09.2012    source источник
comment
Нет никакой гарантии того, какие предикаты будут оцениваться и когда, даже при использовании CASE (раньше я полагал, что CASE можно использовать для наложения порядка, но оказалось, что это не так)   -  person Damien_The_Unbeliever    schedule 13.09.2012
comment
Поскольку это, похоже, связано с поиском, вы можете прочитать отличные Условия динамического поиска в SQL < / а>   -  person Damien_The_Unbeliever    schedule 13.09.2012
comment
Что ж, насколько я тестировал, CASE в любом случае нельзя использовать с CONTAINS, но спасибо за замечание.   -  person Marek    schedule 13.09.2012
comment
Спасибо тоже за ссылку. Я только что дочитал, и для меня появилось несколько новых полезных моментов. В основном это убедило меня в очевидном факте, что динамический SQL не так уж плох, как я считал давным-давно. Возможно, мне придется прибегнуть к этому с этими процедурами, если кто-то не поможет с лучшим предложением. Я просто не очень хочу этого делать, так как эти выделения имеют длину примерно 400 строк, и поэтому приятно получить цветовую кодировку при их обновлении.   -  person Marek    schedule 13.09.2012

Ответы (1)


arrow_upward
1
arrow_downward

Сервер Sql кэширует план, чтобы его можно было использовать повторно. Непосредственно подразумевается, что план, созданный SQL-сервером, должен работать для всех случаев и, следовательно, должен быть универсальным. Это исключает любую возможность короткого замыкания логики.

Одно из решений - использовать подсказку запроса OPTION (RECOMPILE). Это заставит SQL-сервер перекомпилировать запрос при каждом его выполнении. Поскольку SQL знает, что план никогда не будет повторно использован, он будет проверять значения параметров, применять логику короткого замыкания, выбирать отфильтрованные индексы и составлять наилучший план.

Это не ужасное решение, потому что полнотекстовые индексы в любом случае генерируют частичную перекомпиляцию (вы можете получить другой план в зависимости от запроса, переданного в функцию CONTAINS).

Альтернативой является использование динамического SQL для построения запроса. С динамическим SQL вы получите лучшее из обоих миров: лучшие планы и повторное использование планов.

Имеет ли значение, какую технику вы используете? В большинстве случаев нет, все в порядке. Если ваш запрос вызывается очень часто, например, 3-4 миллиона вызовов в день, то количество компиляций в секунду может стать узким местом. Если это произойдет, предпочтителен подход динамического SQL.

person StrayCatDBA    schedule 25.04.2013