fed: Ledger Balance Data in DAX

[Ivanhoe]

Цитата:

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

... Вот только пересчет балансов "очеловечили" бы, снабдив параметрами для задания периода - весь LedgerTrans перелопачивать каждый раз некрасиво как-то

Пересчет можно запустить из формы периодов по конкретному периоду

[fed]

Ладно - уговорили - пусть живут эти ваши балансы.
Хотя все равно не считаю задачу мгновенного получения баланса по счету ГК первостепенной для ERP-системы.

[glibs]

Цитата:

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

...
Ведь мы уже выяснили что обычно размеры сопоставимы. Даже если размер ledgerBalancesDimTrans составляет скажем 15-20% от размера ledgerTrans, время выполнения запроса по ней не падает в 5 раз.
...

Давайте найдем большую БД и проверим.

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

[fed]

Цитата:

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

Давайте найдем большую БД и проверим.

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

Ну насчет кластерных индексов - как раз сомнительно. Если у тебя стандартный индекс accountNum+dimension, а я ищу выборку по AccountNum+dimension[2], то индекс будет использоваться только для отбора всех проводок по счету. Фильтрация по аналитике будет делаться уже за счет данных, а не за счет индекса. И если условие по dimension[2] высокоселективно, то надо по нему индекс строить (типа transDate+accountNum+dimension[2]) (независимо от того - ищем мы по ledgerTrans или ledgerBalancesDimTrans). Может заметно быстрее получиться - несмотря на лукап... Кстати - длинный ключ кластерного индекса по ledgerBalances тут может негативную роль сыграть...

[Ivanhoe]

Посмотрел две живые БД. За базу берется значение для таблицы проводок ГК, ниже приведено отношение значения для соответствующей таблицы к базе. Данные взяты стандартным отчетом "Размер компании".

БД1 кол-во записей:
Проводки = 1,000
Сальдо ГК = 0,012
Сальдо ГК + аналит = 0,066
БД1 объем таблицы:
Проводки = 1,000
Сальдо ГК = 0,020
Сальдо ГК + аналит = 0,129

БД2 кол-во записей:
Проводки = 1,000
Сальдо ГК = 0,016
Сальдо ГК + аналит = 0,226
БД2 объем таблицы:
Проводки = 1,000
Сальдо ГК = 0,282
Сальдо ГК + аналит = 5,038

[glibs]

Цитата:

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

...
Ну насчет кластерных индексов - как раз сомнительно.
...

Не конкретно.

Цитата:

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

...
Если у тебя стандартный индекс accountNum+dimension, а я ищу выборку по AccountNum+dimension[2], то индекс будет использоваться только для отбора всех проводок по счету. Фильтрация по аналитике будет делаться уже за счет данных, а не за счет индекса.
...

Верно. Но если индекса по AccountNum+dimension[2] нет и на LedgerTrans, а его там нет, то выборка по кластерному индексу приведет к эффекту сканирования фрагментов кластерного индекса на LedgerBalancesdimTrans. Т.е. произойдет а-ля сканирования таблицы на самом деле, но по отдельным кускам таблицы. Это оптимальный подход к выборке данных в данном случае. Да, фильтрация по данным будет.

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

Индекс некластерный эффективен при выборке одной или нескольких записей из большой таблицы.

Цитата:

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

...
И если условие по dimension[2] высокоселективно, то надо по нему индекс строить (типа transDate+accountNum+dimension[2]) (независимо от того - ищем мы по ledgerTrans или ledgerBalancesDimTrans). Может заметно быстрее получиться - несмотря на лукап
...

Я говорил про стандартные индексы. На все случаи жизни индексов не настроишь. Индексирование — дело разумного компромисса.

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

Цитата:

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

...
Кстати - длинный ключ кластерного индекса по ledgerBalances тут может негативную роль сыграть
...

О чем идет речь?

Блокировки? Скорость разноски на OLTP операциях?

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

[fed]

Цитата:

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

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

Не совсем так. MS SQL прочитает из страниц некластерного индекса rowId страниц с данными (то есть - сочетание номер файла:номер страницы:смешение в странице), отсортирует по файлам и страницам и начнет читать. Каждая страница будет прочитана один раз. (Насчет нехватки памяти - один rowId то ли 10 то ли 12 байт памяти занимает, вряд ли памяти в систем не хватит, даже если идет чтение 10 миллионов записей). Тут правда, действительно, есть одно "но": Если по кластерному индексу у нас надо выбрать 60% записей, то система 60% страниц и прочитает. А вот по таблице с heap-org, с большой вероятностью данные из этих 60% записи разбросаны по всем станицам и запрос выльется в fullscan.
[/QUOTE]

Цитата:

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

Я говорил про стандартные индексы. На все случаи жизни индексов не настроишь. Индексирование — дело разумного компромисса.

Ну да - согласен.

.

Цитата:

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

О чем идет речь?

Ну если у тебя по таблице есть кластерный индекс, то все остальные индексы содержат не rowId, а значение ключа кластерного индекса. Поскольку ключ в стандартном ledgerBalancesDimTrans длинный, то размер индексной запси (ключ некластерного индекса+ключ для поиска по кластерному индексу) будет заметно больше. Длинее индексная запись - меньше индексных записей в странице. Меньше индексных записей в странице - выше дерево. Выше дерево - больше операций чтения.
Кроме того - если я отбираю в некластерном индексе те же 60% записей, то опять таки может сложится ситуация при котором эти 60% записей раскиданы по всем страницам кластерного индекса. И все это выльется в итоге в фулл-скан.

Так что для того чтобы кластерный индекс был действительно эффективным, надо чтобы большая часть поисков (ну то есть - эдак процентов 70 хотя бы) выполнялось всегда по одному и тому же ключу. Это хорошо выполняется для custTable, например а вот для LedgerBalanceDimTrans - не выполняется, поскольку фильтрация всегда идет по сочетанию Счет+некий набор аналитик. Просто я тут наблюдал как работает некоторое специализированное распределение счетов, в которой автор как раз использует ledgerBalancesDimTrans, и для разных видов затрат задает разные фильтры по аналитикам. Так вот - индекс использовался только для фильтрации по номеру счета+дата.

[glibs]

Цитата:

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

...
Не совсем так.

Спасибо. Я действительно с процессами доступа к данным в MS SQL не знаком. Представление складываю на основании удачных попыток оптимизации производительности.

Цитата:

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

...
MS SQL прочитает из страниц некластерного индекса rowId страниц с данными (то есть - сочетание номер файла:номер страницы:смешение в странице), отсортирует по файлам и страницам и начнет читать. Каждая страница будет прочитана один раз.

Я с трудом себе это представляю если в запросе будет сортировка или группировка или join. Это общий случай описан или применительно к суммированию LedgerBalancesdimTrans?

Цитата:

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

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

Спасибо.

Цитата:

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

...
Кроме того - если я отбираю в некластерном индексе те же 60% записей, то опять таки может сложится ситуация при котором эти 60% записей раскиданы по всем страницам кластерного индекса. И все это выльется в итоге в фулл-скан.
...

60% записей — это full scan как ни крути. Исключение — если условие совпадает с кластерным индексом. Тогда это 60 процентов full scan.

Разумеется, если в условии запроса не будет критериев по первым полям кластерного индекса, то будет full scan. О том что кластерный индекс исключает full scan речи не было и быть не могло.

Цитата:

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

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

Я бы не стал говорить про эффективность индекса вообще. Это больше похоже на теорию. Меня больше интересуют конкретные запросы и скорость их работы. Если какие-то запросы из-за индекса работают быстро — хорошо. Если он чему-то реально мешает — плохо. Дальше думать.

Цитата:

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

...
а вот для LedgerBalanceDimTrans - не выполняется, поскольку фильтрация всегда идет по сочетанию Счет+некий набор аналитик.
...

Сначала код компании, потом дата, потом счет, потом сканирование по данным уже по аналитикам. Для запросов по суммированию проводок с начала времен это почти оптимально.

Цитата:

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

...
Так вот - индекс использовался только для фильтрации по номеру счета+дата.
...

Если там тоже суммируются проводки с начала времен — это уже отличный результат. Иначе был бы full scan. И даже выборка по некластерному индексу большого количества данных может оказаться хуже full scan.

А проектировать систему под full scan...

[glibs]

Кстати про LedgerBalancesDimTrans.

Есть еще очень экстремальная, но смертельно убойная вещь.

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

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

В эксклюзивных случаях я таким балуюсь.

Жаль, кстати, что управленческий и налоговый учет не отключаются параметрически/конфигурационно. Это к вопросу о размере записи в таблице.

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

[mazzy]

Цитата:

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

Ок. Беру паузу. Практический эксперимент попробую сделать ночером.

отмотал назад по версиям... пока до 2.5...
нашел конкорд, но не помню пароля.
посмотрел что было раньше.

Цитата:

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

Не, Денис, ты ошибаешься.
Здесь тебе стоит пересмотреть свои взгляды.

Просто у денежных полей в таблицах LedgerBalancesTrans и LedgerBalancesDimTrans
установлено свойство FieldUpdate = Relative.
http://msdn.microsoft.com/en-us/library/aa577032.aspx

Это значит, что программисту не надо писать Update, не нужно маятся с вилкой (find?update:insert).
Программисту достаточно давать команду Insert.
Система сама сложит, если найдет уже существующую запись.
Это свойство пришло еще из Конкорда.

похоже это мне нужно пересматривать взгляды.

Цитата:

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

Попробывал - не получилось.
Создал в ax2009 таблицу с двумя полями: текстовое Id, числовое Amount с FieldUpdate = Relative. Уникальный индекс по полю Id.
Джобом пытаюсь добавлять, обновлять запись разными способами - никак не получается воспроизвести ситуацию, что бы поле Amount обновлялось с учетом предыдущего значения. Каждый раз значение поля переписывается.

Может, я что-то не так делаю?

да. у меня такая же ситуация.
в 2.5, 3.0, 4.0, 2009
пока считаю, что это я что-то серьезно напутал.

мало того, в ax2.5 суммовые поля в таблицах LedgerBalances не являются Relative!

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

[AndyD]

Сергей, для первого скриншота write() - это просто замена проверки существования записи.
Т.е. вместо

select firstonly forupdate ledgerBalances
    where ledgerBalances.accountNum == accountNum    &&
              ledgerBalances.transDate   == transDate    &&
              ledgerBalances.periodCode == periodCode;
if (ledgerBalances)
{
    ...
    ledgerBalances.update();
}
else
{
    ...
    ledgerBalances.insert();
}

просто делается write(), который определяет, был ли получен буфер из базы или он неинициализирован и вызывает либо update(), либо insert() внутри.

Обрати внимание, что всегда записываются значения в

ledgerBalances.accountNum   = accountNum;
ledgerBalances.transDate    = transDate;
ledgerBalances.periodCode   = periodCode;

А Relative просто при обновлении записи делает

update ledgerBalancesTrans 
set debitMST = debitMST + delta
where ...

где delta = (ledgerBalances.debitMST - ledgerBalances.orig().debitMST) и рассчитывается в Аксапте, вместо

update ledgerBalancesTrans 
set debitMST = value
where ...

где value = ledgerBalances.debitMST

[mazzy]

Цитата:

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

Сергей, для первого скриншота write() - это просто замена проверки существования записи.
Т.е. вместо...

1.
2. Спасибо
3. Я просто хотел сказать, что раньше был не только insert

[mazzy]

Цитата:

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

А Relative просто при обновлении записи делает

update ledgerBalancesTrans 
set debitMST = debitMST + delta
where ...

где delta = (ledgerBalances.debitMST - ledgerBalances.orig().debitMST) и рассчитывается в Аксапте, вместо

update ledgerBalancesTrans 
set debitMST = value
where ...

где value = ledgerBalances.debitMST

Хочешь сказать, оно так должно работать. Я тоже так думал.
только я всегда думал, что дельтой является просто ledgerBalances.debitMST

Попробуй Job сделать.
У меня сходу не получилось. Тупо записывается последнее значение. Видимо что-то неправильно делал.

[AndyD]

Вот такой код

    Table3  table3;
    ;
    ttsbegin;
    select forupdate table3;
    table3.Field1 = table3.Field1+10;
    table3.update();
    ttscommit;

с включенным Relative уходит такой запрос

UPDATE TABLE3 SET FIELD1=(FIELD1+@P1),RECVERSION=((((cast(2147483647 as bigint) + RECVERSION+@P2 - 1)%2147483647) + 1)) WHERE ((DATAAREAID=@P3) AND (RECID=@P4))

где @P1=10.0
а с Absolute такой

UPDATE TABLE3 SET FIELD1=@P1,RECVERSION=@P2 WHERE (((DATAAREAID=@P3) AND (RECID=@P4)) AND (RECVERSION=@P5))

Это на 2009-й Ax

[AndyD]

Кстати, обрати внимание на использование RECVERSION с включенным Relative.

Он не используется в предложении WHERE и не проверяется его изменение с момента последнего чтения записи.
Т.е. если между select и update другая транзакция успела изменить данные (и RECVERSION в том числе), то update успешно завершится без генерации исключения конфликта версий.

Аксапийная оптимистическая конкуренция (а у меня она включена по умолчанию) для таких полей не используется.

Замечу, что и при чтении с select forUpdate при этом не происходит блокировки записи.
Для того, что бы накладывалась блокировка, надо использовать select pessimisticLock

PS По-моему, от темы блога совсем в сторону отклонились

[mazzy]

Цитата:

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

Замечу, что и при чтении с select forUpdate при этом не происходит блокировки записи.
Для того, что бы накладывалась блокировка, надо использовать select pessimisticLock

PS По-моему, от темы блога совсем в сторону отклонились

Да, похоже.
Зато выяснили, что на LedgerBalances* не ставятся блокировки

[bodeaux]

Здравствуйте.

Решил возобновить тему, чтобы еще раз попытаться разобраться с FieldUpdate = Relative

С помощью таких действий

static void Job_1(Args _args)
{
    Ltable t_1;    Ltable t_2;    Ltable t_3;;   
    // point_0
    ttsbegin;
    {// block_1
        select forupdate t_1 where t_1.id == 5;
        {// block_2
            select forupdate t_2 where t_2.id == 5;
            { // block_3
                select forupdate t_3 where t_3.id == 5;
                t_3.Val = 14;  t_3.update();
            }
            t_2.Val = 700;  t_2.update();
        }
        t_1.Val = 50000;  t_1.update();
    }
    ttscommit;
}

удалось установить, что в подобных случаях в таблице остается значение

W = V + (-V + U_1) + (-V + U_2) + ... + (-V + U_n),

где V - значение в точке point_0, U_i - значение, переданное в update номер i.

В частности, в самых интересных для практики случаях, которые обсуждались выше, получаем
1. i = 1 : W = U_1 (отличия от FieldUpdate = Absolute не видны).
2. i = 2 :
после первого запуска джоба имеем W = U_1 + U_2 - V
после второго запуска джоба имеем W = V
(любители арифметики могут убедиться, что при других i подобных циклов нет).
----------------------
Надеюсь, кто-нибудь сможет пояснить, для чего заложена именно такая формула для Relative update,
поскольку из предыдущего обсуждения мне показалось,
что люди ожидали работы по формуле W = V + U_1 + U_2 + ... + U_n.


В качестве офтопа еще хочу спросить, почему не возникает deadlock при выполнении block_2 после block_1.

[gl00mie]

Цитата:

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

удалось установить, что в подобных случаях в таблице остается значение W = V + (-V + U_1) + (-V + U_2) + ... + (-V + U_n), где V - значение в точке point_0, U_i - значение, переданное в update номер i.
Надеюсь, кто-нибудь сможет пояснить, для чего заложена именно такая формула для Relative update, поскольку из предыдущего обсуждения мне показалось, что люди ожидали работы по формуле W = V + U_1 + U_2 + ... + U_n.

Relative Update предполагает именно относительное изменение значения поля. Т.е. эта возможность работает "логично", когда вам не важно прежнее значение, а важно лишь приращение. Если бы у вас был такой код (обратите внимание на инкремент вместо присваивания):

select forupdate t_1 where t_1.id == 5;
{// block_2
    select forupdate t_2 where t_2.id == 5;
    { // block_3
        select forupdate t_3 where t_3.id == 5;
        t_3.Val += 14; t_3.update();
    }
    t_2.Val += 700; t_2.update();
}
t_1.Val += 50000; t_1.update();

То все отработало бы так, как ожидается, правда?

Цитата:

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

В качестве офтопа еще хочу спросить, почему не возникает deadlock при выполнении block_2 после block_1.

Блокировки нужны для синхронизации параллельного доступа к одним и тем же записям, а тут все все выборки forupdate происходят в рамках одного соединения с БД, поэтому deadlock не возникает: в рамках одного соединения синхронизировать по определению нечего. Более интересный вопрос: почему при обновлении в случае оптимистичной конкурентной модели не возникает конфликт обновления после block_3. Ответ на этот вопрос можно найти в книге Inside Dynamics AX 2009:

Цитата:

...К счастью, среда времени выполнения Microsoft Dynamics AX управляет данной ситуацией. При исполнении оператора обновления среда времени выполнения находит все другие буфера записей, содержащие ту же запись, и если при этом они были извлечены с ключевым словом forupdate, то среда времени выполнения изменяет значение поля RecVersion данных буферов записей на новое значение из базы данных. Следовательно, второе обновление не вызовет ошибки.

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

[AndyD]

Цитата:

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

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

Если верить подписи, то у bodeaux Ax3 SP4 без каких-либо KR.
Так что, убедиться будет проблематично