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

6 thoughts 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. 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

Leave a Reply to Umesh Pandit Cancel 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.