Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски)

[mazzy]

Хотелось бы спросить уважаемое сообщество
Как оптимально написать 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?

[Logger]

Может быть не нужно джоин, а использовать подзапрос ?
В SQL при выполнении запроса тоже происходит кеширование, так что получится как в аксапте. Только кешировать будет не АОС, а сам движок SQL при выполнении подзапросов с определением счета.

[mazzy]

Цитата:

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

Может быть не нужно джоин, а использовать подзапрос ?
В SQL при выполнении запроса тоже происходит кеширование, так что получится как в аксапте. Только кешировать будет не АОС, а сам движок SQL при выполнении подзапросов с определением счета.

подзапрос где? среди полей select? там допустим только подзапрос, который гарантировано возвращает одно значение (обычно там присутствует sum, count без group by). как указать, что подзапрос по настройкам возвращает одно?

подзапрос в where? и как оттуда вытащить счет ГК?

в общем, а можно пример реализации?

[Logger]

Пример не могу сейчас привести. Зашиваюсь.
Кину еще одну идею - вы никогда не рассматривали вариант сделать денормализацию и хранить счет прямо в проводке. Все будет быстрее работать. И оборотки не будут ломаться при изменении настроек по разноске (конечно их тоже надо допиливать).

[Logger]

Цитата:

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

подзапрос где? среди полей select? там допустим только подзапрос, который гарантировано возвращает одно значение (обычно там присутствует sum, count без group by). как указать, что подзапрос по настройкам возвращает одно?

Не писал сам таких запросов. Сходу не посоветую.

[mazzy]

Цитата:

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

Пример не могу сейчас привести. Зашиваюсь.
Кину еще одну идею - вы никогда не рассматривали вариант сделать денормализацию и хранить счет прямо в проводке. Все будет быстрее работать. И оборотки не будут ломаться при изменении настроек по разноске (конечно их тоже надо допиливать).

Бгггг. На том проекте, где я сейчас "ваяю нетленку" именно так и поступили.
Но в результате выяснилось, что денормализованное поле не совпадает в некоторых случаях с настройкой, а пользователи ожидают совпадения. Вот мужики удивились то ))))

поскольку на этом проекте база в несколько десятков терабайт... сверка и приведение в чувство денормализованного поля - отдельная подзадача )))) И оно там не одно.

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

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

вопрос как раз: как оптимально повторить стандартный функционал в T-SQL запросе?

========================
Повторюсь, подобных "настроек" в аксапте много.

[Alexius]

Цитата:

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

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

А совпадения РК - ГК пользователи не ожидают ?

[fed]

В общем - по моим наблюдениям, SQL вообще очень неважно оптимизирует запросы с OR в условиях джойна.
Я бы пошел по такому пути:
1. Создал бы вспомогательную таблицу с полями клиент, группа, профиль и счет ГК.
2. Заполнил бы эту таблицу декартовым произведением профилей и клиентов
3. Потом прогнал бы по этой таблице три update. Первый update джойнит вспомогательную таблицу с custLedger по условию совпадения профиля и клиента. Второй update обновляет только строки с пустым счетом ГК и по условию совпадения профиля и группы клиентов.Третий update действует аналогично но работает уже только по профилю разноски.
4. Каждый из запросов в пункте 3 надо проанализировать и построить подходящие индексы (поскольку каждый из джойнов только по AND, проблем быть не должно).
5. Потом джойнишь custTrans со вспомогательной таблицей.

P.S. Пожалуй что декартово произведение из пункта 2, можно заменить просто сбором уникальных комбинаций клиента и профиля из custTrans

[mazzy]

Цитата:

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

А совпадения РК - ГК пользователи не ожидают ?

конечно ожидают. и конечно не получают. но это отдельная тема.
вернемся к T-SQL запросу для выборки настройки Table/Group/Al.

Цитата:

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

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

а при чем здесь or?
проблема:

Цитата:

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

Основная проблема - в настроечной таблице для одной проводки может быть несколько разных подходящих настроек для одной исходной мастер-записи.

если вернуться к примеру, то для одного клиента в базе может быть ОДНОВРЕМЕННО несколько настроек: для него самого, для его группы и для всех.
некоторые (или все) настройки могут отсутствовать.

Цитата:

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

1. Создал бы вспомогательную таблицу с полями клиент, групп, профиль и счет ГК.
2. Заполнил бы эту таблицу декартовым произведением профилей и клиентов
3. Потом прогнал бы по этой таблице три update.

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

если будешь эмулировать, то нафига она вообще нужна, давай сформулируем как сделать select, который реализует твой алгоритм.

[mazzy]

Цитата:

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

Я бы пошел по такому пути:

кстати, ты просто переформулировал мой второй пример наивной реализации с 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')

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

Вообще-то, это банальный вопрос приоритета. По принципу, "кто первый встал, того и тапки". Соответственно, решается с помощью Order by в подзапросе

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'

[mazzy]

Цитата:

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

Вообще-то, это банальный вопрос приоритета. По принципу, "кто первый встал, того и тапки". Соответственно, решается с помощью Order by в подзапросе

Угу. Top 1... Ведь чувствовал, что можно и подзапросом. Такой простой вариант упустил.

Теперь собственно к слову "оптимально"
Подзапрос - это оптимально? Как MS SQL будет оптимизировать данную конструкцию? Где об этом почитать?

[Alexius]

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

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

На всякий случай.

Если поля DataAreaId указывать в качестве ключа связи, то скорость выполнения запроса резко проседает по сравнению с указанием конкретного значения DataAreaId. Не на порядки, конечно, но в несколько раз.

Поэтому, с точки зрения производительности, выгоднее сделать несколько последовательных запросов меняя значение DataAreaId (если необходимо, объединить потом по UNION ALL), чем один общий запрос в котором указано объединение таблиц в том числе и по DataAreaId

Т.е. запрос вида

select *
from custTrans
inner join custTable on custTable.AccountNum = custTrans.AccountNum
   and custTrans.DataAreaId = custTable.dataAreaId

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

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'

(...)

Бывают исключения, конечно, но обычно выгоднее все-таки указывать конкретное значение DataAreaId для всех таблиц-источников, чем использовать объединение по DataAreaId

[gl00mie]

Цитата:

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

Задача:
внешняя система читает проводки по клиентам. Причем внешняя система для каждой проводки ожидает получить в выборке счет ГК из профиля разноски. Как оптимально написать T-SQL запрос для такой выборки?
Основная проблема - в настроечной таблице для одной проводки может быть несколько разных подходящих настроек для одной исходной мастер-записи.

Какая-то нетипичная задача, как мне кажется, и потому она требует нетипичного (с т.з. Аксапты) подхода к решению.

Цитата:

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

как, на ваш взгляд должны быть устроены подобные настроечные таблицы, чтобы и без Аксаптовского кэширования можно было бы удобно работать с такими настроечными таблицами на уровне SQL?

Тут, по-моему, может быть несколько вариантов:

1. • понятная пользователю настроечная таблица
   • не требует дополнительных затрат для ее поддержки
   • не очень удобно работать с настройками из TSQL;
2. • понятная пользователю настроечная таблица
   • дополнительные затраты для поддержки SQL-friendly представления данных
   • удобно работать с настройками из TSQL;
3. • непонятная пользователю настроечная таблица с SQL-friendly представленем данных
   • не требует дополнительных затрат для ее поддержки
   • удобно работать с настройками из TSQL;

В стандартной Аксапте выбрали 1-й вариант, потому что совсем нечасто стоит задача обработать большую выборку данных и на лету подобрать для каждой записи выборки некий параметр из настроечной таблицы со связями Table/Group/All. Намного чаще обработка идет по одной записи, и по ее полям нужно быстро получить настроечный параметр. При таком раскладе таблица должна быть как можно более понятна для пользователя, который делает настройки, максимум, что от нее требуется, - это обеспечивать нужную сортировку записей. Всё остальное решается кэшированием таблиц в ядре Аксапты и кэшированием результатов выборки в каком-нить SysGlobalobjectCache, как часто делается в 12-ке.
Но возьмем для примера иерархию категорий из AX 2012. С одной стороны, есть понятная пользователю настроечная таблица, хранящая узлы иерархии (категории), а с другой, для той или иной категории есть потребность быстро определять в запросах связанные подкатегории выше по иерархии, на которые могут ссылаться другие настройки, скажем, те же скидки за комплект и т.п. Тут уже разработчики стандарта пошли по второму пути из приведенных выше и реализовали таблицу RetailCategoryContainmentLookup, содержащую SQL-friendly представление иерархии категорий.
Так вот, мне кажется, в следующем сценарии:

• внешняя система читает проводки по клиентам
• для каждой проводки ожидает получить в выборке счет ГК из профиля разноски
• в настроечной таблице для одной проводки может быть несколько разных подходящих записей
• записи настроечной таблицы должны выбираться с учетом определенного приоритета

имеет смысл реализовать 2-й вариант хранения настроек и сделать их SQL-friendly представление, которое уже использовать при чтении проводок. Такое представление, в зависимости от дополнительных условий и ограничений, может как храниться и поддерживаться на постоянной основе, так и строиться ad-hoc перед началом чтения данных внешней системой. Если же затраты на поддержку такого SQL-friendly представления настроек кажутся чрезмерными, то, вероятно, и "трудозатраты" СУБД при выполнении подзапросов не должны особо смущать.

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

Цитата:

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

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

Вот не верю я в этом смысле тем цифрам, которые выставляет планировщик Тут только практикой. Тупо выполнить каждый запрос несколько раз и сравнить время выполнения

[Alexius]

Цитата:

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

Вот не верю я в этом смысле тем цифрам, которые выставляет планировщик

Как в истину последней инстанции и я не верю, но для пристрелки вполне себе

[Vadik]

Ну раз про потенциальные проблемы с выверкой модулей и ГК уже писали, то "чисто техническое" решение - сделать CTE которое по "наивному" (хотя что там наивного, вполне рабочая логика) варианту отрезолвит комбинацию счета клиента и профиля разноски (CustTable - CustGroup - CustLedgerAccounts) в счет ГК. Это достаточно компактная выборка и ее уже можно джойнить с CustTrans (один раз, вместо трех)

[mazzy]

Цитата:

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

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

сейчас занимаюсь тем что запустил все три на многотеррабайтной базе.

а как "посмотреть какой из них оптимизатор признает наиболее шустрым"?

Цитата:

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

Если поля DataAreaId указывать в качестве ключа связи, то скорость выполнения запроса резко проседает по сравнению с указанием конкретного значения DataAreaId. Не на порядки, конечно, но в несколько раз.

Наверное. Я компании стараюсь в самом низу вставлять. Когда виртуальных компаний нет. С виртуальными компаниями да... проще захардкодить компании по всему запросу.

Если используется with, то SQL нормально поднимает константы.

[Alexius]

Цитата:

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

а как "посмотреть какой из них оптимизатор признает наиболее шустрым"?

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