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

[ice]

Цитата:

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

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

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

[Lucky13]

Тот же эффект наблюдается на таблице InventTrans

SELECT *
FROM INVENTTRANS
WHERE DATAAREAID = ?? and DATEPHYSICAL >= {ts '2008-04-20 00:00:00.000'}

Идет поиск не по индексу, время выполнения запроса 7 мин

SELECT *
FROM INVENTTRANS
WHERE DATAAREAID = ?? and DATEPHYSICAL >= {ts '2008-04-21 00:00:00.000'}

Идет поиск по индексу, время выполнения запроса 1 мин 50 сек. Такое же время выполняется первый запрос, если указать индекс явно.

?? - код какой-либо компании, которая есть в базе
Дата подбирается эксперементально. После критической даты план начинает показывать fullscan

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

Как с этим бороться по прежнему не понятно

[Alexei S]

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

Цитата:

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

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

[Lucky13]

Цитата:

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

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

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

[AFed]

попробуйте использовать в запросе
query.literals(0) чтобы оптимизатор сам решил использовать литерал или шаблон в запросе
если у вас четверка то там по умолчанию используется шаблон в запросе а у вас похоже надо использовать литерал
query.literals(1) принудительное использование литералов
query.literals(2) принудительное использование шаблонов

[Alexius]

1. Включена ли на таблице "SaveDataPerCompany" ?
2. Есть ли кластерный индекс ?

[Lucky13]

Цитата:

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

1. Включена ли на таблице "SaveDataPerCompany" ?

Да, включена. Поле DataAreaId присутствует в индексе и в запросе. В примере условие по DataAreaId опущено для наглядности

Цитата:

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

2. Есть ли кластерный индекс ?

Нет, на таблице нет кластерных индексов

[Alexius]

Варианты решения:
1. Создайте кластерный индекс по RecId
2. Добавьте в имеющейся индекс (DataAreaId, SalesDate, SalesTime) поле RecId и сделайте его кластерным
3. Попробуйте ограничить число полей в выборке и создайте покрывающий индекс
4. Создайте индекс напрямую на SQL (SalesDate, SalesTime, DataAreaId), если поможет, то можете в Аксапте встроить его проверку/создание в Classes / Application / dbSynchronize

Варианты 2,3 помогут с очень высокой вероятностью, 1,4 - надо экспериментировать

[Logger]

Цитата:

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

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

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

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

[Logger]

Цитата:

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

Варианты решения:
1. Создайте кластерный индекс по RecId
2. Добавьте в имеющейся индекс (DataAreaId, SalesDate, SalesTime) поле RecId и сделайте его кластерным
3. Попробуйте ограничить число полей в выборке и создайте покрывающий индекс
4. Создайте индекс напрямую на SQL (SalesDate, SalesTime, DataAreaId), если поможет, то можете в Аксапте встроить его проверку/создание в Classes / Application / dbSynchronize

Варианты 2,3 помогут с очень высокой вероятностью, 1,4 - надо экспериментировать

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

По п.4 - можно и в Аксапте решить - был такой ключ командной строки CROSSCOMPANY - позволял ставить dataareaId не в начало индекса.

[fed]

Если мне не изменяет память, статистика автоматически строится только по первому полю индекса, каковым в случает Аксапты всегда является поле dataAreaId. Кроме того, если при выполнении запроса системе не хватает данных о распределении значений в поле, то она также пытается построить статистику по этому полю. Правда тут есть некая заковыка: Если система считает что строить статистику на лету слишком долго (ну типа - пока еще 17 миллионов записей обработаешь), то она эту статистику просто не строит, а работает на некоторых умолчаниях (например что для полей типа дата все выборки за данными старше одной недели делать full scan ) Так что есть очень большое подозрение, что в вашем случае нужной статистики просто нету.

Поэтому - есть один очень простой рецепт борьбы с некорректными планами запроса. Если план запроса получается странноватым, то прежде всего нужно ручками СОЗДАТЬ статистику по тем полям, которые используются для range или join. Обновление статистики не дает нужного эффекта, поскольку оно обновляет уже СУЩЕСТВУЮЩУЮ статистику. Если система так ни разу и не собралась с мыслями эту статистику по полю создать, то и обновление не поможет.
Если и после этого план получается странным - надо проверить что в нужных местах стоит режим forceliterals, потому что без него оптимизатор просто игнорирует статистику. Правда, в нашем случае он наверняка стоит, поскольку в обратном случае система делала бы одинаковый план запроса для любой даты.

Если после всех этих простых действий система продолжает генерировать кривой план запроса - вот тут уже можно заниматься всякими шаманскими действиями типа построения кластерного индекса по recId и тп.

Кстати - еще очень рекомендую в MS SQL 2005 в свойствах БД ставить галочку "Auto update statistics asynchronously". Ее смысл состоит в том что если даже система решила сэкономить время и выполнять запрос на умолчаниях (то есть без ожидания построения статистики), то статистика по данному полю все равно потихоньку собирается, на случай если она пригодится для следующих запросов.

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

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

[Alexius]

Цитата:

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

По п.4 - можно и в Аксапте решить - был такой ключ командной строки CROSSCOMPANY - позволял ставить dataareaId не в начало индекса.

Далеко не всегда перемещение поля DataAreaId в конец индекса оптимально для производительности. В АХ 4.0 это поле по умолчанию в начале, но можно перетащить куда угодно.

[Logger]

Цитата:

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

Далеко не всегда перемещение поля DataAreaId в конец индекса оптимально для производительности.

Я этого и не имел в виду.
Просто упомянул про удобный ключ.

[Lucky13]

Интересно, такое поведение оптимизатора наблюдается всегда на больших таблицах MSSQL 2005 (а может быть и на других серверах) или только при определенных условиях? Может какие-то настройки сервера влияют. Я пробовал на разных серверах и разных таблицах - если в таблице несколько миллионов записей и производится выборка по полю типа дата (см. пример запроса выше), то при указании определенного критического значения даты поиск идет не по индексу.

[Alexius]

А параллелизм в плане не присутствует ?

[Lucky13]

Цитата:

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

А параллелизм в плане не присутствует ?

Нет, только fullscan и все