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

[Alexius]

Цитата:

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

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

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

[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 представления настроек кажутся чрезмерными, то, вероятно, и "трудозатраты" СУБД при выполнении подзапросов не должны особо смущать.

[mazzy]

Цитата:

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

В стандартной Аксапте выбрали 1-й вариант

эм.... вариант с настройками пришел еще из конкорда, где была база данных собственного формата с доступом по одной записи (типа DBF). sql'я тогда еще не было.

не. не валидно. )

Цитата:

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

имеет смысл реализовать 2-й вариант хранения настроек и сделать их SQL-friendly представление, которое уже использовать при чтении проводок.

Замечательно. А пример реализации? куда можно подсмотреть?

[Vadik]

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

[mazzy]

Цитата:

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

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

"Наивное" - сугубо в хорошем смысле слова. Типа "наивное искуство".
В смысле, что вижу, то и пишу.

Меня беспокоит высокая цена join'ов.
если так писать, то будет ли делаться лишняя выборка? или SQL закэширует выборки и по возможности повторять не будет?

[mazzy]

Цитата:

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

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

А резолвить нужно при каждом выполнении запроса?
Или где-то по тригеру? Ведь и клиенты могут меняться, и настройки...

[fed]

mazzy, ну ты там сам написал что твой запрос с partition и over by как-то подозрительно много времени тратит на джойн. Причина проста - из за or между несколькими условиями джойна, SQL обычно не может использовать ни одного индекса. В результате он делает что-то типа декартова произведения таблиц и потом медленно и печально фильтрует.
Вообще - подход "n запросов без OR, вместо одного запроса с n OR" мне неоднократно экономил производительность. Последний пример - у клиента с порядка миллиона батчей, запрос в inventUpdateOnHand отрабатывал порядка 2-3-4 минут (блокируя все в пессиместическом режиме), а после того как я его заменил на n запросов по числу испольуемых масок аналитик - стало срабатывать за 3-4 секунды.

[mazzy]

Цитата:

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

1. Создаем временную таблицу КодКлиента (группа клиента не нужна), Профиль, СчетГК
...
4. Строим запрос по проводкам с привязкой временной таблицы по INNER JOIN

А заполнять нужно при каждом выполнении запроса?
Или где-то по тригеру? Ведь и клиенты могут меняться, и настройки...

Блин, код жеж нереентерабельный.
Запросы шага 4 нельзя строить пока не будет выполнено построение временной/вспомогательной/зарезолвенной таблицы.

А перестроение придется делать при каждом создании/удалении клиента, плана счетов или настройки.

Тут пора вспомнить акс2012 с ее безумными фин.аналитиками. Там перестроение придется делать для каждой комбинации фин.аналитик (напомню, что в акс2012 в фин.аналитики входит и счет). Да, я говорил вначале про упрощение задачи. Но не такой же ценой. Подход отдельной вспомогательной таблицы по-моему неприменим для акс2012 и выше
))))

[fed]

Цитата:

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

А заполнять нужно при каждом выполнении запроса?
Или где-то по тригеру? Ведь и клиенты могут меняться, и настройки...

Блин, код жеж нереентерабельный.
Запросы шага 4 нельзя строить пока не будет выполнено построение временной/вспомогательной/зарезолвенной таблицы.


))))

Ну дык добавь колонку с GUID, и засунь ее во все запросы, обновления и индексы. Или ParmId там, если из аксапты обновлять собираешься.
Ах да - и перестраивать при каждом запросе, разумеется. А чтобы не тормозило - построй по custTrans индекс по custAccount+postingProfile.

[mazzy]

Цитата:

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

Ну дык добавь колонку с GUID, и засунь ее во все запросы, обновления и индексы.

оО!
ну нахер http://www.youtube.com/watch?v=HcfHBgUTn7I

[Alexius]

Цитата:

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

А заполнять нужно при каждом выполнении запроса?

Для начала - да, при каждом выполнении. Философского булыжника нет. Нужно пробовать.

SELECT DATAAREAID, COUNT(*)
FROM
(
  SELECT DATAAREAID, ACCOUNTNUM, POSTINGPROFILE
  FROM CUSTTRANS
  GROUP BY DATAAREAID, ACCOUNTNUM, POSTINGPROFILE
) T
GROUP BY DATAAREAID

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

[Vadik]

--

Цитата:

наивный: 4% по плану запроса, выполнено за 1:17, выбрано 342294 записи

Ну, для отчета (если это отчет) - неплохо, примерно столько же времени потребуется чтобы отчет на ее основе тупо пролистать не читая. Ты хочешь что-то дальше оптимизировать? Зачем? У тебя есть отчеты на 15 миллионов строк ? Ты хочешь чтобы они выполнялись за минуту а не за 20 ? Чего-то ты недоговариваешь

[mazzy]

Цитата:

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

--
Ты хочешь что-то дальше оптимизировать? Зачем? У тебя есть отчеты на 15 миллионов строк ? Ты хочешь чтобы они выполнялись за минуту а не за 20 ? Чего-то ты недоговариваешь

Злодей ))) http://coub.com/view/9z36u

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

собственно главный вопрос - Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (ответ похоже получили. Но так писать не шибко удобно)

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

[Vadik]

Цитата:

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

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

Удобство - штука относительная. Вот сейчас реализуй то же самое для AX2012, прогони тест со временем выполнения запросов и ты поймешь насколько кучеряво тебе живется на 2009

Цитата:

Злодей

Я знаю

[mazzy]

мдя... на большой базе пришлось подбирать фильтры. в принципе там получалось минут 20 на всю выборку в 15млн результирующих записей.

вот какие запросы были:

-- наивный: 4% по плану запроса, выполнено за 1:24, выбрано 342294 записи
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.accountnum = tr.accountnum)
left join CUSTLEDGERACCOUNTS as accTable
    on (accTable.DATAAREAID = 'eras'
    and accTable.POSTINGPROFILE = tr.POSTINGPROFILE
    and accTable.AccountCode = 0 and accTable.NUM = tab.ACCOUNTNUM)
left join CUSTLEDGERACCOUNTS as accGroup 
    on (accGroup.DATAAREAID = 'eras'
    and accGroup.POSTINGPROFILE = tr.POSTINGPROFILE
    and accGroup.AccountCode = 1 and accGroup.NUM = tab.CUSTGROUP)
left join CUSTLEDGERACCOUNTS as accAll
    on (accAll.DATAAREAID = 'eras'
    and accAll.POSTINGPROFILE = tr.POSTINGPROFILE
    and accAll.AccountCode = 2)
where tr.DATAAREAID = '3r'
  and tab.dataAreaId = 'edat'
)
select *
from trans
where trans.pSumAccount in ('62.02.01', '62.02.02', '62.02.02', '62.01.09')

-- row_number, 87% по плану запроса, выполнился 4:39, выбрано 342294 записи
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.accountnum = tr.accountnum)
left join CUSTLEDGERACCOUNTS as la
    on ( la.dataareaid = 'eras'
     and la.POSTINGPROFILE = tr.POSTINGPROFILE
     and la.num = (case la.AccountCode 
							when 0 then tab.AccountNum
							when 1 then tab.CustGroup
							else la.num
							end)
    )
where tr.DATAAREAID = '3r'
  and tab.dataAreaId = 'edat'
)
select *
from trans
where trans.acc_rn = 1
  and trans.pSumAccount in ('62.02.01', '62.02.02', '62.02.02', '62.01.09')

-- подзапрос: 8% по плану запроса, выполнился за 1:59, выбрано 342294 записи
with trans as (
select 
	(select top 1 SumAccount 
		from CustLedgerAccounts as la
		where la.PostingProfile = tr.postingProfile
			and la.num = (case la.AccountCode 
							when 0 then tab.AccountNum
							when 1 then tab.CustGroup
							else la.num
							end)
			and la.dataAreaId = 'eras'
		order by AccountCode  ---- <----
		) as pSumAccount

	,tr.*
from custtrans as tr
inner join custtable as tab on (tab.AccountNum = tr.AccountNum)
where tr.DATAAREAID = '3r'
  and tab.dataAreaId = 'edat'
)
select *
from trans
where trans.pSumAccount in ('62.02.01', '62.02.02', '62.02.02', '62.01.09')

прикольно.
А есть у кого-нибудь объяснение таким результатам?
И как можно сделать оптимальнее?

[gl00mie]

Copy-paste detected

Цитата:

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

вот какие запросы были:

-- наивный: 4% по плану запроса, выполнено за 1:17, выбрано 342294 записи
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.accountnum = tr.accountnum)
left join CUSTLEDGERACCOUNTS as accTable
    on (accTable.DATAAREAID = 'eras'
    and accTable.POSTINGPROFILE = tr.POSTINGPROFILE
    and accTable.AccountCode = 0 and accTable.NUM = tab.ACCOUNTNUM)
left join CUSTLEDGERACCOUNTS as accGroup 
    on (accTable.DATAAREAID = 'eras'
    and accGroup.POSTINGPROFILE = tr.POSTINGPROFILE
    and accGroup.AccountCode = 1 and accGroup.NUM = tab.CUSTGROUP)

Во втором left join должно быть

on (accGroup.DATAAREAID = 'eras'

вместо

on (accTable.DATAAREAID = 'eras'

[mazzy]

Цитата:

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

Copy-paste detected Во втором left join должно быть

on (accGroup.DATAAREAID = 'eras'

вместо

on (accTable.DATAAREAID = 'eras'

опа! спасибо.

перезапустил запрос. принципиально не изменилось. было 1:17, стало 1:24.
исправил в сообщении, чтобы потом не путаться.

[KiselevSA]

Может стоит для оптимизации пойти по следующему пути:
1. Select into во временную таблицу по custtable, custledger, custledgeraccount с использованием union. Записать код клиента, тип счета, профиль, найденный счет разноски и dataareaid. На этом этапе отсекаем записи с отсутствующими типами настройки;
2. Для custtrans ищем первую запись во временной таблице по совпадению кода клиента и профиля разноски, отсортировав их по коду клиента и типу счета.

На мой взгляд, для SQL вставка во временную таблицу максимум [кол-во клиентов * кол-во профилей * 3] записей и единственный join отработает быстрее. чем несколько left join с последующим отбором.

Но это надо тестить, а "боевой" базы под рукой нет.
PS: custledger в первом запросе не нужен, т.к. код профиля уже есть в custledgeraccount

[mazzy]

Цитата:

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

отсекаем записи с отсутствующими типами настройки;

отсекать нельзя - не соответствует стандартному поведению.
стандартное поведение на X++ - настройка может отсутствовать.

Цитата:

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

На мой взгляд, для SQL вставка во временную таблицу максимум [кол-во клиентов * кол-во профилей * 3] записей и единственный join отработает быстрее. чем несколько left join с последующим отбором.

ой, сомневаюсь.
сначала вставка во временную(!) а потом join - это режим "cross apply join".
см кучу статей по поводу разницы между "cross join" и "join"
из того, что я знаю - просто join будет работать быстрее.

и чтобы выйти на такой режим не надо городить временные таблицы. достаточно ключевого слова в select )))))

[KiselevSA]

select ct.ACCOUNTNUM, cla.ACCOUNTCODE, cla.POSTINGPROFILE, dim.DISPLAYVALUE, cla.DATAAREAID from CUSTTABLE as ct
join CUSTLEDGERACCOUNTS as cla on
	ct.ACCOUNTNUM = cla.NUM and
	ct.DATAAREAID = cla.DATAAREAID and
	cla.ACCOUNTCODE = 0
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim on
	cla.SUMMARYLEDGERDIMENSION = dim.RECID
	where 
		ct.DATAAREAID = 'usmf'
union
select ct1.ACCOUNTNUM, cla1.ACCOUNTCODE, cla1.POSTINGPROFILE, dim1.DISPLAYVALUE, cla1.DATAAREAID from CUSTTABLE as ct1
join CUSTLEDGERACCOUNTS as cla1 on
	ct1.CUSTGROUP = cla1.NUM and
	ct1.DATAAREAID = cla1.DATAAREAID and
	cla1.ACCOUNTCODE = 1
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim1 on
	cla1.SUMMARYLEDGERDIMENSION = dim1.RECID
		where 
		ct1.DATAAREAID = 'usmf'
union
select ct2.ACCOUNTNUM, cla2.ACCOUNTCODE, cla2.POSTINGPROFILE, dim2.DISPLAYVALUE, cla2.DATAAREAID from CUSTTABLE as ct2
join CUSTLEDGERACCOUNTS as cla2 on
	ct2.DATAAREAID = cla2.DATAAREAID and
	cla2.ACCOUNTCODE = 2
join DIMENSIONATTRIBUTEVALUECOMBINATION as dim2 on
	cla2.SUMMARYLEDGERDIMENSION = dim2.RECID
	where 
		ct2.DATAAREAID = 'usmf'

у меня такой запрос на demo выполняется мгновенно (план посмотреть не могу, прав не хватает). Думаю, что и select into будет работать так же быстро.