Формирование прайс-листа в Axapta. Проблемы производительности.

[Lucky13]

Задача сформировать прайс-лист, т.е. вывести все позиции, которые находятся на складе X в количестве больше Y (частный случай >0). Простейшее решение выполнить следующий запрос:

Select from InventSum 
join InventDim
Where InventSum.InventDimId == InventDim.InventDimId &&  InventSum.AvailPhysical > Y && InventDim.InventLocationId = X

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

Вопрос как лучше решать данную задачу в Axapta? Кто какие подходы для этого использует?

[ice]

как решено у нас:
внешний цикл по номенклатуре, с фильтром заблокированных и др ограничения.
внутри находим количество на складе, цену, накладываем допусловия и ограничения, если все ОК, то добавляем в tmpTable. вывод

[Logger]

Цитата:

так как выборка идет не по индексам

Что же мешает создать нужный индекс ?

С совсем тяжелых случаях (если inventSum и InventDim - очень большие) можно сделать денормализацию данных. Создать поле склад в InventSum и индексы повесить. Тем самым избавимся от джоина. Намного легче запрос получится.

[Lucky13]

Цитата:

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

Что же мешает создать нужный индекс ?

Мешает то, что непонятно каким должен быть этот индекс. По полю AvailPhysical? Не думаю, что индекс по вещественному полю увеличит производительность.

Цитата:

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

С совсем тяжелых случаях (если inventSum и InventDim - очень большие) можно сделать денормализацию данных. Создать поле склад в InventSum и индексы повесить. Тем самым избавимся от джоина. Намного легче запрос получится.

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

[mazzy]

Цитата:

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

Задача сформировать прайс-лист, т.е. вывести все позиции, которые находятся на складе X в количестве больше Y (частный случай >0). Простейшее решение выполнить следующий запрос:

Простейшее решение не такое.
Простейшее решение - взять готовый отчет Критические запасы
http://forum.mazzy.ru/index.php?showtopic=1842

[Lucky13]

Цитата:

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

как решено у нас:
внешний цикл по номенклатуре, с фильтром заблокированных и др ограничения.
внутри находим количество на складе, цену, накладываем допусловия и ограничения, если все ОК, то добавляем в tmpTable. вывод

А какая у этого всего производительность? Если оценивать по моим данным то:
цикл по номенклатуре с учетом всех ограничений - более 50000 записей, количество записей в прайс-листе - около 3000.

Таким образом для каждой номенклатуры нужно выполнить запрос к InventSum по ItemId и суммировать кол-во. Такой запрос выполняется примерно 1 сек. (точно не анализировал, но запрос первой попавшейся НЕ на реально загруженном сервере дало именно такой результат).
То есть общий цикл будет выполняться ну очень долго.

Если я ошибаюсь в рассуждениях, поправьте меня.

[ice]

Цитата:

А какая у этого всего производительность?

у нас меньше номенклатуры, но сложный механизм расчета цены, выполняется 15-30 сек

[konopello]

Цитата:

Простейшее решение не такое.
Простейшее решение - взять готовый отчет Критические запасы
http://forum.mazzy.ru/index.php?showtopic=1842

во блин благодарен как раз его и искал!!!

[gl00mie]

Цитата:

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

Задача сформировать прайс-лист, т.е. вывести все позиции, которые находятся на складе X в количестве больше Y (частный случай >0). Простейшее решение выполнить следующий запрос:

Select from InventSum 
join InventDim
Where InventSum.InventDimId == InventDim.InventDimId &&  InventSum.AvailPhysical > Y && InventDim.InventLocationId = X

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

У любой сложной задачи существует простое, ясное и неправильное решение А выгрузка прайс-листа - задача в общем лсучае сложная, вон, mazzy даже людей подбирает, исходя из того, как они с ней справляются

Цитата:

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

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

Во-первых, не подходите к этой задаче с точки зрения того, как вам построить один мега-запрос, который бы выдал все нужные данные с нужными группировками и фильтрами. Это и на родном SQL не всегда получается, а уж в Аксапте с ее ограничениями по количеству связанных таблиц в запросе - и подавно. Здесь по любому придется использовать подзапросы для получения дополнительных данных. Во-вторых, подумайте о том, что именно вы выгружаете: у вас ведь не отчет по складским остаткам, а выгрузка прайс-листа. Прайс-лист - это в первую очередь номенклатура, вот от нее и пляшите. Сколько у вас всего номенклатур? Если она исчисляется не миллионами, то лучше, вероятно, сделать выгрузку, перебирающую все номенклатуры (с определенными фильтрами, скажем, по бренду), а уже внутри этого перебора делающую проверку на остатки по определенному складу, на соответствие выгружаемых цен определенным критериями, etc.
Подумайте также о том, что InventSum у вас может постоянно блокироваться при обработке движений по номенклатуре, а вы, пытаясь сделать по ней мега-запрос выгрузки прайс-листа, в свою очередь, будете блокировать других пользователей. Поэтому, опять же, кучка мелких подзапросов будет в общем случае предпочтительнее одного мега-запроса, который сначала будет ждать освобождения кучи блокировок, а потом сам надолго заблокирует таблицу, и без того являющуюся зачастую узким местом в системе.
Затем, вам наверняка потребуется предоставить пользователю возможность группировать номенклатуру в выгрузке по различным критериям: либо по бренду, либо по какому-нить классификатору номенклатуры, либо еще по чему-то... Не так давно пришлось приделывать такую сортировку, плюс немного оптимизировать выгрузку прайс-листа, в котором используется довольно много условных критериев, в результате выгрузка (на псевдокоде) получилась примерно такая:

инициализировать канал вывода                           // отдельный метод
    если выгрузка в csv/Excel
        сделать то-то
    если выгрузка в другую базу SQL
        сделать что-то другое
вывести заголовок (header)                              // отдельный метод
сбросить значения предыдущего бренда и классификатора
построить запрос выборки номенклатуры                   // отдельный метод
    switch по типу сортировки
        если сортировка по бренду
            запрос - таблица_брендов join InventTable
        если сортировка по иерархическому классификатору номенклатуры
            запрос - таблица_классификации join InventTable join таблица_брендов
    установить фильтры по брендам                       // отдельный метод
    установить фильтры по номенклатуре                  // отдельный метод
создать QueryRun с созданным запросом по номенклатуре
цикл, пока QueryRun.next() == true
    если QueryRun.changed(таблица_брендов)
        если текущий бренд не должен экспортироваться   // отдельный метод
            установить флаг пропуска бренда
    если установлен флаг пропуска бренда
        continue
    получить необходимве данные по текущей номенклатуре
    если номенклатура не должна экспортироваться        // отдельный метод
        continue
    switch по типу сортировки
        если сортировка по бренду
            если текущий бренд != предыдущий бренд
                вывести заголовок бренда                // отдельный метод
                запомнить бренд как предыдущий
        если сортировка по иерархическому классификатору номенклатуры
            если текущий код классификатора не равен предыдущему
                вывести стек заговлоков классификатора  // отдельный метод
                запомнить код классификатора как предыдущий
    сбросить переменные, используемые для получения цен, и флаги
    цикл по ценовым группам
        если ценовая группа не должна экспортироваться  // отдельный метод
            // и это отдельный метод
            если номенклатура с "неэкспортируемой" ценой должна пропускаться
                установить флаг пропуска номенклатуры
                break
            continue
        получить цену (PriceDiscTable) по данной ценовой группе
        // опять отдельный метод
        если цена (т.е. PriceDiscTable) не должна экспортироваться
            // copy-paste из проверки ценовых групп
            если номенклатура с "неэкспортируемой" ценой должна пропускаться
                установить флаг пропуска номенклатуры
                break
            continue
        обработать данные по цене номенклатуры          // отдельный метод
            если выгрузка в csv/Excel
                сделать то-то
            если выгрузка в другую базу SQL
                сделать что-то другое
    если установлен признак пропуска номенклатуры
        continue
    выгрузить данные по текущей номенклатуре            // отдельный метод
вывести заголовок (footer)                              // отдельный метод
деинициализировать канал вывода                         // отдельный метод

С нужными индексами на таблицах из запроса по номенклатуре такая выгрузка по ~70000 наименований номенклатуры отрабатывает за минуту-две, в зависимости от канала вывода.

[Morpheus]

Цитата:

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

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

Объясните пожалуйста, как запрос на чтение записей блокирует таблицу?

[gl00mie]

Цитата:

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

Объясните пожалуйста, как запрос на чтение записей блокирует таблицу?

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

[Lucky13]

Цитата:

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

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

Теоретически все верно, но практически что-то не очень.
Запрос:

while select inventTable
where ...
{
}

Где "..." набор некоторых ограничений. В итоге цикл выполняется 66542 раза за 32 сек.

Если в цикл добавить запрос на выборку запасов в наличии

select sum(AvailPhysical) from inventSum
join maxof(recid) from InventDim
where inventSum.InventDimId == inventDim.inventDimId && inventSum.ItemId == inventTable.ItemId && inventSum.Closed == NoYes::No && inventDim.InventLocationId == 'склад'

Запрос выполняется очень быстро. Разве что немного мешает maxof(recid), но без него на сервер уходит запрос всех полей из InventDim, что неверно. Подставить вместо recId фиктивное поле TableId не получается, хотя помнится где-то об этом на форуме писалось.

Так вот - такой цикл выполняется очень долго. Честно ждал полчаса, но безрезультатно. "Мега-запрос" хотя и имеет массу недостатков, но выполняется максимум за 3 минуты.

Вопрос что здесь не так, почему такое расхождение теории с практикой?

[gl00mie]

Цитата:

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

Запрос:

while select inventTable where ...
{
}

Где "..." набор некоторых ограничений. В итоге цикл выполняется 66542 раза за 32 сек. Если в цикл добавить запрос на выборку запасов в наличии

select sum(AvailPhysical) from inventSum
join maxof(recid) from InventDim
where inventSum.InventDimId      == inventDim.inventDimId 
   && inventSum.ItemId           == inventTable.ItemId 
   && inventSum.Closed           == NoYes::No 
   && inventDim.InventLocationId == 'склад'

Запрос выполняется очень быстро. Разве что немного мешает maxof(recid), но без него на сервер уходит запрос всех полей из InventDim, что неверно.

а зачем использовать агрегатную функцию? просто join recId не дает нужного эффекта?

Цитата:

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

Так вот - такой цикл выполняется очень долго. Честно ждал полчаса, но безрезультатно.

Что-то я не пойму: цикл while selet inventTable выполняется быстро, запрос по InventSum выполняется быстро, а все вместе медленно? Попробуйте поставить условия where по InventSum перед join InventDim - на форуме писали, что Ms SQL может не самым эффектиным образом обрабатывать запрос по нескольким таблицам, где все where свалены в конец запроса. Посмотрите на план запроса, на то, какие индексы там используются. Попробуйте поменять условия where для InventSum, чтобы они соответствовали порядку полей хоть в одном из индексов на InventSum, - на перестановку полей при подборе индекса у СУБД тоже может уходить дополнительное время. Если ничего не поможет, попробуйте использовать forceplaceholders в запросе - возможно, на 66542 итераций его парсинга СУБД тратит больше времени, чем на саму выборку данных.

Цитата:

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

Вопрос что здесь не так, почему такое расхождение теории с практикой?

Трассировка SQL-запросов и профилировищик из СУБД гораздо точнее ответят вам на этот вопрос.