InventLocationId в InventTrans

[Logger]

Цитата:

Сообщение от Мартынов Дмитрий

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

Ну в данном случае речь идет не об увеличении мощности сервера, а как повлияет на производительность переход на 1 денормализованную табличку. На том же самом сервере.
Интересно было бы увидеть более развернутый ответ. А то вы вроде начали, а по существу ничего не написали. Отделались общими словами.

По поводу памяти - поясняю. На нашем проекте база крутится с 2005-го года. Стартовали на 3-ке. в 2006 году заметили явные подтормаживания на запросах когда был джоин по InventSum и InventDim с фильтром по складу и номенклатуре (ItemId like 'XXX%' . (Нам важно было достичь быстрого времени отклика - порядка доли секунды). Проблему решили денормализацией InventSum. Добавили туда поле склад, проиндексировали, а InventDim из джоина выкинули. Система словно задышала. Все сразу стало быстрее. Админ был очень доволен - сказал что память используется намного меньше.

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

Пока вижу причину в том что в 2006 году был другой сервер БД, в котором стояло совсем немного памяти. А сейчас памяти навалом.

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

[someOne]

Цитата:

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

Очень странно, что перенос склада в inventTrans дал такой прирост производительности. Могу поверить в типичный показатель накладных расходов на джойн двух таблиц в 40-50%. Могу поверить на нетипичные случаи с 200% накладных расходов. Не могу поверить в накладные расходы в 900%
Мне кажется, у вас там просто какая-то беда с планом запроса в стандартной оборотке. Может статистика кривая, может сам сиквел глючит почему-то, может индексы не перестраивались несколько лет. В общем - попробуйте план запроса выщемить и выложить.

Пожалуй соглашусь с этим.
Вот пример из реальной базы
inventTrans - 22 млн записей
inventDim - 4 млн записей

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

запрос, код которого ниже (вызывается одним из наших отчетов Аксапта)

Use Axapta;

SELECT SUM(A.QTY),
SUM(A.COSTAMOUNTPOSTED),
SUM(A.COSTAMOUNTADJUSTMENT),
A.ITEMID,
A.DIRECTION FROM INVENTTRANS A
WHERE
A.DATAAREAID=N'cmp' AND
A.DATEFINANCIAL>={ts '2010-12-01 00:00:00.000'} AND
A.DATEFINANCIAL<={ts '2010-12-31 00:00:00.000'} AND
EXISTS (SELECT 'x' FROM INVENTDIM B WHERE ((B.DATAAREAID=N'cmp') AND ((B.INVENTLOCATIONID=N'Магазин3') AND (A.INVENTDIMID=B.INVENTDIMID))))
GROUP BY A.ITEMID,A.DIRECTION ORDER BY A.ITEMID,A.DIRECTION

Возвращает результат (15 тыс строк) за 1 минуту 12 секунд. Что тут можно еще оптимизировать ?

Вряд ли добавление поля "код склада" в InventTrans как то повлияет на производительность...

Интересно а как с этим у других ?

[Zabr]

Цитата:

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

inventTrans - 22 млн записей
inventDim - 4 млн записей

Возвращает результат (15 тыс строк) за 1 минуту 12 секунд.

Интересно а как с этим у других ?

inventTrans - 72 млн записей
inventDim - 11 млн записей (склад,размер,партия)
170 складов, 40 тыс. номенклатур (это к тому, что у вас же там группировка и сортировка по ItemId).
Возвращает результат (10 тыс строк) за 5 мин 22 сек - очень даже есть что оптимизировать.

[Zabr]

Процитирую сам себя (это про выполнение запроса SomeOne):

Цитата:

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

Возвращает результат (10 тыс строк) за 5 мин 22 сек - очень даже есть что оптимизировать.

Мда. Тот же запрос на Х++ в Аксапте выполняется за 30 секунд.

[Logger]

Мне кажется, результат еще сильно должен зависеть от объема памяти который в среднем выделяется на сессию.

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

Так что неплохо бы знать еще объемы таблицы, индексов, числа юзеров и оперативки БД.

[fed]

Цитата:

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

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

Ты в общем прав, но мне кажется что это какой-то уж очень вырожденый случай должен быть, который тяжело в реальности встретить. Все-таки трудно представить что inventTrans по отдельности в память влез, для inentTrans+inventDim настолько не хватило памяти, что скорость упала в 10 раз (а не в 1.5-2). Хотя да - интересно сколько у них там памяти, какой размер inventDim и inventTrans за год в записях; какой размер этих таблиц в целом, ну и наконец интересно было бы посмотреть на планы исполнения самых ресурсоемких запросов из оборотки...

[Wamr]

someOne. тут много чего можно оптимизировать
а уж с полем склад вообще будет лётать

[someOne]

Цитата:

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

someOne. тут много чего можно оптимизировать
а уж с полем склад вообще будет лётать

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

По вашему после добавления поля "склад" в таблицу InventTrans он (этот запрос) будет исполняться за 2 секунды ? Я сильно в этом сомневаюсь...

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

Я, конечно, извиняюсь, но если речь идет об отчетах, то почему не делать прямые запросы к серверу через Connection + Statment + ResultSet? Это ускоряет выполнение отчета даже не в разы, а на порядки. Правда, за счет потери универсальности настроек отчета.

Именно по этой причине (потеря универсальности настроек отчета) такой подход невозможен в "стандарте". Но ведь речь идет о кастомизации под требования конкретного клиента. Т.е. не "общее для всех", а под одного конкретного клиента. Как частное решение - вполне уместно.

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

[Logger]

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

Кстати, неужели прямо в разы разница ?

[lev]

Цитата:

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

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

Кстати, неужели прямо в разы разница ?

Могу сказать за себя (про тот пример, который я приводил ранее в этой теме).

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

После добавления поля InventLocationId в таблицу InventTrans время работы отчетов, где была связка двух таблиц InventTrans + InventDim и на InventDim условие по складу, сократилось в 10-ки раз (с ~25-30 мин до ~1-2минут)!
Объяснения этому я нашел такие (не совсем технические):
1. Системе проще искать в одной большой таблице, чем в двух больших таблицах (все таки, как мне кажется, выбрать данные из 22млн строк быстрее, чем из 28 млн строк да ещё и в разных местах хранящихся). Что я имею ввиду. Например, нам нужно выбрать данные из InventTrans за один день, по одному складу. При выполлнении запроса из InventTrans выбирались данные за этот день, по всем складам, которых было порядка двухста (200) (да использовался индекс по дате, но все равно много записей)... потом выбирались аналитики (InventDim) с указанным складом (тут записей не очень много), и затем уже на основе полученных аналитик фильтровались проводки (InventTrans). И на все эти операции SQL тратило время.
2. В те времена использовался SQL2005, и как мне объясняли при джойнах SQL собирал временную табличку по двум табличкам, на это тоже тратилось время.

После добавления поля InventLocationId в InventTrans, система стала искать записи по индексу Дата+Склад (ну там ещё компания, но мы сейчас не про неё), в одной табличке, и поэтому SQL стало тратить время только на выобрку данных из индекса...

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

[Logger]

Про "разницу в разы" вопрос был к Владимиру Максимову.
Не верится что прямой запрос к БД настолько быстрее работает.

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

Цитата:

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

Про "разницу в разы" вопрос был к Владимиру Максимову.
Не верится что прямой запрос к БД настолько быстрее работает.

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

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

Если применительно к складской аналитике, то, например, получить остатки всех товаров на дату в разрезе складской аналитики средствами SQL на несколько порядков быстрее, чем средствами Axapta. Вот уж с этим я долго мучился Средствами Axapta отчет работает часами (без преувеличения), а средствами SQL - максимум несколько минут.

[Logger]

Цитата:

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

Если применительно к складской аналитике, то, например, получить остатки всех товаров на дату в разрезе складской аналитики средствами SQL на несколько порядков быстрее, чем средствами Axapta. Вот уж с этим я долго мучился Средствами Axapta отчет работает часами (без преувеличения), а средствами SQL - максимум несколько минут.

Так в чем причина такого поведения ?
Ограниченность X++ на котором не выразишь все идеи, которые можно сделать одним запросом на SQL ? Или накладные расходы внутри аоса при разборе полученной выборки и передаче её в X++ ?

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

Цитата:

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

Так в чем причина такого поведения ?
Ограниченность X++ на котором не выразишь все идеи, которые можно сделать одним запросом на SQL ? Или накладные расходы внутри аоса при разборе полученной выборки и передаче её в X++ ?

Одно следствие другого. Факт ограниченности X++ в данном вопросе приводит к катастрофическому росту накладных расходов. X++ просто физически не может решить задачу по другому.

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

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

[AndyD]

А можно я о своем, о мелком?

Поиск делением пополам на биллионах записей - это вы предлагаете, как альтернатива B-Tree?

[Мартынов Дмитрий]

Цитата:

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

А можно я о своем, о мелком?
Поиск делением пополам на биллионах записей - это вы предлагаете, как альтернатива B-Tree?

Ну раз вы знаете, что такое B-Tree, то вам будет несложно посчитать количество элементарный операций для поиска делением пополам и для поиска в B-Tree. Сделайте пожалуйста такую табличку, где видно максимальное количество элементарный операций для двух методов B-Tree и для поиска делением пополам для разного количества записей в таблице: для 1e+1, для 1e+2 записей, для 1е+3 записей..... и так до гугла (1е+100) можно с шагом 3 (т.е. тысяча, миллион, биллион.....). Надо сказать, что эта таблица с достаточной точностью считается в уме (без калькулятора), для тех кто помнит, что два в десятой это приблизительно 10 в третьей.

Чтобы не мучиться скажу вам сразу - разницы почти не будет..., точнее она будет не в ползу B-Tree. Это по тому, что мы смотрим на максимум элементарных операций, а B-Tree подразумевает большую рыхлость данных. Эта рыхлость эффективна при апдейтах а вот при поиске она дает лишние циклы...

[S.Kuskov]

Цитата:

Сообщение от Мартынов Дмитрий

Чтобы не мучиться скажу вам сразу - разницы почти не будет..., точнее она будет не в ползу B-Tree. Это по тому, что мы смотрим на максимум элементарных операций, а B-Tree подразумевает большую рыхлость данных. Эта рыхлость эффективна при апдейтах а вот при поиске она дает лишние циклы...

А вы учитываете, что дерево балансируется относительно значений хранящихся в таблице, а ваш "класический индекс" относительно их размера/местоположения? Т.е. вы допускаете, что величины в вашем биллионе записей могут быть распределены неравномерно?

[AndyD]

Речь идет об идеальной ситуации или мы как-то привязаны к тому, что имеем сейчас?

[fed]

К предположению gl00mie могу только добавить совет перед каждым прогоном теста, сбрасывать дисковый кэш сиквела с помощью комманды DBCC DROPCLEANBUFFERS
Кроме того, если считать что тесты 2 и 3 из сообщения участника pustik выполнялись в равноправных условиях (в смысле все данные уже были закэшированы), то разница в полотора раза - это нормально. Нет смысла копаться в планах запросов, скорее всего все и так хорошо...