SBX - Search With Button

SBX - Forum Post Title

Slow Performance Query in AX, but not in SQL !

Microsoft Dynamics AX Forum

JauB asked a question on 15 May 2019 9:47 AM
My Badges

Question Status

Unanswered

Hi Everybody

I developped one query using AOT, I saw that when i execute it from AX it take a long time, but if I execute it directly from Managment Studio for SQL so it is exectued in few secondes !

What can be the issue ?

Thanks.

Reply

Hi JauB,

Can you provide some more information? There how did you test the query in SQL. What was created in AX? Did you use the trace parser to find out what is happening? Probably there is some caching involved.

Reply
JauB responded on 15 May 2019 12:35 PM
My Badges

Hi André

Thanks for your answer.

So my query is tested in SQL by measuring execution time (SELECT * FROM MY_VIEW). This Vue is Created in AX using AOT (see screenshot attached).

I never used Trace Parser, so i juste run it now and putted the result in the file attached. I don't know how to read it or the informations that I can get from ! It will be good if some one can explain to me to expore it :)

Please, when you say "Probably there is some caching involved." Can you give me more details about this point ?

Reply

Hi JauB,

The database time (bottom part) of the trace is the real time spent by the SQL server. You did show one quick and two slow database actions. Can you tell what exactly is the purpose of this view? The design looks a bit odd as you are increasing the levels, but the range is on a datasource from another part of the hierarchy. The InventLocation could possibly be linked on the same level as the datasource for WMSPickingRoute. This could be a cause of the bad performance. If you just need some detail fields, you can also consider computed columns in the view.

Related to the slow and quick methods: What type of ranges did you apply here? Or did you join the view with other tables in one of these methods?

Reply
JauB responded on 15 May 2019 6:19 PM
My Badges

Hi André

"The InventLocation could possibly be linked on the same level as the datasource for WMSPickingRoute" How to do it ? When creating a View, we can add only one DataSource by level !

I have 4 ranges on this view like this :

One range on WMSORDERTRANS (expeditionStatus)

One range on SALESTABLE (DocumentStatus)

2 ranges on WMSPICKINGROUTE (expeditionStatus and TransType)

each range is created on its right level.

"Related to the slow and quick methods: What type of ranges did you apply here? Or did you join the view with other tables in one of these methods?" I don't have any method. I try only to execute the view from the AOT (by selecting the view and making Ctrl + O). And my view is not joined to any tables !

Reply

SBX - Two Col Forum

SBX - Migrated JS