Временные SQL таблички в ax2009 - DAX: Программирование

Logger · 16.01.2019, 19:18

Всем привет.

Коллеги, доводилось слышать, что в 2009-й аксапте можно в AOT объявить табличку название которой начинается с #

Тогда SQL Server при синхронизации будет создавать времянку в SQL и этим можно успешно пользоваться. Попробовал руками в AOT ее создать - не дает. Импортом из xpo - тоже.

Если кто так делал, поделитесь как это можно сделать.

db · 16.01.2019, 20:28

Делал так - создавал обычную таблицу и переименовывал джобом

X++:

static void rdol_createSQLTmpTable(Args _args)
{
    TreeNode    tn= TreeNode::findNode("\\Data Dictionary\\Tables\\mInventSumDeltaKeyTmp");
    ;

    tn.AOTsetProperties('PROPERTIES\nName\n#\#mInventSumDeltaKeyTmp\nENDPROPERTIES');
    tn.AOTsave();
}

TasmanianDevil · 17.01.2019, 06:28

Ругается при попытке синхронизации из AOT таблицы с измененным таким образом именем.

У меня SQL сервер 10.0.6556

Logger · 17.01.2019, 07:14

Сделайте ее временной inMemory. Или коныигурауионным ключом отключите. Тогда при синзронизации она удалится из базы.
Затем верните обратно. При синхронизации она создастся. Но уже как sql времянка.

TasmanianDevil · 17.01.2019, 09:56

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

S.Kuskov · 17.01.2019, 13:17

Попробуйте через QueryRun или через DictTable(tableId).makeRecord Получить значение поля по TableId, FieldId, RecId

TasmanianDevil · 18.01.2019, 07:11

Цитата:

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

Получить значение поля по TableId, FieldId, RecId

S.Kuskov, спасибо, но мне не проблема через Dict-классы играться с данными произвольных таблиц и пользоваться произвольными вызовами произвольных методов таблиц/объектов

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

X++:

tablenum(MyTempTable)

и

X++:

fieldnum(MyTempTable, MyField)

теперь использовать нельзя из-за наличия в имени символа #.
Хардкодить коды таблиц/полей- не вариант.
Единственный способ замены tablenum()/fieldnum() вижу только в поиске в AOT по имени соответствующих узлов и сбор оттуда ID-шников.

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

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

Logger · 18.01.2019, 07:26

Может попробовать сделать табличный мап без # в именах и работать через него.
Тогда можно обойтись без common. Только new dictTable(...).makeRecord при инициализации.

**mazzy** · 18.01.2019, 11:03

Цитата:

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

Единственный способ замены tablenum()/fieldnum() вижу только в поиске в AOT по имени соответствующих узлов и сбор оттуда ID-шников.

Нет, конечно

Цитата:

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

Вторая проблема - ухудшение читабельность кода и затруднение отладки при работе с данными через Dict-классы и Common.

У Ромы там куча вспомогательных утилит для работы. Очень удобных.

fieldnum(MyTempTable, MyField) - не самая удобная конструкция

Ром, опубликуй, наконец.
Ты сделал реально круто!

Заодно и отрефакторишь

TasmanianDevil · 18.01.2019, 13:28

Цитата:

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

Нет, конечно

А что еще у нас есть для решения задачи поиска ID таблицы и ее полей по известным имени и типу искомого объекта ? Таблица UtilIdElements? Можно и ее ...

Только вот в обоих случаях для поиска ID придется передавать строчные константы наименования объектов и ошибки в них придется ловить только в run-time. Не кошерно как-то это все по сравнению с халяльными tablenum() /fieldnum(), которых мы лишаемся из-за использования в названии таблицы символа #, и в которых компилятор сразу нас мордой тычет в ошибки, если что не так.

Предложенная Logger'ом идея работы через Map в AOT как-то ближе

**mazzy** · 18.01.2019, 15:15

Цитата:

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

Единственный способ замены tablenum()/fieldnum() вижу только в поиске в AOT по имени соответствующих узлов и сбор оттуда ID-шников.

Цитата:

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

Нет, конечно

Цитата:

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

А что еще у нас есть для решения задачи поиска ID таблицы и ее полей по известным имени и типу искомого объекта ? Таблица UtilIdElements?

tablestr
fieldstr

если же речь идет о таблицах с первым символом #
то можно использовать таблицу SqlDictionary или Dictionary.TableCnt2Id()

treeNode - вполне себе способ.
но это НЕ единственный способ.

а для работы с DictTable, вообще говоря, достаточно ID таблицы.

db · 18.01.2019, 17:28

Как обращаться к такой таблице в коде? Первый вариант уже сказали - табличный мап и инициализация через MakeRecord. Второй вариант - дублировать таблицу чтобы FieildId были такие как в таблице с решетом и то-же инициализация через MakeRecord. В этом случае заработают insert-update-delete_recordset. Этакий хакерский мап в виде таблицы.

Работа с мапом обычным (mInventSumDeltaKeyMap - мап)

X++:

   mInventSumDeltaKeyMap   inventSumDeltaKey = mInventSumDeltaKeyMap::tmpTableInit();
    InventSum               inventSum;
    InventSum               inventSumNew;
    RecordInsertList        insertList;
    ;
    while select ItemId, InventDimId from inventSumDeltaKey
        group by ItemId, InventDimId
        notexists join inventSum
            where inventSum.ItemId         == inventSumDeltaKey.ItemId &&
                  inventSum.InventDimId    == inventSumDeltaKey.InventDimId
    {
        if (!inventSumDeltaKey.ItemId || !inventSumDeltaKey.InventDimId)
        {
            throw error(strfmt("@SYS68912",funcname()));
        }
        if (! insertList)
        {
            insertList = new RecordInsertList(tablenum(InventSum), true, true, true);
        }
        inventSumNew.ItemId         = inventSumDeltaKey.ItemId;
        inventSumNew.InventDimId    = inventSumDeltaKey.InventDimId;
        inventSumNew.ClosedQty      = NoYes::Yes;
        inventSumNew.Closed         = NoYes::Yes;
        insertList.add(inventSumNew);
    }
    if (insertList)
    {
        insertList.insertDatabase();
    }

или так (mLedgerBalancesTransDeltaMap - мап)

X++:

    mLedgerBalancesTransDeltaMap    ledgerBalancesTransDelta = trecord::init(this.getBalanceDeltaTableId());
    ;
    unchecked(Uncheck::TableSecurityPermission)
    {
        insert_recordset
            ledgerBalancesTrans (AccountNum, PeriodCode, TransDate, SystemGeneratedUltimo, DebitMST,
                                 CreditMST, DebitOPRMST, CreditOPRMST, DebitTaxMST, CreditTaxMST, DebitMSTSecond,
                                 CreditMSTSecond, DebitOPRMSTSecond, CreditOPRMSTSecond, DebitTaxMSTSecond, CreditTaxMSTSecond, Qty)
        select forceLiterals
            LedgerAccountNum, PeriodCode, TransDate, SystemGeneratedUltimo, sum(DebitMST), sum(CreditMST), sum(DebitOPRMST), sum(CreditOPRMST), sum(DebitTaxMST),
            sum(CreditTaxMST), sum(DebitMSTSecond), sum(CreditMSTSecond), sum(DebitOPRMSTSecond), sum(CreditOPRMSTSecond), sum(DebitTaxMSTSecond), sum(CreditTaxMSTSecond), sum(Quantity)
        from
            ledgerBalancesTransDelta group by LedgerAccountNum, PeriodCode, TransDate, SystemGeneratedUltimo;
    }

Работа с таблицей для подмены имени (mInventSumDateTransTmp - таблица)

X++:

    mInventSumDateTransTmp      transTmp = trecord::init(_tableId, false, true);
    mInventSumDateDim           dim;
    mInventSumDateTrans         trans;

    #mInventSumDateDimDevelop

    insert_recordset transTmp (TransDate, ItemId, FinancialDimId)
        select maxof(TransDate), ItemId, FinancialDimId from trans
            group by ItemId, FinancialDimId
            where trans.TransDate <= transDate
               && (!inventSumDateDimParmCriteria.ItemIdFlag || (trans.ItemId == inventSumDateDimCriteria.ItemId))
            join dim
                where dim.FinancialDimId == trans.FinancialDimId
                   && (!inventSumDateDimParmCriteria.InventSiteIdFlag          || (dim.InventSiteId          == inventSumDateDimCriteria.InventSiteId         ))
                   && (!inventSumDateDimParmCriteria.InventLocationIdFlag      || (dim.InventLocationId      == inventSumDateDimCriteria.InventLocationId     ))
                   && (!inventSumDateDimParmCriteria.InventProfileIdFlag       || (dim.InventProfileId       == inventSumDateDimCriteria.InventProfileId      ))
                   && (!inventSumDateDimParmCriteria.InventProfileItemTypeFlag || (dim.InventProfileItemType == inventSumDateDimCriteria.InventProfileItemType))
                   && (!inventSumDateDimParmCriteria.AccountFlag               || (dim.Account               == inventSumDateDimCriteria.Account              ))
                   && (!inventSumDateDimParmCriteria.Account2Flag              || (dim.Account2              == inventSumDateDimCriteria.Account2             ))
                   && (!inventSumDateDimParmCriteria.AccountOffsetFlag         || (dim.AccountOffset         == inventSumDateDimCriteria.AccountOffset        ))
                   && (!inventSumDateDimParmCriteria.AccountOffset2Flag        || (dim.AccountOffset2        == inventSumDateDimCriteria.AccountOffset2       ))
                   && (!inventSumDateDimParmCriteria.StornoFlag                || (dim.Storno                == inventSumDateDimCriteria.Storno               ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[1]          || (dim.Dimension[1]          == inventSumDateDimCriteria.Dimension[1]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[2]          || (dim.Dimension[2]          == inventSumDateDimCriteria.Dimension[2]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[3]          || (dim.Dimension[3]          == inventSumDateDimCriteria.Dimension[3]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[4]          || (dim.Dimension[4]          == inventSumDateDimCriteria.Dimension[4]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[5]          || (dim.Dimension[5]          == inventSumDateDimCriteria.Dimension[5]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[6]          || (dim.Dimension[6]          == inventSumDateDimCriteria.Dimension[6]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[7]          || (dim.Dimension[7]          == inventSumDateDimCriteria.Dimension[7]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[8]          || (dim.Dimension[8]          == inventSumDateDimCriteria.Dimension[8]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[9]          || (dim.Dimension[9]          == inventSumDateDimCriteria.Dimension[9]         ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[10]         || (dim.Dimension[10]         == inventSumDateDimCriteria.Dimension[10]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[11]         || (dim.Dimension[11]         == inventSumDateDimCriteria.Dimension[11]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[12]         || (dim.Dimension[12]         == inventSumDateDimCriteria.Dimension[12]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[13]         || (dim.Dimension[13]         == inventSumDateDimCriteria.Dimension[13]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[14]         || (dim.Dimension[14]         == inventSumDateDimCriteria.Dimension[14]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[15]         || (dim.Dimension[15]         == inventSumDateDimCriteria.Dimension[15]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[16]         || (dim.Dimension[16]         == inventSumDateDimCriteria.Dimension[16]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[17]         || (dim.Dimension[17]         == inventSumDateDimCriteria.Dimension[17]        ))
                   && (!inventSumDateDimParmCriteria.DimensionFlag[18]         || (dim.Dimension[18]         == inventSumDateDimCriteria.Dimension[18]        ));
}

Не в коня корм от задачи зависит как обычно. В моем случае темповыми стали InventSumDelta и LedgerBalancesTransDelta. Результат - ПОЛНОЕ отсутствие блокировок при обновлении запасов в наличии и балансов по ГК. Полное означает не облегчение, а устранение проблемы на корню и возможность многопоточной разноски документов в тысячи реально параллельных не ждущих друг друга потоков. И такие странные методы стоили достигнутого результата

**fed** · 19.01.2019, 17:12

Цитата:

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

Не в коня корм от задачи зависит как обычно. В моем случае темповыми стали InventSumDelta и LedgerBalancesTransDelta. Результат - ПОЛНОЕ отсутствие блокировок при обновлении запасов в наличии и балансов по ГК. Полное означает не облегчение, а устранение проблемы на корню и возможность многопоточной разноски документов в тысячи реально параллельных не ждущих друг друга потоков. И такие странные методы стоили достигнутого результата

Хотя это некоторый офтопик в этой теме, но проблема блокировок и дидлоков по LedgerBalancesTransDelta лечится простым удалением индекса по RecId. Просто ставишь в свойствах таблицы createRecIdIdx==No и проблема решается.

Raven Melancholic · 20.01.2019, 10:29

Цитата:

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

Просто ставишь в свойствах таблицы createRecIdIdx==No и проблема решается.

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

**fed** · 20.01.2019, 14:08

Цитата:

Сообщение от Raven Melancholic

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

В LedgerBalancesTransDelta есть нормальный кластерный индекс, в котором в самом начале стоит SessionId. Соответственно, если этот индекс используется, то конфликтов между сессиями не возникает, поскольку все поиски идут с указанием сессии, и, соответственно, SQL Server вешает блокировки на разные ключи и страницы. (Вероятно может все-таки блокировка случаться, если несколько разных сессий в одну страницу попало, но это не очень часто случается). Проблема в том, что авторы фичи добавили еще и индекс по RecId (который реально использоваться не должен). Почему добавили - не знаю. Может машинально, может долбоархитекторы заставили. Проблема в том, что табличка эта - часто обновляется и основной кластерный индекс становится очень фрагментированым. В этой ситуации, SQL Server время от времени решает что надо использовать индекс по RecIc, но только для того, чтобы отобрать записи по условию dataaareaid. В итоге - вешаются блокировки на заметную часть этого самого индекса, поскольку записи с одинаковым SessionId могут там попасть в совсем разные страницы.
А когда мы индекс по RecId удаляем, системе не остается ничего другого кроме как использовать единственный кластерный индекс. А при этом блокировки случаются эдак на 2-3 порядка реже чем при использовании индекса по RecId.
Ну то есть - решение Романа, вероятно, еще более универсально, поскольку вероятность блокировок доводит до нуля, но отрубить индекс по RecId не долго, а результат дает почти такой же, как и использование временной таблицы....

Raven Melancholic · 20.01.2019, 16:07

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

**fed** · 20.01.2019, 16:50

Цитата:

Сообщение от Raven Melancholic

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

Сформулируем это так: неудачный индекс не только увеличивает время обновления, но и вызывает повышенный риск блокировок. Просто на некоторых стадиях поиска по индексу, SQL блокирует не только те записи, которые он реально обновил, но и просто те записи, которые он собирается прочитать. Если индекс построен таким образом, что при обновлении, поиск по индексу не очень селективный, вероятность дидлоков и долгих блокировок изрядно возрастает.

Ace of Database · 20.01.2019, 17:37

Цитата:

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

Не в коня корм от задачи зависит как обычно. В моем случае темповыми стали InventSumDelta и LedgerBalancesTransDelta. Результат - ПОЛНОЕ отсутствие блокировок при обновлении запасов в наличии и балансов по ГК. Полное означает не облегчение, а устранение проблемы на корню и возможность многопоточной разноски документов в тысячи реально параллельных не ждущих друг друга потоков. И такие странные методы стоили достигнутого результата

Ура, значит теперь АХ2012 будет работать так же быстро как АХ3.0! Надо попробовать

Raven Melancholic · 20.01.2019, 18:22

Цитата:

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

Сформулируем это так: неудачный индекс не только увеличивает время обновления, но и вызывает повышенный риск блокировок.

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

Raven Melancholic · 20.01.2019, 18:37

Цитата:

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

Не в коня корм от задачи зависит как обычно. В моем случае темповыми стали InventSumDelta

Не секрет, что в InventOnHand* в стандарте по каким-то причинам не учитывается InventSumDelta, приходится дописывать.
Если перевести InventSumDelta в TempDB, то обращение в отдельном классе будет обращаться к той же темповой таблице, что и в другом месте или нужны какие-то дополнительные действия?
В DAX2012, чтобы в разных местах были обращения к одной и той же темповой таблице из TempDB приходится немного помашанить иначе разные курсоры создают разные #что-то_в_TempDB.

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
emeadaxsupport: AX Performance - Analyzing key SQL Server configuration and database settings	Blog bot	DAX Blogs	0	28.09.2015 14:11
Какое оптимальное сочетание версий SQL и AX2009 ?	AXcons	DAX: Администрирование	14	02.09.2015 11:27
emeadaxsupport: AX Performance Troubleshooting Checklist Part 1A [Introduction and SQL Configuration]	Blog bot	DAX Blogs	0	05.09.2014 21:11
zakharov: Внедряем AX2009. Поиск "тяжелых" запросов используя Microsoft SQL Server Activity Monitor	Blog bot	DAX Blogs	5	22.08.2013 11:18
Помогите с выбором версии SQL Server для Ax2009	Predator	DAX: Администрирование	9	02.02.2010 21:38