Dynamics Community Forum Thread Details

Hi All,

When i call EcoResProductDetailsExtended form directly from AOT the query 1 is generated and it results the data in few seconds but where the same form is called from EcoResProductPerCompanyListPage through hyperLink menu item the Query 2 is generated with WHsinventTable as the 2nd join the query but the WHSInventTable is not configured in our system which results in creating a temp table tempdb."DBO".t102647_2E56DD754F1749FAAB9CC613BC6CA61E and the results are retrieved after a minute.

Can any one know why the joins of tables are completely varying with these calls and how to avoid this changes in query each time?

Thanks,

Query 1 :

SELECT * FROM INVENTTABLE T1 CROSS JOIN INVENTTABLEMODULE T2 CROSS JOIN INVENTTABLEMODULE T3 CROSS JOIN INVENTTABLEMODULE T4 CROSS JOIN INVENTITEMLOCATION T5 LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUPITEM T6 ON ((T6.PARTITION=?)
AND ((T1.ITEMID=T6.ITEMID) AND (T1.DATAAREAID=T6.ITEMDATAAREAID))) LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUPITEM T7 ON ((T7.PARTITION=?) AND ((T1.ITEMID=T7.ITEMID) AND (T1.DATAAREAID=T7.ITEMDATAAREAID))) LEFT OUTER JOIN INVENTITEMGROUPITEM T8 ON ((T8.PARTITION=?) AND ((T1.ITEMID=T8.ITEMID) AND (T1.DATAAREAID=T8.ITEMDATAAREAID))) LEFT OUTER JOIN INVENTMODELGROUPITEM T9 ON ((T9.PARTITION=?) AND ((T1.ITEMID=T9.ITEMID) AND (T1.DATAAREAID=T9.ITEMDATAAREAID))) CROSS JOIN ECORESPRODUCT T10 LEFT OUTER JOIN ECORESPRODUCT T11 ON ((T11.PARTITION=?) AND (T1.PRODUCT=T11.RECID)) LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUP T12 ON ((T12.PARTITION=?) AND (T6.STORAGEDIMENSIONGROUP=T12.RECID)) LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUP T13 ON ((T13.PARTITION=?) AND (T7.TRACKINGDIMENSIONGROUP=T13.RECID)) LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUPPRODUCT T14 ON ((T14.PARTITION=?) AND (T10.RECID=T14.PRODUCT)) LEFT OUTER JOIN ECORESPRODUCTMASTERMODELINGPOLICY T15 ON ((T15.PARTITION=?) AND (T10.RECID=T15.PRODUCTMASTER)) LEFT OUTER JOIN ECORESPRODUCTTRANSLATION T16 ON ((T16.PARTITION=?) AND ((T16.LANGUAGEID=?) AND (T10.RECID=T16.PRODUCT))) LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUP T17 ON ((T17.PARTITION=?) AND (T14.PRODUCTDIMENSIONGROUP=T17.RECID)) WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND ((T2.MODULETYPE=?) AND (T2.ITEMID=T1.ITEMID))) AND (((T3.PARTITION=?) AND (T3.DATAAREAID=?)) AND ((T3.MODULETYPE=?) AND (T3.ITEMID=T1.ITEMID))) AND (((T4.PARTITION=?) AND (T4.DATAAREAID=?)) AND ((T4.MODULETYPE=?) AND (T4.ITEMID=T1.ITEMID))) AND (((T5.PARTITION=?) AND (T5.DATAAREAID=?)) AND ((T5.INVENTDIMID=?) AND (T1.ITEMID=T5.ITEMID))) AND ((T10.PARTITION=?) AND (T1.PRODUCT=T10.RECID)) ORDER BY T1.ITEMID OPTION(FAST 4)

Query 2:
SELECT * FROM INVENTTABLE T1 LEFT OUTER JOIN tempdb."DBO".t102647_2E56DD754F1749FAAB9CC613BC6CA61E T2 ON (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.ITEMID=?)) LEFT OUTER JOIN INVENTMODELGROUPITEM T3 ON ((T3.PARTITION=?) AND ((T1.ITEMID=T3.ITEMID) AND (T1.DATAAREAID=T3.ITEMDATAAREAID))) LEFT OUTER JOIN INVENTITEMGROUPITEM T4 ON ((T4.PARTITION=?) AND ((T1.ITEMID=T4.ITEMID) AND (T1.DATAAREAID=T4.ITEMDATAAREAID))) LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUPITEM T5 ON ((T5.PARTITION=?) AND ((T1.ITEMID=T5.ITEMID) AND (T1.DATAAREAID=T5.ITEMDATAAREAID))) LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUPITEM T6 ON ((T6.PARTITION=?) AND ((T1.ITEMID=T6.ITEMID) AND (T1.DATAAREAID=T6.ITEMDATAAREAID))) CROSS JOIN ECORESPRODUCT T7 CROSS JOIN INVENTTABLEMODULE T8 CROSS JOIN INVENTTABLEMODULE T9 CROSS JOIN INVENTTABLEMODULE T10 CROSS JOIN INVENTITEMLOCATION T11 LEFT OUTER JOIN ECORESPRODUCT T12 ON ((T12.PARTITION=?) AND (T1.PRODUCT=T12.RECID)) LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUP T13 ON ((T13.PARTITION=?) AND (T5.TRACKINGDIMENSIONGROUP=T13.RECID)) LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUP T14 ON ((T14.PARTITION=?) AND (T6.STORAGEDIMENSIONGROUP=T14.RECID)) LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUPPRODUCT T15 ON ((T15.PARTITION=?) AND (T7.RECID=T15.PRODUCT)) LEFT OUTER JOIN ECORESPRODUCTTRANSLATION T16 ON ((T16.PARTITION=?) AND (((T16.LANGUAGEID=?) OR (T16.LANGUAGEID=?)) AND (T7.RECID=T16.PRODUCT))) LEFT OUTER JOIN ECORESPRODUCTMASTERMODELINGPOLICY T17 ON ((T17.PARTITION=?) AND (T7.RECID=T17.PRODUCTMASTER)) LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUP T18 ON ((T18.PARTITION=?) AND ((T15.PRODUCTDIMENSIONGROUP=T18.RECID) AND (T15.PRODUCTDIMENSIONGROUP=T18.RECID))) WHERE (((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND (((((((((T1.ITEMID=?) AND (T7.DISPLAYPRODUCTNUMBER=?)) AND (T7.RECID=?)) AND (T16.PRODUCT=?)) AND (T16.LANGUAGEID>=?)) OR ((((T1.ITEMID=?) AND (T7.DISPLAYPRODUCTNUMBER=?)) AND (T7.RECID=?)) AND (T16.PRODUCT>?))) OR (((T1.ITEMID=?) AND (T7.DISPLAYPRODUCTNUMBER=?)) AND (T7.RECID>?))) OR ((T1.ITEMID=?) AND (T7.DISPLAYPRODUCTNUMBER>?))) OR (T1.ITEMID>?))) AND ((T7.PARTITION=?) AND (T1.PRODUCT=T7.RECID)) AND (((T8.PARTITION=?) AND (T8.DATAAREAID=?)) AND ((T8.MODULETYPE=?) AND (T8.ITEMID=T1.ITEMID))) AND (((T9.PARTITION=?) AND (T9.DATAAREAID=?)) AND ((T9.MODULETYPE=?) AND (T9.ITEMID=T1.ITEMID))) AND (((T10.PARTITION=?) AND (T10.DATAAREAID=?)) AND ((T10.MODULETYPE=?) AND (T10.ITEMID=T1.ITEMID))) AND (((T11.PARTITION=?) AND (T11.DATAAREAID=?)) AND ((T11.INVENTDIMID=?) AND (T1.ITEMID=T11.ITEMID))) ORDER BY T1.ITEMID,T7.DISPLAYPRODUCTNUMBER,T7.RECID,T16.PRODUCT,T16.LANGUAGEID OPTION(FAST 4)