Form performance when adding Outer Join DataSource

Question

I have a custom form with the following Datasource setup;

SalesTable
SalesLine (SalesTable - Inner Join)
InventTable (SalesLine - Inner Join)
InventDim (SalesLine - Inner Join)

...which works without any performance issue.

When I add the following;

InventHazardousGroup (InventTable - Outer Join)

...I see no performance issues in our development environment, however in the production environment the query is terribly slow, which means the form takes a long time to load.

SQL Statement trace log produced the following output in both environments;

(I have ended the field list with etc becuase it is long);

SELECT A.SALESID,A.SALESNAME,A.RESERVATION,A.CUSTACCOUNT,A.INVOICEACCOUNT,A.DELIVERYDATE,A.DELIVERYADDRESS,A.URL,A.PURCHORDERFORMNUM,A.SALESTAKER,A.SALESGROUP,A.FREIGHTSLIPTYPE,A.DOCUMENTSTATUS,A.INTERCOMPANYORIGINALSALESID,etc
FROM  {OJ INVENTTABLE C LEFT OUTER JOIN INVENTHAZARDOUSGROUP E ON ((E.DATAAREAID=?)
AND (C.HAZARDOUSGROUPID=E.HAZARDOUSGROUPID))},SALESTABLE A,SALESLINE B,INVENTDIM D
WHERE ((A.DATAAREAID=?)
AND (A.SALESTYPE=?))
AND ((B.DATAAREAID=?)
AND (A.SALESID=B.SALESID))
AND ((C.DATAAREAID=?)
AND (B.ITEMID=C.ITEMID))
AND ((D.DATAAREAID=?)
AND (B.INVENTDIMID=D.INVENTDIMID))
ORDER BY A.DATAAREAID,A.SALESID OPTION(FAST 1)

Is there any reason why this should be so slow in one environment but not in another? The data I have tested on in the development environment is quite recent, around 1 month old. I have the same performance problem in the production environment, in a different company.

score 2 · Accepted Answer · answered Sep 13 '12 at 18:28

I have had this issue crop up before and I do not think that it has anything to do with the outer join. It is most likely because the number of queries that the form generates in production vs. development. SQL attempts to cache the query when it is used and AX likes to pass objects into SQL as variables. Most likely, you have a bad cache plan in Production that then gets used by all the users. I suggest using Force Literals . I have used it sparingly in a few places and it has had a major impact on performance.

It may be worth noting, that to accomplish this on a form, you can use the following syntax: `salesTable_ds.query().literals(true);` — Vince Perta, Aug 30 '13 at 12:10

score 1 · Answer 2 · answered May 16 '12 at 15:59

1

Check the indexes in AX exist in SQL Server.

answered May 16 '12 at 15:59

David Lawson

796
3
10

They all match, I have seen that issue before though. Synchronising the data dictionary in the AOT fixes that. – AnthonyBlake May 17 '12 at 15:29

score 0 · Answer 3 · answered May 16 '12 at 11:25

0

Check the execution plan of the query.

The easiest is to log to the infolog (long queries setup on the SQL tab on User settings), then double-click the offending query.

Otherwise try an index rebuild of the tables and a create statistics of the tables.

answered May 16 '12 at 11:25

Jan B. Kjeldsen

17,817
5
32
50

Thanks Jan. I am tempted to ask the DBA about this kind of maintenance. I know I can rebuild indexes from within AX, do you know if is it possible to create statistics within AX or is that a SQL server command? – AnthonyBlake May 16 '12 at 13:31
By the way Jan - the query above came from switching on logging on the SQL tab, it was logged as a long running query. Would a screenshot of the execution plan help? – AnthonyBlake May 16 '12 at 13:33
Yes, if you expand the execution plan. You may see a "Full table scan" if it did not find an useful index. – Jan B. Kjeldsen May 16 '12 at 14:07
You can run a SQL server maintenance plan to do the statistics on the production environment (outside job hours). The AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS should be enabled on the database. – Jan B. Kjeldsen May 16 '12 at 14:09
2

Every Dynamics AX installation should have frequent automatically rebuilding or reorganizing of indexes, and in addition updating of statistics even more frequently. I prefer to use the ones shipped with the Dynamics Performance project. http://archive.msdn.microsoft.com/DynamicsPerf – Skaue May 16 '12 at 16:06
@Skaue I was unaware of that tool, looks very useful – AnthonyBlake May 17 '12 at 08:37

score 0 · Answer 4 · answered Jun 22 '12 at 03:05

0

What version of SQL server are you running in your development, and what version in production. Do a DBCC TRACESTATUS(-1); to determine what flags are on in dev vs prod. Make sure those do not differ. I have seen issues where when they do, a performance issue shows up in one, but not the other.

Does the query ALWAYS run slow in production, or does it only SOMETIMES run slow?

answered Jun 22 '12 at 03:05

esac

24,099
38
122
179

I will raise a call with our DBA, thanks. The form is always slow in production, even out of business hours, and in any company it is opened in. – AnthonyBlake Jun 22 '12 at 08:24

Form performance when adding Outer Join DataSource

4 Answers4