AX 2012 R2 CU7 Исправление медленно закрывающейся формы сопосталений проводок по клиенту/поставщику, открытой из журнала ГК

[Morpheus]

При помощи Tracing утилиты обнаружил в методе CustVendPaymNote.buildPaymNote() очень медленно выполняющийся запрос:

while select crossCompany RecId from specTrans
        order by specTrans.RecId
        where specTrans.SpecCompany == _specCompany
           && specTrans.SpecTableId == _specTableId
           && specTrans.SpecRecId   == _specRecId
        join RecId from custVendTransOpen
            where custVendTransOpen.DataAreaId == specTrans.RefCompany
               && custVendTransOpen.TableId == specTrans.RefTableId
               && custVendTransOpen.RecId   == specTrans.RefRecId
        join TransDate, DocumentNum, Invoice, PaymId, Voucher from custVendTrans
            where custVendTrans.RecId == custVendTransOpen.RefRecId

Выполнил его рефакторинг:

while select crossCompany TransDate, DocumentNum, Invoice, PaymId, Voucher
            from custVendTrans
            join RecId
            from custVendTransOpen
           where custVendTransOpen.RefRecId == custVendTrans.RecId
            join RecId
            from specTrans
           where specTrans.RefCompany       == custVendTransOpen.DataAreaId
              && specTrans.RefTableId       == custVendTransOpen.TableId
              && specTrans.RefRecId         == custVendTransOpen.RecId
              && specTrans.SpecCompany      == _specCompany
              && specTrans.SpecTableId      == _specTableId
              && specTrans.SpecRecId        == _specRecId

И создал на таблице SpecTrans индекс:

#SpecTransIdx
      PROPERTIES
        Name                #SpecTransIdx
      ENDPROPERTIES
      
      INDEXFIELDS
        #SpecRecId
        #SpecTableId
        #SpecCompany
        #Partition
        #RecId
        #INCLUDEDCOLUMNPRESENT
        #RefRecId
        #INCLUDEDCOLUMNPRESENT
        #RefTableId
        #INCLUDEDCOLUMNPRESENT
        #RefCompany
        #INCLUDEDCOLUMNPRESENT
      ENDINDEXFIELDS

До рефакторинга запрос выполнялся 12 сек, после - меньше 1 сек. Пролем в работе бизнес-логики пока не обнаружил.

[GannexMan]

Хотел бы уточнить некот. аспекты.
1.За счет чего получился более быстрый запрос? (Происходил скан таблиц или оптимизатор SQL сбоил?)
2.Размер индекса может быть равным самим данным и больше(с включением полей прямо в индекс), что для вставки и обновлений тоже не очень.
3.Кол-во записей во всех талицах?
4.План запроса увидеть бы до и после.(Сколько записей ожидается, сколько обработано и т.п.)

[Morpheus]

Это текст и план стандартного SQL зароса (новый индекс не отключал, т.к. на план выполнения он не повлиял):

SELECT T1.RECID,
	T2.RECID,
	T2.DATAAREAID,
	T3.TRANSDATE,
	T3.DOCUMENTNUM,
	T3.INVOICE,
	T3.PAYMID,
	T3.VOUCHER,
	T3.DATAAREAID,
	T3.RECID 
FROM SPECTRANS T1 CROSS 
JOIN VENDTRANSOPEN T2 CROSS 
JOIN VENDTRANS T3 
WHERE ((T1.PARTITION=5637144576) 
	AND (((T1.SPECCOMPANY='DA1') 
	AND (T1.SPECTABLEID=212)) 
	AND (T1.SPECRECID=17852886295))) 
	AND ((T2.PARTITION=5637144576) 
	AND (((T2.DATAAREAID=T1.REFCOMPANY) 
	AND (T1.REFTABLEID=866)) 
	AND (T2.RECID=T1.REFRECID))) 
	AND ((T3.PARTITION=5637144576) 
	AND (T3.RECID=T2.REFRECID 
	AND (T3.DATAAREAID = T2.DATAAREAID) 
	AND (T3.PARTITION = T2.PARTITION))) 
	ORDER BY T1.RECID

А это текст и план отрефакторенного SQL зароса:

SELECT T1.TRANSDATE,
       T1.DOCUMENTNUM,
       T1.INVOICE,
       T1.PAYMID,
       T1.VOUCHER,
       T1.RECID,
       T2.RECID,
       T3.RECID
  FROM VENDTRANS T1 CROSS
  JOIN VENDTRANSOPEN T2 CROSS
  JOIN SPECTRANS T3
 WHERE ((T1.PARTITION=5637144576)
   AND (T1.DATAAREAID='DA1'))
   AND (((T2.PARTITION=5637144576)
   AND (T2.DATAAREAID='DA1'))
   AND (T2.REFRECID=T1.RECID))
   AND ((T3.PARTITION=5637144576)
   AND ((((((T3.REFCOMPANY=T2.DATAAREAID)
   AND (T3.REFTABLEID=866))
   AND (T3.REFRECID=T2.RECID))
   AND (T3.SPECCOMPANY='DA1'))
   AND (T3.SPECTABLEID=212))
   AND (T3.SPECRECID=17852886295)))

[GannexMan]

Думаю, что план запроса лучше не стал.
Если в первом варианте, было два кластерных индекса,
то во втором только один.
Получили Key Lookup и жесткий удар по производительности.

Во втором случае одна и та же таблица VendTrans выдала разное кол-во записей.
В последнем запросе явно указано для всех таблиц данные брать только из DA1, возможно это и объясняет поведение, оптимизатор ограничил кол-во записей по выбранной компании, а не по всем

AND (T3.RECID=T2.REFRECID
AND (T3.DATAAREAID = T2.DATAAREAID)
AND (T3.PARTITION = T2.PARTITION)))

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

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

[Vadik]

Цитата:

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

Выполнил его рефакторинг

А отключение (если трасса была собрана верно) crosscompany было запланировано или это побочный эффект ? Просто помимо него и отключения дурацкой и ненужной сортировки по SpecTrans.RecId - никаких изменений в коде нет

[Morpheus]

Цитата:

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

А отключение (если трасса была собрана верно) crosscompany было запланировано или это побочный эффект ?

Где Вы видите, что crosscompany отключен?

Цитата:

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

Просто помимо него и отключения дурацкой и ненужной сортировки по SpecTrans.RecId - никаких изменений в коде нет

Порядок, в котором следуют таблицы, изменен. В итоге sql запрос изменился.

[Morpheus]

Цитата:

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

Получили Key Lookup и жесткий удар по производительности.

Key Lookup можно устранить, добавив IncludedColumn(s) поля в индекс.

Коллеги, у вас есть возможность проверить на ваших инсталляциях, быстро ли закрывается форма сопоставлений? Для теста необходимо создать в журнал ГК транзакцию платеж для клиента или поставщика и сопоставить ее с инвойсом при помощи меню Функции/Сопоставление на форме строк журнала ГК. После сопоставления закрыть форму и оценить, как быстро она закроется.

[Vadik]

Цитата:

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

Где Вы видите, что crosscompany отключен?

Ну давайте вместе посчитаем в трассах количество критериев WHERE по DATAAREAID на VENDTRANS и VENDTRANSOPEN. У меня получилось 0 до рефакторинга и 2 - после

[Vadik]

Цитата:

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

Коллеги, у вас есть возможность проверить на ваших инсталляциях, быстро ли закрывается форма сопоставлений?

У меня на CU7 - быстро (DATAAREAIDLITERAL и PARTITIONLITERAL - включены)

[Morpheus]

Цитата:

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

У меня на CU7 - быстро (DATAAREAIDLITERAL и PARTITIONLITERAL - включены)

А какая у Вас версия БД?

[Morpheus]

Нашел еще один похожий "медленный" запрос в методе LedgerJournalTransUpdateVend.checkSettleVoucher(), который вызывается при проверке/разноске журнала ГК содержащем сопоставленные проводки по клиенту или поставщику:

while select crossCompany RecId from specTrans
            where specTrans.SpecCompany == _ledgerJournalTrans.company() &&
                specTrans.SpecTableId == _ledgerJournalTrans.TableId &&
                specTrans.SpecRecId == _ledgerJournalTrans.RecId
            join RecId from vendTransOpen
                where specTrans.RefCompany == vendTransOpen.DataAreaId &&
                    specTrans.RefTableId == vendTransOpen.TableId &&
                    specTrans.RefRecId == vendTransOpen.RecId
            join  RecId, AccountNum, Voucher, Approved, Closed from vendTrans
                where vendTransOpen.RefRecId == vendTrans.RecId
            join Party from vendTransVendor
                where vendTrans.AccountNum == vendTransVendor.AccountNum

Куда копать?

[Vadik]

Цитата:

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

А какая у Вас версия БД?

На инстансе с которым я сейчас плотно работаю - SQL Server 2008 R2 EE

[ZVV]

Попробуйте для интереса пересобрать свежую SQL статистику для участвующих таблиц.

[Morpheus]

Цитата:

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

У меня на CU7 - быстро (DATAAREAIDLITERAL и PARTITIONLITERAL - включены)

Включение DATAAREAIDLITERAL и PARTITIONLITERAL не помогло.

[Morpheus]

Цитата:

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

На инстансе с которым я сейчас плотно работаю - SQL Server 2008 R2 EE

У меня работает SQL Server 2008 R2 SP1 SE.

[Vadik]

Цитата:

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

У меня работает SQL Server 2008 R2 SP1 SE.

Зохен вей. SP3 же вышел год назад, а поверх него - пара секьюрных обновлений
Можете дать статистику по количеству записей (общему и в разбивке по компаниям) в VendTrans, VendTransOpen, SpecTrans ? Будет время, поэкспериментирую

У меня:
VendTrans - 1946893
VendTransOpen - 180907
SpecTrans - 11627

P.S. Стандартные индексы на всех трех таблицах - на местах, без изменений ?

[Morpheus]

Цитата:

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

Попробуйте для интереса пересобрать свежую SQL статистику для участвующих таблиц.

Выполнил оператор "UPDATE STATISTICS ИмяТаблицы" для всех учавствующих таблиц. Произошло чудо. "Проблемные" запросы теперь работают быстро.

[ZVV]

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

[GannexMan]

@ZVV
Оба запроса - кросс компани.
В первом через промежуточное условие и во втром,
аксапта "вот так решила" сгенерировать запрос.
В коде указано для каких компаний надо делать выборку, вот аксапта и указала явно.
Видно не смогла "связать" условием как в первом варианте и стала перечислять компании.

@Morpheus Куда копать?
Сначала в план запроса. Смотрим, где макс. поток данных и пытаемся его ограничить.

[ZVV]

Цитата:

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

Оба запроса - кросс компани.
В первом через промежуточное условие и во втром,

Мне видится что вы не правы.
В первом запросе нет никакого ограничения на SPECTRANS.REFCOMPANY, ни явно, ни через условия. Т.е. оно может быть любым и ссылаться на записи VendTrans(Open) в любой компании.
Во втором же запросе неожиданно появляются эти явные ограничения T1/T2.DATAAREAID='DA1'