Почему несколько условий WHERE замедляют запрос, а не ускоряют его?

Проблема в том, что рассматриваемый запрос работает очень медленно по сравнению с запросом, выполняемым с одним или двумя, а не со всеми тремя его условиями.

Теперь запрос.

Select Count(*)
From 
    SearchTable 
Where 
    [Date] >= '8/1/2009' 
    AND 
    [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
    AND 
    FreeText([Description], 'keyword list here')

Первое условие является самоочевидным. Второй использует UDF для получения списка Zip-кодов в пределах 150 миль от 30348. Третий использует полный текстовый индекс для поиска предоставленных слов.

Только с этим условием

[Date] >= '8/1/2009'

Запрос возвращает 43884 (размер таблицы составляет менее 500 тыс. строк) в 3 секунды.

Используя только это условие

[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))

Я получаю 27920, также возвращается в 3 секунды.

И только с полной текстовой частью

FreeText([Description], 'keyword list here')

68404 возвращается в 8 секунд.

Когда я использую только почтовый индекс и полные текстовые условия, я получаю 4919 в 4 секунды.
Только дата и полные текстовые условия заставляют меня 9481 просто стесняться 14 секунд.
Использование условий даты и почтового индекса дает мне только 3238 в 14 секунд.
При всех трех условиях запрос возвращает 723 в 2 минуты, 53 секунды. (Wtfbbq)

Ответ 1

Единственный способ узнать, почему нужно проверить план выполнения. Попробуйте SET SHOWPLAN_TEXT ON.

Ответ 2

Получить план выполнения

Вам нужно посмотреть план выполнения, чтобы иметь любую надежду, чтобы понять реальную причину изменения времени ответа. В частности, в этом случае необходимо учитывать несколько факторов:

Возможно, что некоторые из запросов, возвращающих больше строк, быстрее, потому что они выполняют сканирование таблиц - у всех есть "сканирование таблицы медленное", но в зависимости от распределения данных, возможно, быстрее выполнить сканирование таблицы чем 50 000 строк поиска. Его просто невозможно рассказать без сканирования выполнения.
Также возможно, что некорректная статистика препятствует тому, чтобы SQL-сервер точно предсказал, что количество строк, которое он ожидает вернуть, - если SQL-сервер ожидает 20 строк, но есть действительно 20 000, а затем в более сложных запросах, которые, скорее всего, в неправильном порядке, что приводит к очень медленному запросу - снова его просто невозможно сказать без плана выполнения.
В частности, использование Freetext означает, что используется полнотекстовая поисковая система, что может вызвать дополнительные проблемы SQL-сервера при прогнозировании количества возвращенных строк.

Действительно, получите план выполнения.

Update:

Возможные причины

В отсутствие плана выполнения я считаю, что наиболее вероятной причиной медленного выполнения являются плохие оценки условий на ZipCode и Description:

Трудно оценить количество совпадений в ZipCode, поскольку его результат зависит от хранимой процедуры.
Трудно оценить количество совпадений в Freetext, поскольку оно основано на результатах полнотекстового механизма запросов.

Я считаю, что SQL-сервер недооценивает количество строк, которые будут оставаться после фильтрации, и применяя запросы в неправильном порядке. Результатом является то, что он заканчивает выполнение десятков (возможно, сотен) тысяч поисков, что намного намного медленнее, чем просто сканирование таблицы.

Для особо сложного запроса я видел, что SQL-сервер выполняет ~ 3 000 000 поисков, пытающихся вернуть одну строку - в таблице даже не было 3 000 000 строк!

Что нужно попробовать - Поместите ZipCodeForRadius в временную таблицу.

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

Улучшенная статистика временной таблицы
Он будет иметь побочный эффект, при котором основной оператор SELECT должен быть перекомпилирован каждый раз, когда вы запускаете запрос (если диапазон почтовых индексов не очень мал) - в proc требуется несколько секунд, в любом случае это будет Хорошо, если есть большие различия в соответствующих почтовых индексах. Если нет, то есть способы предотвращения перекомпиляции.

В любом случае это не должно наносить слишком большого урона.

Ответ 3

Потому что больше условий для проверки больше работы для механизма базы данных. Кажется логичным для меня.

Если у вас должно быть одно условие над кластеризованным полем индекса, эта конкретная проверка не сильно замедлит эту операцию. Рассматривали ли вы перегруппировку индексов в соответствии с запросом?

Ответ 4

Строковые операции, такие как FreeText, дороги
Функция ZipCodesForRadius может быть слишком дорогой в зависимости от того, как она закодирована, и если присутствуют необходимые индексы или нет.

Если порядок предложений WHERE не ускоряется, выбор из вашего выбора может сделать трюк (в некоторых случаях он ускорял работу с DB2/400, не уверен, как оптимизирует SqlServer):

Select Count(*)
From
(
    Select [Description]
    From 
        SearchTable 
    Where 
        [Date] >= '8/1/2009' 
        AND 
        [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))

) as t1
Where FreeText([Description], 'keyword list here')

Ответ 5

Попробуйте добавить некоторые индексы в свою таблицу. В частности, те, которые охватывают условия в вашем разделе where. Скорее всего, теперь он выполняет сканирование таблицы, чтобы вытащить данные, которые могут быть очень медленными.

Также вы можете использовать кнопку "Включить текущий план выполнения" в студии управления, чтобы показать, как это происходит с определением, какие записи вы получаете.

UPDATE

Из одного из ваших комментариев кажется, что этот запрос вытягивается из таблицы temp. В этом случае после создания таблицы применяют к нему индексы. Добавление индексов, после чего выполнение запросов будет быстрее, чем запуск сканирования таблицы в таблице temp 500k.

Ответ 6

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

В вашем случае, вероятно, произойдет следующее:

[Date] >= '8/1/2009' выполняется индексом, который содержит Date, скорее всего, по индексу ON Date, поэтому его быстрое сканирование диапазона
[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150)) то же, что и Date. Даже если у вас нет индекса в Zip, у вас, вероятно, есть индекс, содержащий Zip.
FreeText([Description], 'keyword list here') полнотекстовый поиск для count, который проходит через внутренние индексы FT, быстро.
Все три условия. Теперь это становится беспорядочным. Если у вас достаточно ОЗУ, запрос может сначала составить план поиска FT, затем HASH-JOIN, затем Zip scan, затем HASH-JOIN the Date. Это было бы быстро, порядка 3 + 3 + 8 секунд + изменить (для операции хэша). Но если у вас недостаточно ОЗУ или если оптимизатор не любит делать хеш-соединение, ему придется делать поиск по FT, затем вложенный поиск цикла Zip, а затем вложенный поиск цикла кода, и он может попасть в точка опроса в своих решениях. Поэтому, скорее всего, вы получите сканирование таблицы. Это, конечно, спекуляция с моей стороны, но в конце концов вы разместили только текст T-SQL и нулевую информацию о структуре ваших кластерных и некластеризованных индексов.

В конце вы должны помнить, что SQL - это не ваш C-процедурный язык. Когда речь заходит о производительности в SQL, это никогда не о сравнении и логической логике. Это всегда касается доступа к данным и количества прочитанных страниц. Таким образом, хотя каждое отдельное условие может быть удовлетворено небольшим быстрым сканированием диапазона индексов узкого некластеризованного индекса или индекса FT, комбинация не может (или в его случае Оптимизатор запросов не определил способ).

Ответ 7

Передача данных мудрая, вы правы в своем мышлении: меньше данных, более быстрое время завершения. Однако обычно это время минимально, и большую часть времени тратится на обработку фактических запросов.

Посмотрите на это так: если бы вы были в машине много, было бы легче выбрать все автомобили, которые были красными, или все автомобили, которые были красными, 2006 модельный год, черный интерьер и резиновые напольные коврики?

Ответ 8

Я подозреваю, что поле Date не индексировано и без индекса, чтобы полагаться на фильтрацию набора результатов перед тем, как применить предложение where в столбцах, не поддающихся выравниванию, он дает им одинаковый вес и не выполняет быстрые фильтры перед применяя другие более дорогие предложения.

Когда я не могу настроить базу данных с помощью индексов и т.д., я часто нахожу, что повторного написания запроса, подобного этому, достаточно, чтобы направить компилятор на более эффективный запрос:

Select Count(*)
From (
    Select 1
    From SearchTable 
    Where [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
) 
Where [Date] >= '8/1/2009' 
    AND FreeText([Description], 'keyword list here')