21.04.2016, 13:36 | #21 |
Участник
|
эм.... вариант с настройками пришел еще из конкорда, где была база данных собственного формата с доступом по одной записи (типа DBF). sql'я тогда еще не было.
не. не валидно. ) Замечательно. А пример реализации? куда можно подсмотреть? |
|
21.04.2016, 13:41 | #22 |
Участник
|
Цитата:
Сообщение от Vadik
Ну раз про потенциальные проблемы с выверкой модулей и ГК уже писали, то "чисто техническое" решение - сделать CTE которое по "наивному" (хотя что там наивного, вполне рабочая логика) варианту отрезолвит комбинацию счета клиента и профиля разноски (CustTable - CustGroup - CustLedgerAccounts) в счет ГК. Это достаточно компактная выборка и ее уже можно джойнить с CustTrans
В смысле, что вижу, то и пишу. Меня беспокоит высокая цена join'ов. если так писать, то будет ли делаться лишняя выборка? или SQL закэширует выборки и по возможности повторять не будет? |
|
21.04.2016, 13:48 | #23 |
Участник
|
Цитата:
запросы пока выполняются на большой базе. увидим время выполнения. а пока данные от оптимизатора с большой базы: "наивный запрос": 4% "подзапрос в полях": 9% "запрос с row_number over partition": 87% интересно и забавно. "запрос с row_number over partition" в реальности работал быстрее, чем наивный |
|
21.04.2016, 13:56 | #24 |
Участник
|
Цитата:
Или где-то по тригеру? Ведь и клиенты могут меняться, и настройки... |
|
21.04.2016, 13:58 | #25 |
Участник
|
Цитата:
1. Создаем временную таблицу КодКлиента (группа клиента не нужна), Профиль, СчетГК 2. Заполняем всеми пересечениями КодКлиента, Профиль из проводок (желателен такой индекс) 3. Делаем обновление в ней СчетаГК из настроек 3-мя запросами: сначала Все, потом Группа и наконец Таблица 4. Строим запрос по проводкам с привязкой временной таблицы по INNER JOIN 5. Индексы по вкусу |
|
21.04.2016, 14:00 | #26 |
Участник
|
|
|
21.04.2016, 14:02 | #27 |
Moderator
|
mazzy, ну ты там сам написал что твой запрос с partition и over by как-то подозрительно много времени тратит на джойн. Причина проста - из за or между несколькими условиями джойна, SQL обычно не может использовать ни одного индекса. В результате он делает что-то типа декартова произведения таблиц и потом медленно и печально фильтрует.
Вообще - подход "n запросов без OR, вместо одного запроса с n OR" мне неоднократно экономил производительность. Последний пример - у клиента с порядка миллиона батчей, запрос в inventUpdateOnHand отрабатывал порядка 2-3-4 минут (блокируя все в пессиместическом режиме), а после того как я его заменил на n запросов по числу испольуемых масок аналитик - стало срабатывать за 3-4 секунды. |
|
|
За это сообщение автора поблагодарили: mazzy (2), twilight (1). |
21.04.2016, 14:04 | #28 |
Участник
|
Цитата:
Или где-то по тригеру? Ведь и клиенты могут меняться, и настройки... Блин, код жеж нереентерабельный. Запросы шага 4 нельзя строить пока не будет выполнено построение временной/вспомогательной/зарезолвенной таблицы. А перестроение придется делать при каждом создании/удалении клиента, плана счетов или настройки. Тут пора вспомнить акс2012 с ее безумными фин.аналитиками. Там перестроение придется делать для каждой комбинации фин.аналитик (напомню, что в акс2012 в фин.аналитики входит и счет). Да, я говорил вначале про упрощение задачи. Но не такой же ценой. Подход отдельной вспомогательной таблицы по-моему неприменим для акс2012 и выше )))) Последний раз редактировалось mazzy; 21.04.2016 в 14:09. |
|
21.04.2016, 14:06 | #29 |
Moderator
|
Цитата:
Ах да - и перестраивать при каждом запросе, разумеется. А чтобы не тормозило - построй по custTrans индекс по custAccount+postingProfile. Последний раз редактировалось fed; 21.04.2016 в 14:09. |
|
21.04.2016, 14:22 | #30 |
Участник
|
Последний раз редактировалось mazzy; 21.04.2016 в 14:25. |
|
21.04.2016, 14:49 | #31 |
Участник
|
мдя... на большой базе пришлось подбирать фильтры. в принципе там получалось минут 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') А есть у кого-нибудь объяснение таким результатам? И как можно сделать оптимальнее? Последний раз редактировалось mazzy; 21.04.2016 в 16:19. |
|
21.04.2016, 15:05 | #32 |
Участник
|
Для начала - да, при каждом выполнении. Философского булыжника нет. Нужно пробовать.
X++: SELECT DATAAREAID, COUNT(*) FROM ( SELECT DATAAREAID, ACCOUNTNUM, POSTINGPROFILE FROM CUSTTRANS GROUP BY DATAAREAID, ACCOUNTNUM, POSTINGPROFILE ) T GROUP BY DATAAREAID |
|
21.04.2016, 15:21 | #33 |
Участник
|
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) Код: on (accGroup.DATAAREAID = 'eras' Код: on (accTable.DATAAREAID = 'eras' |
|
|
За это сообщение автора поблагодарили: mazzy (2). |
21.04.2016, 16:13 | #34 |
Модератор
|
--
Цитата:
наивный: 4% по плану запроса, выполнено за 1:17, выбрано 342294 записи
__________________
-ТСЯ или -ТЬСЯ ? |
|
21.04.2016, 16:20 | #35 |
Участник
|
|
|
21.04.2016, 16:23 | #36 |
Участник
|
Цитата:
я сформулировал свои вопросы в первом сообщении этой темы Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски) собственно главный вопрос - Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (ответ похоже получили. Но так писать не шибко удобно) побочный квест - как, на ваш взгляд должны быть устроены подобные настроечные таблицы, чтобы и без Аксаптовского кэширования можно было бы удобно работать с такими настроечными таблицами на уровне SQL? |
|
21.04.2016, 17:10 | #37 |
Модератор
|
Цитата:
Цитата:
Злодей
__________________
-ТСЯ или -ТЬСЯ ? Последний раз редактировалось Vadik; 21.04.2016 в 17:17. |
|
21.04.2016, 19:05 | #38 |
Участник
|
http://coub.com/view/2xtx9
Цитата:
Цитата:
Сообщение от mazzy
...
И для простоты я предлагаю обсуждать акс2009 и ниже. Поскольку сам принцип выборки данных из настроечных таблиц в акс2012 и выше не изменился. Но общие планы счетов (chart of accounts), безумные финансовые аналитики, включающие счет ГК, только захламят обсуждение, ничего не изменяя в сути вопроса. =================================== и все таки... может у кого есть что сказать? напомню, что сама идеология подобных таблиц появилась еще в Конкорде и в Навижине. Еще на базах собственного формата с построчным обращением к базе. SQL тогда был в младенчестве. |
|
22.04.2016, 09:21 | #39 |
Злыдни
|
Может стоит для оптимизации пойти по следующему пути:
1. Select into во временную таблицу по custtable, custledger, custledgeraccount с использованием union. Записать код клиента, тип счета, профиль, найденный счет разноски и dataareaid. На этом этапе отсекаем записи с отсутствующими типами настройки; 2. Для custtrans ищем первую запись во временной таблице по совпадению кода клиента и профиля разноски, отсортировав их по коду клиента и типу счета. На мой взгляд, для SQL вставка во временную таблицу максимум [кол-во клиентов * кол-во профилей * 3] записей и единственный join отработает быстрее. чем несколько left join с последующим отбором. Но это надо тестить, а "боевой" базы под рукой нет. PS: custledger в первом запросе не нужен, т.к. код профиля уже есть в custledgeraccount
__________________
люди...считают, что если техника не ломается, то ее не нужно ремонтировать. Инженеры считают, что если она не ломается, то нуждается в совершенствовании. Последний раз редактировалось KiselevSA; 22.04.2016 в 09:26. |
|
22.04.2016, 09:46 | #40 |
Участник
|
отсекать нельзя - не соответствует стандартному поведению.
стандартное поведение на X++ - настройка может отсутствовать. Цитата:
сначала вставка во временную(!) а потом join - это режим "cross apply join". см кучу статей по поводу разницы между "cross join" и "join" из того, что я знаю - просто join будет работать быстрее. и чтобы выйти на такой режим не надо городить временные таблицы. достаточно ключевого слова в select ))))) |
|
|
Опции темы | Поиск в этой теме |
Опции просмотра | |
|