Dynamics365

D365F&O – Address performance 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

I appreciate that I can be a part of this worldwide blog community—as a consultant working from Norway, the blog lets me share more than 20 years of experience with Microsoft Dynamics 365. Along the way, I participated in developing retail, PDA/RF, barcoding, master data, kitting and WMS-solutions for Dynamics. My blog focuses on my deepest interests and expertise: along with a 360 degree view of digital transformation topics, I welcome opportunities to dive into retail and intercompany supply chain automation, logistics, and production—everything that is moving around in a truly connected enterprise. As Enterprise Architect on Dynamics 365, I specialize in strategic development and planning for corporate vertical solutions and works to build international networks that increase knowledge and understanding for Dynamics 365. As an advocate for both providers and customers, I'm committed to ensure that customers constantly changing needs are meet, and I see community as key for increasing expertise. I welcome you to connect with me.

6 comments on “D365F&O – Address performance tips

  1. thanks Kurt very much! this might be very relevant for us.

    Just to make sure I understand – for the “Retail” customers, I need to create “fake” contact details, question is –
    for all types (email, fax, phone, URL) or just 1 is enough? or to the same types that OTHER customers have?

    Like

    • Jupp. Create contact information on as many customers as possible, but most important is the customer that is specified on the retail store table. This trick is actually related to sales order creation and invoicing, so it is also relevant in other scenario’s. And it is ok to just have blank email/url/phone, as the only thing that matter is that there is a record. Happy D365’ing

      Like

  2. Baber Owais

    Hi Kurt,

    Just wondering what you initially mentioned that customers have phone numbers, email assigned to them and later on you said to specify phone, email address and URL under contact information tab in Customer record. So where was the email, phone numbers was previously assigned to the customer record?

    Thanks,
    Baber.

    Like

    • Hi. The majority of customers had email/phone etc. But a small kunder of customers (used in retail to post retail statements) did not have this specified. This caused the indexes to not work as expected, and the database had to do “index seeks”. When a sales order is created/invoiced, certain of these contact informations are copied to the salesTable(sales order header), and this cased the very high DTU consumption.

      Liked by 1 person

      • Baber Owais

        Thanks Kurt for the explanation. Appreciate your prompt response.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: