AX 2012 ускорение синхронизации базы в 3-5 раз

[Logger]

Добрый день.
Коллеги, хотел поделиться опытом ускорения синхронизации базы в 2012-й аксапте. Как известно, на стандартном приложении она длится >=20 минут какой бы мощности сервер ни взять. Достаточно долго получается. (имеется в виду полная синхронизация базы в случае когда никаких изменений нет)

Я задумался, а что там в этот момент происходит. Запустил SQL профайлер.
Оказалось, что у меня синхронизация длится 22 минуты 49 секунд.

При этом большую часть времени сервер выполняет такие запросы:
1.

select name, change_tracking_state_desc from sys.columns AS Cols inner join sys.fulltext_index_columns AS FTSCols inner join sys.fulltext_indexes as FTS on FTSCols.object_id = FTS.object_id on Cols.object_id = FTSCols.object_id where Cols.column_id = FTSCols.column_id and Cols.object_id = object_id('TableName')

где TableName – SQL имя таблицы

выполняется примерно 8713 *2 = 17426 запросов длительностью по 50-70 миллисекунд, что дает от 15 до 20 минут. (Такое огромное число запросов получается потому что в базе 8713 таблиц и при синхронизации выполняется 2 прохода)


2.

exec [sys].sp_pkeys N'TableName',NULL,NULL

… также примерно 8713 *2 = 17426 запросов длительностью по 10 миллисекунд, что дает 3 минуты ожидания.

Итого в сумме из 23 минут эти 2 запроса выполнялись примерно от 18 до 23 минут.
На первый взгляд не очень эффективно получается.

Большую часть времени сервер приложения делает запросы к системных объектам SQL сервера, а мы ждем. Причем, получается, что разработчики ядра аксапты проигнорировали базовые правила выполнения SQL запросов и делают десятки тысяч запросов там, где можно было обойтись одним запросом, разобрать его результаты и уже использовать их. Это дало бы значительное ускорение.
Но попробуем улучшить то, что имеем.

Запустил первый запрос без фильтров. Т.е. убрал условие
Cols.object_id = object_id('TableName')

Результат – всего навсего 25 записей !!!

Т.е. при синхронизации большую часть времени аос без конца выполняет запрос к системным вьюхам, а по факту обрабатывает конечную выборку из 25 записей. Совсем интересно стало:
sys.columns содержит 172 000 записей.
sys.fulltext_indexes содержит 19 записей.
sys.fulltext_index_columns содержит 25 записей.

Примерно понятно куда девается время.

Попробуем ускорить.
Сперва попытались в лоб, применив что-то типа этого :
Попробовал создать материализованный View так чтобы оптимизатор SQL использовал его при построении плана запроса. Оказалось, что в SQL Server нельзя применять Schema binding к системным представлениям. (Была идея создать представление, проиндексировать его, чтобы оптимизатор вместо нашего джоина реально выполнял запрос по индексу на view в котором лежит всего навсего 25 записей.)

Но, к сожалению, это оказалось невозможно в отличие от оракла. Плюс это все работает только для Enterprise версии. Т.е. для Standart edition все равно не сработало бы.
(у меня SQL 2016 SP1 Standart Edition. Ax 2013 build 6.3.6000.8144 (самый свежий на текущий момент)

Попробовал модифицировать описанный выше способ.
Создал свои таблички
sysZcolumns
sysZfulltext_indexes
sysZfulltext_index_columns

и заполнил их также как системные представления

Затем при помощи утилиты HEX Editor Neo
https://www.hhdsoftware.com/Downloads/free-hex-editor

пропатчил ax32Serv.exe так, что он стал отправлять запрос к нашим табличкам вместо системных :

select name, change_tracking_state_desc from sysZcolumns AS Cols inner join sysZfulltext_index_columns AS FTSCols inner join sysZfulltext_indexes as FTS on FTSCols.object_id = FTS.object_id on Cols.object_id = FTSCols.object_id where Cols.column_id = FTSCols.column_id and Cols.object_id = object_id('TableName')

Время проблемного запроса упало с 50-70 до 5 миллисекунд.
Время полной синхронизации теперь заняло 8:04. Ускорение примерно в 3 раза
Хорошо, но недостаточно.

Подошел чуть радикальнее.
Создал табличку sysZFTSC
Поля:
object_id
column_id
nam
change_tracking_state_des

Заполнил на основании системных представлений. Табличка содержит 25 записей.
Подправил ax32serv.exe так чтобы уходил запрос такого вида.

select nam , change_tracking_state_des  from sysZFTSC    AS Cols 
where 
 1 = 1  and Cols.object_id = object_id('TableName')

Т.е. джоин из 3 системных вьюх превратился в запрос по табличке из 25 записей.
Длительность запроса упала до 0,1-2 миллисекунд. (В начале синхронизации идут запросы длительностью 2 миллисекунды, а затем время падает до 0,1. Почему SQL повел себя так, до конца непонятно). Это уже лучше. Хотя сильно на время синхронизации это не повлияло. Возможно надо было сделать больше измерений, набрав статистику, но нет времени на это.

Что еще можно сделать. Как-то исправить 2-й запрос

exec [sys].sp_pkeys

- не получается. Там системная хранимка работает.

В аналогичном приложение на ax4 c которого мы переходим, в базе 1406 таблиц. Синхронизация идет меньше минуты и вообще отклик очень быстрый.

Попробовал перепроверить конфигурацию в 2012-й и поотключал все, что можно, включил только необходимый минимум как в 4-ке.
Таблички в базе при этом не удаляются, но удаляются ряд индексов. Время упало до 6:35

Еще я заметил что если DEL_ табличка отключена ключом SysDeletedObjects* то при синхронизации табличка совсем удаляется из базы. И ряд запросов к системных вьюхам и хранимкам с именем этой таблички не выполняется. Например, наш 2-й запрос.

На этом можно сэкономить. У нас от стандарта используется стандартный набор: расчеты с клиентами, поставщиками, управление запасами. Много табличек в базе лежат пустые и в принципе не нужны.

Сейчас в базе 8713 таблиц и 1370 view.

Проверил конфигурацию, отключил все что не нужно.
На каждом отключенном конфключе джобом создал дочерний ключ

SysDeltedObjects_XXX и прописал его в соответствующую табличку. (Оказывается если создать ключ с именем соответствующем шаблону SysDeletedObjects* то ядро по особому обрабатывает такой ключ – оно удаляет физически из базы таблички с таким ключом)

После синхронизации число табличек уменьшилось до 4364. View – до 667. Т.е. примерно в 2 раза.

Время полной синхронизации упало до 4:28 минут.

Т.е. получили ускорение в 5,1 раз.

Интересно что время выполнения запроса

exec [sys].sp_pkeys N'TableName',NULL,NULL

стало уже не 10 миллисекунд а 4-5 миллисекунды. Что неудивительно, так как стало меньше записей в системных табличках сиквела.

sys.columns раньше содержал 172 тысячи записей, а теперь 90 тысяч.

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

Класс по заполнению таблички sysZFTSC и табличка – во вложении. Поставил заполнение sysZFTSC перед и до super() в appl.dbsynchronise()



Вопрос к участникам :

1. Кто-нибудь что-то подобное делал ? Есть еще способы ускорения синхронизации? Какие риски такого подхода ?


2. Хотелось бы еще ускорить работы с базой модели.
Потому что при первом открытии некоторые формы думают по 6-10 секунд загружая определения методов таблиц, полей, и EDT (также как и при синхронизации делаются тысячи запросов, например, каждый метод таблички грузится отдельным запросом ). А потом еще выполняют запрос к БД для получения данных. Получение данных из БД мы можем оптимизировать (вылизали так, что отклик идет мгновенно), а работу с моделью – похоже нет.Хотя там используются хранимые процедуры, поэтому можно их допилить.

Но может кому-то удавалось.


3. Как с этим обстоят дела в D365 ? Там описанные проблемы актуальны?


4. Какие риски такого подхода вы видите ?