You’re offline. This is a read only version of the page.
5
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))))
I don't have the answer, but let me make it easier for others to understand the query.
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.QTYBy the way, did you try to check the execution plan?
Share
Report
All responses (
Answers (
