axforum blogs: Поиск источника SQL-запросов в коде X++ методом пересечения множеств перекрестных ссылок

[Blog bot]

Источник: //axforum.info/forums/blog.php?b=8329
==============

Допустим, со стороны СУБД вы (или ваши DBA) поймали какой-то SQL-запрос из X++, который вам нужно оптимизировать - именно в коде X++, а не просто пришпилить plan guide. Как найти источник запроса в коде приложения? Можно включить трассировку "длинных" SQL-запросов для всех подряд пользователей и потом периодически шерстить логи. Но, во-первых, не факт, что ваш запрос выполняется долго и попадет в лог (а логировать быстрые часто выполняемые запросы может выйти себе дороже). Во-вторых, нет гарантии, что запрос будет повторно выполняться вскоре после включения трассировки. И в-третьих, само по себе включение трассировки SQL-запросов для всех пользователей в рабочей системе может потребовать выполнения определеннных бюрократических процедур.

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



Возьмем для примера такой SQL-запрос (в чуть причесанном виде):
PHP код:

SELECT
T1.REFERENCEDISTRIBUTION,
T4.EXCHANGERATE1,
T4.EXCHANGERATE2,
T4.REPORTINGEXCHANGERATE1,
T4.REPORTINGEXCHANGERATE2
FROM
tempdb."DBO".t100007_18B95DDA3C21488C85AC09C9FEB59FE5 T1
CROSS JOIN ACCOUNTINGDISTRIBUTION T2
CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRYDISTRIBUTION T3
CROSS JOIN SUBLEDGERJOURNALACCOUNTENTRY T4
WHERE ((T1.PARTITION = @P1) AND (T1.REFERENCEDISTRIBUTION @P2))
AND ((T2.PARTITION = @P3) AND (T2.RECID = T1.REFERENCEDISTRIBUTION))
AND ((T3.PARTITION = @P4) AND (T3.ACCOUNTINGDISTRIBUTION = T1.REFERENCEDISTRIBUTION))
AND ((T4.PARTITION = @P5) AND (T4.RECID = T3.SUBLEDGERJOURNALACCOUNTENTRY))
GROUP BY
T1.REFERENCEDISTRIBUTION,
T4.EXCHANGERATE1,
T4.EXCHANGERATE2,
T4.REPORTINGEXCHANGERATE1,
T4.REPORTINGEXCHANGERATE2
ORDER BY
T1.REFERENCEDISTRIBUTION,
T4.EXCHANGERATE1,
T4.EXCHANGERATE2,
T4.REPORTINGEXCHANGERATE1,
T4.REPORTINGEXCHANGERATE2

Здесь t100007_* - это экземпляр временной таблицы AccountingDistributionTmpJournalize. В запросе упоминаются поля в условиях WHERE (AccountingDistributionTmpJournalize.ReferenceDistribution, SubledgerJournalAccountEntryDistribution.SubledgerJournalAccountEntry, SubledgerJournalAccountEntryDistribution.AccountingDistribution), а также поля в списке выбора SELECT и группировки GROUP BY (SubledgerJournalAccountEntry.ExchangeRate1, SubledgerJournalAccountEntry.ReportingExchRate1, etc). Обратим внимание, что в запросе нет агрегирования, следовательно, все перекрестные ссылки на поля будут с типом Read (для DAX2012 и более ранних версий, которые это различают). Если бы в запросе использовалось агрегирование значений полей (что-то вроде sum(PurchQty) или maxOf(RecId)), то соотв. ссылки были бы типа Write - это в общем случае позволило бы дополнительно сузить выборку.

Далее описан способ локализации источника запроса по перекрестным ссылкам с помощью Excel. Весьма вероятно, что кто-то уже давно так делает. Также наверняка это всё можно худо-бедно автоматизировать в среде разработки и исключить Excel из инструментария.

Найдем перекрестные ссылки на перечисленные поля таблиц и выгрузим их по отдельности на листы одной книги Excel. Для простоты будем рассматривать только ссылки из методов, игнорируя ссылки из Query, View, Form и прочих подобных объектов - с Query и View разговор отдельный... Для этого ссыки можно отфильтровать по наличию номера строки (1..) и дополнительно при желании - по типу доступа (Read/Write). Тип доступа может быть особенно интересен, если запрос использует агрегирование. После выгрузки можно удалить столбцы "Строка" и "Столбец", оставив только "Путь" и "Ссылка" (Read/Write), а затем с помощью Excel удалить дубликаты (Данные/Работа с данными/Удалить дубликаты, если кто еще не пользуется). На выходе мы получим для каждого интересующего нас поля в SQL-запросе отдельный лист Excel с таблицей уникальных путей к методам, где это поле используется. В зависимостти от "везения" ссылок на каждое поле может быть от полудюжины до нескольких сотен. Если выгружать перекрестные ссылки штатно через Ctrl-T, то данные будут отформатированы как таблицы, что весьма удобно. Если же это не так, то стоит заняться форматированием самостоятельно (в Excel Главная/Стили/Форматировать как таблицу) - так вы получите именованные таблицы в книге и именованные колонки в таблицах вместо безымянных ссылок на ячейки.

На одном из листов добавим столбцы для каждого поля из соседних листов. В колонке нужно использовать формулу, которая позволит понять, есть ли пути из ссылок на текущем листе среди путей, ссылающихся на соотв. дополнительное поле. Я обычно использую формулу наподобие такой:
Код:
=ЕСЛИОШИБКА(ПОИСКПОЗ([@Путь];Table2[[#Все];[Путь]];0)>0;"")
Для тех, кто по каким-то причинам нечасто работает в Excel с данными, отформатированными как таблицы, поясню. Здесь [@Путь] - это ссылка на ячейку в той же строке текущей таблицы и в колонке "Путь"; Table2[[#Все];[Путь]] - ссылка на колонку (одномерный массив ячеек) на соседнем листе, где данные отформатированы в виде именованной таблицы Table2, причем из таблицы тоже берется колонка "Путь". Т.е. мы ищем путь из перекрестных ссылок в текущей строке таблицы среди ссылок в другой таблице, относящихся к другому полю. Если такой путь найдется, то его индекс будет больше нуля, и мы увидим значение "ИСТИНА", если же не найдется, то функция ЕСЛИОШИБКА() подставит пустую строку.

Вот как может выглядеть результат такого пересечения множеств перекрестных ссылок:


Отфильтровав все колонки с формулами по значению "ИСТИНА", мы останемся с тремя ссылками:

• \Classes\SubledgerJournalizer\loadaccountingDistributionTmp
• \Classes\SubledgerJournalizer\PSALoadaccountingDistReleaseTmp
• \Classes\SubledgerJournalizer\loadReferenceDistributionInformation

После этого методом пристального взгляда нетрудно установить, что в двух методах поля в действительности лишь "читаются", а вот SQL-запрос выполняет из \Classes\SubledgerJournalizer\loadReferenceDistributionInformation.


Описанный способ, разумеется, - не панацея, и встречаются разного рода нюансы. Скажем, в коде X++ работа может вестись с Map-ами, а не непосредственно с таблицами, тогда перекрестные ссылки нужно будет искать на поля Map-ов. Также SQL-запрос может формироваться на основе заранее созданного Query и лишь дополнительно фильтроваться в коде, впрочем, из моего скромного опыта, это встречается существенно реже.


Источник: //axforum.info/forums/blog.php?b=8329