AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 31.05.2019, 16:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
kurthatlevik: D365F&O – Address performance tips
Источник: https://kurthatlevik.com/2019/05/31/...formance-tips/
==============

Sometimes the smallest thing can make a huge difference. At a customer we experienced a huge load (DTU +70% average), and the LCS shows that there was a single SQL query that was the reason for the load. The data composition here was that there was close to a half million customers in the customer table, and most of them had addresses, email and phone numbers assigned to them. Except of the customers used for retail statement processing.

In LCS environment monitoring you can see this as spikes in the overview.

 





The query you typical see looks like this:

(@P1 int,@P2 nvarchar(256),@P3 int,@P4 bigint)SELECT TOP 1 T1.COUNTRYREGIONCODE,T1.DESCRIPTION,T1.ISINSTANTMESSAGE,T1.ISMOBILEPHONE,T1.ISPRIMARY,T1.ISPRIVATE,T1.LOCATION,T1.LOCATOR,T1.LOCATOREXTENSION,T1.PRIVATEFORPARTY,T1.TYPE,T1.ELECTRONICADDRESSROLES,T1.MODIFIEDBY,T1.RECVERSION,T1.PARTITION,T1.RECID FROM LOGISTICSELECTRONICADDRESS T1 WHERE ((T1.PARTITION=5637144576) AND ((T1.TYPE=@P1) AND (T1.LOCATOR@P2))) AND EXISTS (SELECT TOP 1 ‘x’ FROM LOGISTICSLOCATION T2 WHERE ((T2.PARTITION=5637144576) AND (T2.RECID=T1.LOCATION)) AND EXISTS (SELECT TOP 1 ‘x’ FROM DIRPARTYLOCATION T3 WHERE ((T3.PARTITION=5637144576) AND (((T3.LOCATION=T2.PARENTLOCATION) AND (T3.ISPOSTALADDRESS=@P3)) AND (T3.PARTY=@P4)))))

By downloading the query plan, we see that there is a index seek on the table LOGISTICSELECTRONICADDRESS.



 

This results in that the indexes don’t get a good “hit” on the logisticselectronicaddess.type.



The solution was surprisingly easy. Add Phone, Email address and URL to the customers.



 

Then the DTU drastically goes down, and normal expected performance was achieved.



 

Conclusion; Remember when having many customers, to fill inn contact information.

This just must be shared



Источник: https://kurthatlevik.com/2019/05/31/...formance-tips/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
kurthatlevik: D365F&O Retail: Combining important retail statement batch jobs Blog bot DAX Blogs 0 19.11.2018 12:11
kurthatlevik: Access Dynamics AX (aka ‘7’) performance counters with “&debug=develop”. Blog bot DAX Blogs 0 03.02.2016 19:12
emeadaxsupport: AX Performance Troubleshooting Checklist Part 2 Blog bot DAX Blogs 0 09.09.2014 16:11
crminthefield: Microsoft CRM Online Performance Tips & Tricks - Part 1 Blog bot Dynamics CRM: Blogs 0 11.03.2013 21:11
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 14:02.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.