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

[mazzy]

Цитата:

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

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

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

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

Цитата:

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

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

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

[mazzy]

Цитата:

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

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

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

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

[mazzy]

Цитата:

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

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

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

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

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

[mazzy]

Цитата:

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

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

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

[Alexius]

Цитата:

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

Цитата:

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

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

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

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

[Alexius]

Цитата:

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

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

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

[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

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

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

[Alexius]

Цитата:

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

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

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

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

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

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

[Vadik]

--

Цитата:

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

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

[mazzy]

Цитата:

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

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

on (accGroup.DATAAREAID = 'eras'

вместо

on (accTable.DATAAREAID = 'eras'

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

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

[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]

Цитата:

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

Удобство - штука относительная.

http://coub.com/view/2xtx9

Цитата:

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

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

я в курсе. поэтому и ввел дисклаймер сразу ))))

Цитата:

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

...
И для простоты я предлагаю обсуждать акс2009 и ниже. Поскольку сам принцип выборки данных из настроечных таблиц в акс2012 и выше не изменился. Но общие планы счетов (chart of accounts), безумные финансовые аналитики, включающие счет ГК, только захламят обсуждение, ничего не изменяя в сути вопроса.

===================================
и все таки... может у кого есть что сказать?

Цитата:

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

как, на ваш взгляд должны быть устроены подобные настроечные таблицы Table/Group/All, чтобы и без Аксаптовского кэширования можно было бы удобно работать с такими настроечными таблицами на уровне SQL, Reporting Service, OLAP и прочих внешних систем?

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

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