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