Dynamics Community Forum Thread Details

What is the following statement doing? We suspect it is killing the performance of the MRP run

We have captured stats for it, and there are 128 billion (!) logical reads generated by this statement (by far the most of any sql).
The 3 tables used all contain less than 1 million records.

SELECT T1.ITEMID,T1.COVINVENTDIMID,T1.REQDATE,T1.DIRECTION,T1.REFTYPE,T1.OPENSTATUS,T1.QTY,T1.COVQTY,T1.REFID,T1.KEEP,
T1.REQDATEDLVORIG,T1.FUTURESDAYS,T1.FUTURESMARKED,T1.OPRNUM,T1.ACTIONQTYADD,T1.ACTIONDAYS,T1.ACTIONMARKED,T1.ACTIONTYPE,
T1.PLANVERSION,T1.ORIGINALQUANTITY,T1.ISDERIVEDDIRECTLY,T1.PRIORITY,T1.ACTIONDATE,T1.FUTURESDATE,T1.INVENTTRANSORIGIN,
T1.BOMREFRECID,T1.MARKINGREFINVENTTRANSORIGIN,T1.LEVEL_,T1.BOMTYPE,T1.ITEMROUTEID,T1.ITEMBOMID,T1.ISFORECASTPURCH,
T1.LASTPLANRECID,T1.REQTIME,T1.FUTURESTIME,T1.SUPPLYDEMANDSUBCLASSIFICATION,T1.REQPROCESSID,T1.INTERCOMPANYPLANNEDORDER,
T1.PMFPLANGROUPPRIMARYISSUE,T1.CUSTACCOUNTID,T1.CUSTGROUPID,T1.ISDELAYED,T1.MCRPRICETIMEFENCE,T1.PDSEXPIRYDATE,T1.PDSSELLABLEDAYS,
T1.PMFACTIONQTYADD,T1.PMFCOBYREFRECID,T1.PMFPLANGROUPID,T1.PMFPLANGROUPPRIORITY,T1.PMFPLANNINGITEMID,T1.PMFPLANPRIORITYCURRENT,
T1.REQUISITIONLINE,T1.ISFORCEDITEMBOMID,T1.ISFORCEDITEMROUTEID,T1.FUTURESCALCULATED,T1.MZKWRAPPERID,T1.RECVERSION,T1.PARTITION,
T1.RECID,T2.RECEIPTRECID,T2.RECID,T3.ITEMID,T3.COVINVENTDIMID,T3.REQDATE,T3.DIRECTION,T3.REFTYPE,T3.OPENSTATUS,T3.QTY,
T3.COVQTY,T3.REFID,T3.KEEP,T3.REQDATEDLVORIG,T3.FUTURESDAYS,T3.FUTURESMARKED,T3.OPRNUM,T3.ACTIONQTYADD,T3.ACTIONDAYS,
T3.ACTIONMARKED,T3.ACTIONTYPE,T3.PLANVERSION,T3.ORIGINALQUANTITY,T3.ISDERIVEDDIRECTLY,T3.PRIORITY,T3.ACTIONDATE,T3.FUTURESDATE,
T3.INVENTTRANSORIGIN,T3.BOMREFRECID,T3.MARKINGREFINVENTTRANSORIGIN,T3.LEVEL_,T3.BOMTYPE,T3.ITEMROUTEID,T3.ITEMBOMID,
T3.ISFORECASTPURCH,T3.LASTPLANRECID,T3.REQTIME,T3.FUTURESTIME,T3.SUPPLYDEMANDSUBCLASSIFICATION,T3.REQPROCESSID,
T3.INTERCOMPANYPLANNEDORDER,T3.PMFPLANGROUPPRIMARYISSUE,T3.CUSTACCOUNTID,T3.CUSTGROUPID,T3.ISDELAYED,T3.MCRPRICETIMEFENCE,
T3.PDSEXPIRYDATE,T3.PDSSELLABLEDAYS,T3.PMFACTIONQTYADD,T3.PMFCOBYREFRECID,T3.PMFPLANGROUPID,T3.PMFPLANGROUPPRIORITY,
T3.PMFPLANNINGITEMID,T3.PMFPLANPRIORITYCURRENT,T3.REQUISITIONLINE,T3.ISFORCEDITEMBOMID,T3.ISFORCEDITEMROUTEID,T3.FUTURESCALCULATED,
T3.MZKWRAPPERID,T3.RECVERSION,T3.PARTITION,T3.RECID FROM REQTRANS T1 CROSS JOIN REQTRANSCOV T2 CROSS JOIN REQTRANS
T3 WHERE (((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) AND ((((((T1.DIRECTION=@P3) OR (T1.QTY<@P4)) OR (T1.COVQTY<@P5))
AND (T1.ISDERIVEDDIRECTLY=@P6)) AND ((T1.REFTYPE=@P7) OR (T1.REFTYPE=@P8))) AND (T1.PLANVERSION=@P9))) AND
(((T2.PARTITION=@P10) AND (T2.DATAAREAID=@P11)) AND (T2.ISSUERECID=T1.RECID)) AND (((T3.PARTITION=@P12) AND
(T3.DATAAREAID=@P13)) AND (((T3.RECID=T2.RECEIPTRECID) AND (T3.PLANVERSION=@P14)) AND (T3.ISDERIVEDDIRECTLY=@P15)))
AND EXISTS (SELECT 'x' FROM REQUNSCHEDULEDORDERS T4 WHERE (((T4.PARTITION=@P16) AND
(T4.DATAAREAID=@P17)) AND (((T4.PROCESSID=@P18) AND (T4.ENGINEBUNDLE=@P19)) AND (T4.REFID=T3.REFID))))

SELECT * FROM REQTRANS T1 CROSS JOIN REQTRANSCOV T2 CROSS JOIN REQTRANS T3 WHERE T1.PARTITION=@P1 AND T1.DATAAREAID=@P AND ((T1.DIRECTION=@P3 OR T1.QTY

By the way, did you try to check the execution plan?

Quick Links

Leaderboard