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

Цитата:

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

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

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

[Logger]

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

[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')

[Alexius]

Цитата:

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

Цитата:

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

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

кстати, ты просто переформулировал мой второй пример наивной реализации с isnull )))

Не-е-е, это другой подход и идея очень интересная:
1. Создаем временную таблицу КодКлиента (группа клиента не нужна), Профиль, СчетГК
2. Заполняем всеми пересечениями КодКлиента, Профиль из проводок (желателен такой индекс)
3. Делаем обновление в ней СчетаГК из настроек 3-мя запросами: сначала Все, потом Группа и наконец Таблица
4. Строим запрос по проводкам с привязкой временной таблицы по INNER JOIN
5. Индексы по вкусу

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

Вообще-то, это банальный вопрос приоритета. По принципу, "кто первый встал, того и тапки". Соответственно, решается с помощью 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]

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

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

Цитата:

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

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

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

[Alexius]

Цитата:

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

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

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

[mazzy]

Цитата:

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

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

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

а пока данные от оптимизатора с большой базы:
"наивный запрос": 4%
"подзапрос в полях": 9%
"запрос с row_number over partition": 87%

интересно и забавно.
"запрос с row_number over partition" в реальности работал быстрее, чем наивный

[Alexius]

Цитата:

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

интересно и забавно.
"запрос с row_number over partition" в реальности работал быстрее, чем наивный

То, о чем предупреждал Владимир Максимов

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

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

Если поля 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

[mazzy]

Цитата:

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

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

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

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

Цитата:

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

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

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

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