InventLocationId в InventTrans

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

Цитата:

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

А почему в данном случае не зависит ? Чем он отличается от общего случая ? Как-то вы загадками говорите.

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

А ключевая проблема в том что выборка по двум таблицам принципиально плохооптимизируемая операция. Например некорректное решение структуры данных при объемах данных порядка биллиона операций хоронит проект...
Но чаще всего мы работаем с маленькими таблицами. При этом мы работаем кое как - и все работает быстро. И тогда лагание на десятках миллионов записей всех ставит в тупик...

[fed]

Цитата:

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

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

No comments. Если делаем правильные структуры, можно жить на голых таблицах - без B-Tree. Вероятно будет пересортировывать после каждой вставки и обновления. Очевидно это тот самый "научный подход" в действии.

Цитата:

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

Хотите - можно написать на Х++, вместо таблиц здесь можно использовать контейнер без операции поиска, только с запоминанием индекса элемента. Суть задачи сводится к выборке по двум таблицам и четырем индексам: фильруем по полю 1 в первой таблице, связываем по полю 2 таблицы, фильтруем по полю 3 вторую таблицу. В первой таблице есть 2 индекса по полю 1 и полю 2 соответственно, аналогично во второй есть индексы по полям 2 и 3.

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

Цитата:

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

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

Не хочешь чтобы над тобою стебались, почитай для начала Вирта "Алгоритмы и структуры данных (про B-Деревья и Хэш-таблицы) и почитай в BOL про виды джойнов в SQL Server. Еще можно почитать блог Craig Freeman Так что знания не тайные, просто, вероятно, твой ''научный подход" - это такое политически корректный термин для "невежество".

[Logger]

Цитата:

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

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

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

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

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

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

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

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

Цитата:

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

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

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

[Logger]

Цитата:

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

Не поленился. На тестовой базе сделал аля InventTrans2. Сделал поле Склад. Джобом перекопировал данные из стандартного InventTrans(c учетом склада) в новый InventTrans2. Отрихтовал оборотку (убрал из джоина InventDim). Отчет выдал информацию в 10 раз быстрее, причем за год, по всем складам. По конкретному складу, вообще нечего говорить, если настроить в InventTrans2 индекс.

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

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

Цитата:

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

No comments. Если делаем правильные структуры, можно жить на голых таблицах - без B-Tree. Вероятно будет пересортировывать после каждой вставки и обновления. Очевидно это тот самый "научный подход" в действии.

Если мы не имеем аппаратных средств копирования блоков памяти, то при качественном проектировании БД В-дерево проиграет классическому индексу. Кстати и в обратную сторону - если у нас есть аппаратные средства сортировки то опять же сортировка выиграет. Но технически копирования блоков памяти фиксированного размера в железе реализуется проще, по этому все перешли на В-трее...

Цитата:

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

Не хочешь чтобы над тобою стебались, почитай для начала Вирта "Алгоритмы и структуры данных (про B-Деревья и Хэш-таблицы) и почитай в BOL про виды джойнов в SQL Server. Еще можно почитать блог Craig Freeman Так что знания не тайные, просто, вероятно, твой ''научный подход" - это такое политически корректный термин для "невежество".

За Вирта спасибо, вообщето начать надо с Тьюринга и Черча... сижу изучаю....Дейкстру почитываю... Кстати, из наших рекомендую прежде всего Ершова.

[S.Kuskov]

Цитата:

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

Если мы не имеем аппаратных средств копирования блоков памяти, то при качественном проектировании БД В-дерево проиграет классическому индексу.

Что вы называете "классическим индексом"?

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

Цитата:

Сообщение от S.Kuskov

Что вы называете "классическим индексом"?

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

[AndyD]

Цитата:

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

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

Прошу прощения, а что имеет в виду под "Отсортированной таблицей"?
И как в этой таблице искать?

[S.Kuskov]

Цитата:

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

Прошу прощения, а что имеет в виду под "Отсортированной таблицей"?
И как в этой таблице искать?

Уточню вопрос. Чем поиск по отсортированной таблице отличается от поиска по неотсортированной?

[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 как то повлияет на производительность...

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

[Wamr]

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

[someOne]

Цитата:

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

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

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

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

[Zabr]

Цитата:

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

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

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

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

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

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

Я, конечно, извиняюсь, но если речь идет об отчетах, то почему не делать прямые запросы к серверу через 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++ ?