Изменение плана запроса при увеличении выборки

[Lucky13]

Есть таблица в которой много записей (17 млн). В ней есть поле SalesDate типа дата и по этому полю есть индекс. Выполняется SQL-запрос:

SELECT * FROM TABLE
WHERE SALESDATE >=

Если значение дата отличается от сегодняшней более чем на неделю, то план запроса показывает, что идет fullscan по таблице, если нет, то происходит выборка по индексу.

С чем может быть связано такое поведение? Реиндексация и обновление статистики не помогает.

[Владимир Максимов]

Что значит "с чем"? Сам же и ответил - отличается больше чем на неделю.

Оптимизатор сервера пришел к выводу, что получить эту выборку будет быстрее, если сканировать не индекс, а саму таблицу. Ведь в данном случае "оптимизация" - означает "ускорение". Использование индекса - далеко не всегда приводит именно к "ускорению".

[Lucky13]

Цитата:

Сообщение от Владимир Максимов

Что значит "с чем"? Сам же и ответил - отличается больше чем на неделю.

Оптимизатор сервера пришел к выводу, что получить эту выборку будет быстрее, если сканировать не индекс, а саму таблицу. Ведь в данном случае "оптимизация" - означает "ускорение". Использование индекса - далеко не всегда приводит именно к "ускорению".

В том-то и дело, что это приводит к конкретному замедлению. Если указать в запросе индекс явно

SELECT * FROM TABLE(INDEX( ))
WHERE SALESDATE >=

то запрос выполняется за минуту (при любом значении даты), если не указывать, то несколько часов. Так как указать индекс явно из аксапты не получается (по крайне мере я не знаю как это сделать через Query), то и приходится думать почему оптимизатор принимает решение, приводящее не к ускорению, а к замедлению и как его заставить этого не делать.

[kashperuk]

Кусок кода из метода \\Classes\\InventDimCtrl_Frm_OnHand\modifyQuery:

        qbsDim.addSortIndex(indexnum(InventDim,DimIdIdx));
        qbsDim.indexIsHint(true);

[Lucky13]

Цитата:

Сообщение от kashperuk

Кусок кода из метода \\Classes\\InventDimCtrl_Frm_OnHand\modifyQuery:

        qbsDim.addSortIndex(indexnum(InventDim,DimIdIdx));
        qbsDim.indexIsHint(true);

По-моему это немного не то.
addSortIndex указывает, что нужно использовать индекс при выполнении сортировки (добавляет USING INDEX DimIdIdx) в секцию ORDER BY запроса, а нужно чтобы нужный индекс использовался при выборке данных

[kashperuk]

Для этого там и указана еще вторая строка - indexIsHint

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

Проверьте. Должно помочь

[Михаил Андреев]

Хм. Прикольно. Редкий случай, когда указание индекса не просто оправдано, но необходимо.

Оптимизатор тоже прав. У него куча запросов по последнему дню. Есть кластерный индекс по SalesId, так как SalesID постоянно растёт, т.е. растёт вместе с датой. Отсюда и вывод оптимизатора - зачем нужен индекс, если результат без индекса такой же, что с индексом, но быстрее. Очень интересный случай Вопрос, как оптимизатор определил, что в старых записях не поменялись даты. Видимо, кеширует старые результаты. Но здесь надо спецов по СУБД MS SQL теребить.

[Lucky13]

Может быть теоретически это и так, но практически получается, что к запросу в аксапте добавляется INDEXISHINT после SELECT и USING INDEX в ORDER BY, но на сервер уходит такой же запрос, что и без указания этих параметров, следовательно план запроса не изменяется.

[Михаил Андреев]

Цитата:

Сообщение от Lucky13

Может быть теоретически это и так, но практически получается, что к запросу в аксапте добавляется INDEXISHINT после SELECT и USING INDEX в ORDER BY, но на сервер уходит такой же запрос, что и без указания этих параметров, следовательно план запроса не изменяется.

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

[Alexei S]

Lucky13, а какой SQL Server используете?

[belugin]

Оптимизатор смотрит на статистику (вы ее давно перестраивали)

В книжке про оракл упоминалось правило 5 процентов - если индекс позволяет выбрать 5% записей, то он используется иначе - нет (давно было могу чо-то наврать)

[Lucky13]

Цитата:

Сообщение от Alexei S

Lucky13, а какой SQL Server используете?

MS SQL 2005

Цитата:

Сообщение от belugin

Оптимизатор смотрит на статистику (вы ее давно перестраивали)

В книжке про оракл упоминалось правило 5 процентов - если индекс позволяет выбрать 5% записей, то он используется иначе - нет (давно было могу чо-то наврать)

Статистика обновлялась перед тем как выполнить запрос. Есть критичная дата после которой индекс перестает использоваться, в данном случае это 10 дней. Т.е.
За 10 дней - результат 595 записей, поиск по индексу
За 11 дней - результат 637 записей, поиск не по индексу

При общем количестве записей 17 млн и то и другое явно меньше 5%

[glibs]

Цитата:

Сообщение от Lucky13

...
За 10 дней - результат 595 записей, поиск по индексу
За 11 дней - результат 637 записей, поиск не по индексу
...

Это по факту или по оценкам оптимизатора?

Оценки оптимизатора более-менее соответствуют реальности?

[Lucky13]

Цитата:

Сообщение от glibs

Это по факту или по оценкам оптимизатора?

Оценки оптимизатора более-менее соответствуют реальности?

Это по факту. А что значит по оценкам оптимизатора, где эти оценки можно посмотреть?

[Logger]

Цитата:

Сообщение от belugin

Оптимизатор смотрит на статистику (вы ее давно перестраивали)

В книжке про оракл упоминалось правило 5 процентов - если индекс позволяет выбрать 5% записей, то он используется иначе - нет (давно было могу чо-то наврать)

Если не ошибаюсь, там можно настроить БД самому на какой угодно процент.

[ivas]

Цитата:

Сообщение от Lucky13

С чем может быть связано такое поведение? Реиндексация и обновление статистики не помогает.

а какой кластерный индекс на вашей таблице?

[Lucky13]

Цитата:

Сообщение от ivas

а какой кластерный индекс на вашей таблице?

Кластерных индексов на таблице нет есть некластерный по полям SALESDATE и SALESTIME. Поле SALESDATE первое в индексе

[ice]

Цитата:

Сообщение от Lucky13

Кластерных индексов на таблице нет есть некластерный по полям SALESDATE и SALESTIME. Поле SALESDATE первое в индексе

может лучше создать отдельный индекс только по полю SALESDATE?

[Alexei S]

Видимо, SQL Server, рассчитывая объем данных, который потребуется загрузить для обработки запроса, считает, что меньшим будет объем записей во всей таблице нежели страницы индекса + соответствующие им записи таблицы. Тогда возможным решением будет то, что предложил ice - это сократит размер записей индекса:

Цитата:

Сообщение от ice

может лучше создать отдельный индекс только по полю SALESDATE?

[Lucky13]

Цитата:

Сообщение от Alexei S

Видимо, SQL Server, рассчитывая объем данных, который потребуется загрузить для обработки запроса, считает, что меньшим будет объем записей во всей таблице нежели страницы индекса + соответствующие им записи таблицы. Тогда возможным решением будет то, что предложил ice - это сократит размер записей индекса:

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