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 issues.
When I add the following:
InventHazardousGroup (InventTable - Outer Join)
... I see no performance problems in our development environment, however in a production environment the request is very slow, which means that the form takes a long time to load.
The SQL Statement trace log returned the following result in both environments:
(I finished the list of fields, etc., because 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 a reason why it should be so slow in one environment but not in another? The data that I tested in the development environment, quite recently, about 1 month. I have the same performance problem in a production environment in another company.