Form performance when adding Outer Join DataSource

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.

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 usingForce Literals. I have used it sparingly in a few places and it has had a major impact on performance.

Check the indexes in AX exist in SQL Server.

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 acreate statisticsof the tables.

What version of SQL server are you running in your development, and what version in production. Do aDBCC 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?

What Others Are Reading