Three things to consider: 1. p12.PODQtyOrdered > p12.PODQtyReceived was the FIRST of the three segments of the PODetail join statement – BUT – it was the SECOND of the four segments of the modified code – you might try putting that segment first 2. All of the parentheses may not be needed – perhaps none are needed – in the PODetail join statement 3. Where as the p12.PODQtyOrdered > p12.PODQtyReceived statement uses a “greater than” it requires a greater comparison effort than an “= 0” or an “IS NULL” – you might try “<>” – i.e., p12.PODQtyOrdered <> p12.PODQtyReceived
Just a few thoughts that might be worth trying. Steve Sweeney From: Steve Fogelson <st...@upnorthsports.com> Sent: Monday, January 9, 2023 2:07 PM To: TeraScript-Talk@terascript.com Subject: TeraScript-Talk: Help on SQL please (OFF TOPIC) I have an SQL command I am working on and am a little stumped. Using MySQL. The command works great at 0 seconds execution time. SELECT i2.OV_ID1 AS OV_ID, p3.OP_ID, i2.OV_ID2, p4.OP_ID AS OP_ID2, i2.InSKU AS OV_SKU, i2.InPartNumber AS OV_Part_Number, concat (o9.OVWDDescription,'/',o10.OVWDDescription) AS OV_Name, o10.OVWDDescription as OV_Name_Option, o6.OV_Price1, o6.OV_Price1 AS OVPrice, o6.OV_Cmsn1 AS OVCmsn, o6.OV_Better_Value, o7.OV_Price1, o7.OV_Price1 AS OVPrice, o6.OV_Price1 + o7.OV_Price1 AS OVPrice10, i8.ImageID, o9.OVWDDescription, i8.ImageFileName, i2.InOwnerAvailable, i2.InOwnerAvailable2, i2.InOwnerAvailable3, i2.InVendorWh1, i2.InVendorWh2, i2.InVendorWh3, i2.InVendorWh4, i2.InVendorWh5, i2.InVendorWh6, i2.InVendorWh7, i2.InVendorWh1PO, i2.InVendorWh2PO, i2.InVendorWh3PO, i2.InVendorWh4PO, i2.InVendorWh5PO, i2.InVendorWh6PO, i2.InVendorWh7PO, i2.InVendorWh1PODate, i2.InVendorWh2PODate, i2.InVendorWh3PODate, i2.InVendorWh4PODate, i2.InVendorWh5PODate, i2.InVendorWh6PODate, i2.InVendorWh7PODate FROM myunsshared.inventory i2 LEFT JOIN myunsshared.ProductOptions p3 ON p3.OP_ID = '27674' LEFT JOIN myunsshared.ProductOptions p4 ON p4.OP_ID = '27675' LEFT JOIN myunsshared.OptionValue o6 ON o6.OV_ID = i2.OV_ID1 LEFT JOIN myunsshared.OptionValue o7 ON o7.OV_ID = i2.OV_ID2 LEFT JOIN myunsshared.Image i8 ON i8.ImageID = O6.ImageID LEFT JOIN myunsshared.OVWebDisplay o9 ON o9.OVWDID = O6.OVWDID LEFT JOIN myunsshared.OVWebDisplay o10 ON o10.OVWDID = O7.OVWDID LEFT JOIN myunsshared.PODetail p12 ON ( (p12.PODQtyOrdered > p12.PODQtyReceived) AND (p12.Ord_ID IS NULL) AND (p12.PODDateClosed = 0)) WHERE i2.P_ID = '24074' ; When I add the code in red, it jumps to 16 to 20 seconds execution time. Both p12.PODPartNumber and i2.InPartNumber have “TINYTEXT” datatype with index length of 20. Any ideas that would help? SELECT i2.OV_ID1 AS OV_ID, p3.OP_ID, i2.OV_ID2, p4.OP_ID AS OP_ID2, i2.InSKU AS OV_SKU, i2.InPartNumber AS OV_Part_Number, concat (o9.OVWDDescription,'/',o10.OVWDDescription) AS OV_Name, o10.OVWDDescription as OV_Name_Option, o6.OV_Price1, o6.OV_Price1 AS OVPrice, o6.OV_Cmsn1 AS OVCmsn, o6.OV_Better_Value, o7.OV_Price1, o7.OV_Price1 AS OVPrice, o6.OV_Price1 + o7.OV_Price1 AS OVPrice10, i8.ImageID, o9.OVWDDescription, i8.ImageFileName, i2.InOwnerAvailable, i2.InOwnerAvailable2, i2.InOwnerAvailable3, i2.InVendorWh1, i2.InVendorWh2, i2.InVendorWh3, i2.InVendorWh4, i2.InVendorWh5, i2.InVendorWh6, i2.InVendorWh7, i2.InVendorWh1PO, i2.InVendorWh2PO, i2.InVendorWh3PO, i2.InVendorWh4PO, i2.InVendorWh5PO, i2.InVendorWh6PO, i2.InVendorWh7PO, i2.InVendorWh1PODate, i2.InVendorWh2PODate, i2.InVendorWh3PODate, i2.InVendorWh4PODate, i2.InVendorWh5PODate, i2.InVendorWh6PODate, i2.InVendorWh7PODate, p12.PODQtyOrdered AS POOrder, p12.PODQtyCommitted AS POCommitted FROM myunsshared.inventory i2 LEFT JOIN myunsshared.ProductOptions p3 ON p3.OP_ID = '27674' LEFT JOIN myunsshared.ProductOptions p4 ON p4.OP_ID = '27675' LEFT JOIN myunsshared.OptionValue o6 ON o6.OV_ID = i2.OV_ID1 LEFT JOIN myunsshared.OptionValue o7 ON o7.OV_ID = i2.OV_ID2 LEFT JOIN myunsshared.Image i8 ON i8.ImageID = O6.ImageID LEFT JOIN myunsshared.OVWebDisplay o9 ON o9.OVWDID = O6.OVWDID LEFT JOIN myunsshared.OVWebDisplay o10 ON o10.OVWDID = O7.OVWDID LEFT JOIN myunsshared.PODetail p12 ON ((p12.PODPartNumber = i2.InPartNumber) AND (p12.PODQtyOrdered > p12.PODQtyReceived) AND (p12.Ord_ID IS NULL) AND (p12.PODDateClosed = 0)) WHERE i2.P_ID = '24074' ; Steve Internet Commerce Solutions ________________________________ To unsubscribe from this list, please send an email to lists...@terascript.com<mailto:lists...@terascript.com> with "unsubscribe terascript-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@terascript.com with "unsubscribe terascript-talk" in the body.
