21.04.2016, 11:09 | #1 |
Участник
|
Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски)
Хотелось бы спросить уважаемое сообщество
Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски) Паттерн настройка по Table/Group/All в аксапте используется очень часто - это и профили разноски, и группы расчета комиссии и т.п. Аксапта всегда в первую очередь использует настройку для Table, если таковая есть. Если настройки для Table нет, то Аксапта ищет настройку для Group. Если и таковой нет, то Аксапта ищет настройку для All. Вполне возможно, что настройка может отсутствовать. ======================== Для определенности и я предлагаю обсуждать Профиль разноски по клиентам (хотя повторюсь, подобное в аксапте встречается очень часто) Для той же простоты я предлагаю считать, что виртуальных компаний нет и все данные находятся в одной компании. И для простоты я предлагаю обсуждать акс2009 и ниже. Поскольку сам принцип выборки данных из настроечных таблиц в акс2012 и выше не изменился. Но общие планы счетов (chart of accounts), безумные финансовые аналитики, включающие счет ГК, только захламят обсуждение, ничего не изменяя в сути вопроса. Также вопрос, думаю будет интересен и для Oracle. Однако поскольку вендор в последних версиях поддерживает только MS SQL, думаю, что стоит сосредоточиться на MS SQL. Задача: внешняя система читает проводки по клиентам. Причем внешняя система для каждой проводки ожидает получить в выборке счет ГК из профиля разноски. Как оптимально написать T-SQL запрос для такой выборки? ========================== вот какой результат ожидаем увидеть после выполнения запросов ======================== В аксапте, для настроечных таблиц как правило включено кэширование на уровне таблиц. Поэтому с точки зрения Аксапты отдельные запросы по настроечной таблице внутри цикла не приводят к обращению к SQL и выполняется достаточно оптимально. ======================== Однако MS SQL не знает про аксаптовское кэширование. MS SQL знает только что это таблицы маленького размера (обычно это так) и исходя из этой статистики может подготовить план. Основная проблема - в настроечной таблице для одной проводки может быть несколько разных подходящих настроек для одной исходной мастер-записи. Поэтому вроде вполне подходит паттерн, принятый среди разработчиков на T-SQL для join firstonly: Код: with trans as ( SELECT --top 100 la.SUMACCOUNT as pSumAccount ,row_number() over (partition by tr.DataAreaId, tr.RecId, la.dataareaid, la.POSTINGPROFILE order by la.ACCOUNTCODE) as acc_rn ,tr.* from custtrans as tr join custtable as tab on (tab.DATAAREAID = tr.DATAAREAID and tab.accountnum = tr.accountnum) left join CUSTLEDGERACCOUNTS as la on ( la.dataareaid = tr.DATAAREAID and la.POSTINGPROFILE = tr.POSTINGPROFILE and ( (la.AccountCode = 0 and la.NUM = tab.ACCOUNTNUM) or (la.AccountCode = 1 and la.NUM = tab.CUSTGROUP) or (la.ACCOUNTCODE = 2) ) ) ) select * from trans where trans.acc_rn = 1 -- and trans.DATAAREAID in ('dmr') 1. получаем join со всеми настройками 2. для каждой проводки нумеруем настройки в нужном нам порядке начиная с 1 3. фильтруем, оставляя только первый нумер для небольших таблиц настроек получается неплохо. однако обратите внимание на sort в плане запроса. и на относительную стоимость выборки из этой маленькой таблицы настроек - 47% =========================== наивный способ - использовать функцию isnull Код: with trans as ( SELECT isnull(accTable.SUMACCOUNT, isnull(accGroup.SumAccount, accAll.SumAccount)) as pSumAccount, tr.* from custtrans as tr join custtable as tab on (tab.DATAAREAID = tr.DATAAREAID and tab.accountnum = tr.accountnum) left join CUSTLEDGERACCOUNTS as accTable on (accTable.DATAAREAID = tr.DATAAREAID and accTable.POSTINGPROFILE = tr.POSTINGPROFILE and accTable.AccountCode = 0 and accTable.NUM = tab.ACCOUNTNUM) left join CUSTLEDGERACCOUNTS as accGroup on (accTable.DATAAREAID = tr.DATAAREAID and accGroup.POSTINGPROFILE = tr.POSTINGPROFILE and accGroup.AccountCode = 1 and accGroup.NUM = tab.CUSTGROUP) left join CUSTLEDGERACCOUNTS as accAll on (accAll.DATAAREAID = tr.DATAAREAID and accAll.POSTINGPROFILE = tr.POSTINGPROFILE and accAll.AccountCode = 2) ) select * from trans --where trans.DATAAREAID in ('dmr') ========================== T-SQL: какие способы выборки данных из настроечных таблиц Table/Group/All используете вы? T-SQL: какие плюсы и минусы видите вы в различных способах выборки? как, на ваш взгляд должны быть устроены подобные настроечные таблицы, чтобы и без Аксаптовского кэширования можно было бы удобно работать с такими настроечными таблицами на уровне SQL? Последний раз редактировалось mazzy; 21.04.2016 в 11:34. Причина: добавил скриншот ожидаемого результата |
|
21.04.2016, 11:27 | #2 |
Участник
|
Может быть не нужно джоин, а использовать подзапрос ?
В SQL при выполнении запроса тоже происходит кеширование, так что получится как в аксапте. Только кешировать будет не АОС, а сам движок SQL при выполнении подзапросов с определением счета. |
|
21.04.2016, 11:31 | #3 |
Участник
|
Цитата:
подзапрос в where? и как оттуда вытащить счет ГК? в общем, а можно пример реализации? |
|
21.04.2016, 11:49 | #4 |
Участник
|
Пример не могу сейчас привести. Зашиваюсь.
Кину еще одну идею - вы никогда не рассматривали вариант сделать денормализацию и хранить счет прямо в проводке. Все будет быстрее работать. И оборотки не будут ломаться при изменении настроек по разноске (конечно их тоже надо допиливать). |
|
21.04.2016, 11:50 | #5 |
Участник
|
|
|
21.04.2016, 12:01 | #6 |
Участник
|
Цитата:
Сообщение от Logger
Пример не могу сейчас привести. Зашиваюсь.
Кину еще одну идею - вы никогда не рассматривали вариант сделать денормализацию и хранить счет прямо в проводке. Все будет быстрее работать. И оборотки не будут ломаться при изменении настроек по разноске (конечно их тоже надо допиливать). Но в результате выяснилось, что денормализованное поле не совпадает в некоторых случаях с настройкой, а пользователи ожидают совпадения. Вот мужики удивились то )))) поскольку на этом проекте база в несколько десятков терабайт... сверка и приведение в чувство денормализованного поля - отдельная подзадача )))) И оно там не одно. ======================== В общем, я за то, чтобы все действующие настройки всегда явно присутстовали в проводках. Но это возможно только если сам Майкрософт в стандартном функционале будет следовать этому принципу. Пока вендор в своих отчетах, запросах и алгоритмах делает выборку из настроечных таблиц, приходится повторять логику стандартного функционала. Ведь пользователи так или иначе сверяют с данными, которые получены стандартным функционалом. вопрос как раз: как оптимально повторить стандартный функционал в T-SQL запросе? ======================== Повторюсь, подобных "настроек" в аксапте много. Последний раз редактировалось mazzy; 21.04.2016 в 12:07. |
|
21.04.2016, 12:26 | #7 |
Участник
|
|
|
21.04.2016, 12:26 | #8 |
Moderator
|
В общем - по моим наблюдениям, SQL вообще очень неважно оптимизирует запросы с OR в условиях джойна.
Я бы пошел по такому пути: 1. Создал бы вспомогательную таблицу с полями клиент, группа, профиль и счет ГК. 2. Заполнил бы эту таблицу декартовым произведением профилей и клиентов 3. Потом прогнал бы по этой таблице три update. Первый update джойнит вспомогательную таблицу с custLedger по условию совпадения профиля и клиента. Второй update обновляет только строки с пустым счетом ГК и по условию совпадения профиля и группы клиентов.Третий update действует аналогично но работает уже только по профилю разноски. 4. Каждый из запросов в пункте 3 надо проанализировать и построить подходящие индексы (поскольку каждый из джойнов только по AND, проблем быть не должно). 5. Потом джойнишь custTrans со вспомогательной таблицей. P.S. Пожалуй что декартово произведение из пункта 2, можно заменить просто сбором уникальных комбинаций клиента и профиля из custTrans Последний раз редактировалось fed; 21.04.2016 в 12:31. |
|
|
За это сообщение автора поблагодарили: Alexius (3), Logger (3). |
21.04.2016, 12:36 | #9 |
Участник
|
конечно ожидают. и конечно не получают. но это отдельная тема.
вернемся к T-SQL запросу для выборки настройки Table/Group/Al. Цитата:
проблема: Цитата:
некоторые (или все) настройки могут отсутствовать. Цитата:
возможно, что этот запрос выполняется одновременно несколькими внешними программами с разными критериями выборки. будешь эмулировать временную таблицу? если будешь эмулировать, то нафига она вообще нужна, давай сформулируем как сделать select, который реализует твой алгоритм. |
|
21.04.2016, 12:39 | #10 |
Участник
|
кстати, ты просто переформулировал мой второй пример наивной реализации с isnull )))
Код: with trans as ( SELECT isnull(accTable.SUMACCOUNT, isnull(accGroup.SumAccount, accAll.SumAccount)) as pSumAccount, tr.* from custtrans as tr join custtable as tab on (tab.DATAAREAID = tr.DATAAREAID and tab.accountnum = tr.accountnum) left join CUSTLEDGERACCOUNTS as accTable on (accTable.DATAAREAID = tr.DATAAREAID and accTable.POSTINGPROFILE = tr.POSTINGPROFILE and accTable.AccountCode = 0 and accTable.NUM = tab.ACCOUNTNUM) left join CUSTLEDGERACCOUNTS as accGroup on (accTable.DATAAREAID = tr.DATAAREAID and accGroup.POSTINGPROFILE = tr.POSTINGPROFILE and accGroup.AccountCode = 1 and accGroup.NUM = tab.CUSTGROUP) left join CUSTLEDGERACCOUNTS as accAll on (accAll.DATAAREAID = tr.DATAAREAID and accAll.POSTINGPROFILE = tr.POSTINGPROFILE and accAll.AccountCode = 2) ) select * from trans --where trans.DATAAREAID in ('dmr') |
|
21.04.2016, 12:47 | #11 |
Участник
|
Вообще-то, это банальный вопрос приоритета. По принципу, "кто первый встал, того и тапки". Соответственно, решается с помощью Order by в подзапросе
X++: select (select top 1 SumAccount from CustLedgerAccounts where CustLedgerAccounts.PostingProfile = custTrans.postingProfile and CustLedgerAccounts.num = (case CustLedgerAccounts.AccountCode when 0 then custtable.AccountNum when 1 then custTable.CustGroup else CustLedgerAccounts.num end) and CustLedgerAccounts.dataAreaId = 'dat' order by AccountCode ---- <---- ) as pSumAccount ,custTrans.* from custtrans inner join custtable on custTable.AccountNum = custTrans.AccountNum where custTrans.DataAreaId = 'dat' and custTable.dataAreaId = 'dat'
__________________
- Может, я как-то неправильно живу?! - Отчего же? Правильно. Только зря... Последний раз редактировалось Владимир Максимов; 21.04.2016 в 12:58. Причина: Да, забыл еще связь по клиенту/группе |
|
|
За это сообщение автора поблагодарили: mazzy (5). |
21.04.2016, 13:03 | #12 |
Участник
|
Цитата:
Теперь собственно к слову "оптимально" Подзапрос - это оптимально? Как MS SQL будет оптимизировать данную конструкцию? Где об этом почитать? |
|
21.04.2016, 13:08 | #13 |
Участник
|
Построить план выполнения для всех трех запросов в одном окне и посмотреть какой из них оптимизатор признает наиболее шустрым.
|
|
21.04.2016, 13:09 | #14 |
Участник
|
На всякий случай.
Если поля DataAreaId указывать в качестве ключа связи, то скорость выполнения запроса резко проседает по сравнению с указанием конкретного значения DataAreaId. Не на порядки, конечно, но в несколько раз. Поэтому, с точки зрения производительности, выгоднее сделать несколько последовательных запросов меняя значение DataAreaId (если необходимо, объединить потом по UNION ALL), чем один общий запрос в котором указано объединение таблиц в том числе и по DataAreaId Т.е. запрос вида X++: select * from custTrans inner join custTable on custTable.AccountNum = custTrans.AccountNum and custTrans.DataAreaId = custTable.dataAreaId X++: select * from custTrans inner join custTable on custTable.AccountNum = custTrans.AccountNum where custTrans.DataAreaId = 'dt1' and custTable.dataAreaId = 'dt1' union all select * from custTrans inner join custTable on custTable.AccountNum = custTrans.AccountNum where custTrans.DataAreaId = 'dt2' and custTable.dataAreaId = 'dt2' (...)
__________________
- Может, я как-то неправильно живу?! - Отчего же? Правильно. Только зря... |
|
|
За это сообщение автора поблагодарили: mazzy (5). |
21.04.2016, 13:09 | #15 |
Участник
|
Цитата:
Сообщение от mazzy
Задача:
внешняя система читает проводки по клиентам. Причем внешняя система для каждой проводки ожидает получить в выборке счет ГК из профиля разноски. Как оптимально написать T-SQL запрос для такой выборки? Основная проблема - в настроечной таблице для одной проводки может быть несколько разных подходящих настроек для одной исходной мастер-записи. Цитата:
Но возьмем для примера иерархию категорий из AX 2012. С одной стороны, есть понятная пользователю настроечная таблица, хранящая узлы иерархии (категории), а с другой, для той или иной категории есть потребность быстро определять в запросах связанные подкатегории выше по иерархии, на которые могут ссылаться другие настройки, скажем, те же скидки за комплект и т.п. Тут уже разработчики стандарта пошли по второму пути из приведенных выше и реализовали таблицу RetailCategoryContainmentLookup, содержащую SQL-friendly представление иерархии категорий. Так вот, мне кажется, в следующем сценарии:
|
|
|
За это сообщение автора поблагодарили: mazzy (2). |
21.04.2016, 13:10 | #16 |
Участник
|
Вот не верю я в этом смысле тем цифрам, которые выставляет планировщик Тут только практикой. Тупо выполнить каждый запрос несколько раз и сравнить время выполнения
__________________
- Может, я как-то неправильно живу?! - Отчего же? Правильно. Только зря... |
|
21.04.2016, 13:22 | #17 |
Участник
|
|
|
21.04.2016, 13:24 | #18 |
Модератор
|
Ну раз про потенциальные проблемы с выверкой модулей и ГК уже писали, то "чисто техническое" решение - сделать CTE которое по "наивному" (хотя что там наивного, вполне рабочая логика) варианту отрезолвит комбинацию счета клиента и профиля разноски (CustTable - CustGroup - CustLedgerAccounts) в счет ГК. Это достаточно компактная выборка и ее уже можно джойнить с CustTrans (один раз, вместо трех)
__________________
-ТСЯ или -ТЬСЯ ? Последний раз редактировалось Vadik; 21.04.2016 в 13:40. |
|
|
За это сообщение автора поблагодарили: mazzy (2), gl00mie (1). |
21.04.2016, 13:28 | #19 |
Участник
|
Цитата:
а как "посмотреть какой из них оптимизатор признает наиболее шустрым"? Цитата:
Если используется with, то SQL нормально поднимает константы. |
|
21.04.2016, 13:32 | #20 |
Участник
|
|
|
|
За это сообщение автора поблагодарили: mazzy (5). |
|
Опции темы | Поиск в этой теме |
Опции просмотра | |
|