Как оптимально написать 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?