April 9, 2021 at 3:32 pm
Tell me how to do it better. It takes to long to run. its because of the Totals by truck but I have to have them.
SELECT CONVERT(VARCHAR, omprequestedshipdate, 101) AS date,
sl.ompshippingmethodid,
cmoname,
sl.uomptrucknumber,
sl.uompdropsequence,
sl.ompsalesorderid,
sl.ompclosed,
/***************************Total by Order**************************/
(SELECT Isnull(Sum(omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl1
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl1.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid = 'FGM') AS FGMqty,
(SELECT Isnull(Sum(uomlbasevolume * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl2
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl2.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid = 'FGM') AS FGMcubes,
(SELECT Isnull(Sum(omlfullunitpricebase * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl3
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl3.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid = 'FGM') AS FGMTotal,
(SELECT Isnull(Sum(omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl4
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl4.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid IN ( 'FGI', 'FGP' )) AS FGIqty,
(SELECT Isnull(Sum(uomlbasevolume * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl5
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl5.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid IN ( 'FGI', 'FGP' )) AS FGIcubes,
(SELECT Isnull(Sum(omlfullunitpricebase * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl6
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl6.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid IN ( 'FGI', 'FGP' )) AS FGITotal,
sl.uompvolumetotal,
sl.ompordertotalbase,
sl.ompordercommentstext,
/***************************Totals by truck****************************/
(SELECT Isnull(Sum(omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl7
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl7.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
AND imppartclassid = 'FGM' group by sl7.UOMPTRUCKNUMBER) AS FGMqtyTot,
(SELECT Isnull(Sum(omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl8
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl8.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
AND imppartclassid = 'FGI' group by sl8.UOMPTRUCKNUMBER) AS FGIqtyTot,
(SELECT Isnull(Sum(uomlbasevolume * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl9
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl9.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
AND imppartclassid = 'FGM' group by sl9.UOMPTRUCKNUMBER) AS FGMcubesTot,
(SELECT Isnull(Sum(uomlbasevolume * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl10
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl10.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
AND imppartclassid = 'FGI' group by sl10.UOMPTRUCKNUMBER) AS FGIcubesTot,
(SELECT Isnull(Sum(omlfullunitpricebase * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl11
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl11.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
AND imppartclassid = 'FGM' group by sl11.UOMPTRUCKNUMBER) AS FGMTotalTot,
(SELECT Isnull(Sum(omlfullunitpricebase * omlorderquantity), 0)
FROM m1_kf.dbo.salesorders sl12
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl12.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
AND imppartclassid = 'FGI' group by sl12.UOMPTRUCKNUMBER) AS FGITotalTot,
(select Isnull(Sum(ompOrderTotalBase), 0) from SalesOrders sl13
WHERE sl13.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
group by sl13.UOMPTRUCKNUMBER) AS FGITotalTot
/*********************************************************************************/
FROM m1_kf.dbo.salesorders sl
LEFT OUTER JOIN m1_kf.dbo.organizations
ON cmoorganizationid = ompcustomerorganizationid
WHERE omprequestedshipdate >= Dateadd(day, -30, Getdate())
AND uomptrucknumber != ''
AND ompshippingmethodid != 'DC'
ORDER BY sl.ompclosed,
CONVERT(VARCHAR, sl.omprequestedshipdate, 101),
sl.uomptrucknumber ,
sl.uompdropsequence
April 9, 2021 at 6:06 pm
Do you have an execution plan for this?
Do you have indexes on any of the objects?
Are you getting "SEEKS" or "SCANS" on the tables?
How large is the final data set (approx in KB)?
Are you pulling the data into memory or into an application?
I ask these questions as they will greatly impact the performance and solution. Without an execution plan, all we see are tables, columns, and potentially views, so we have no idea why it is slow. You may be pulling 1 row per table or 1 billion rows per table. I ask about seeks and scans as well as indexes because seeks are generally a lot faster than scans and indexes can help turn a scan into a seek (not always mind you).
I ask about how large the final data set is because if you are pulling 1 Gb along a 100 Mbps connection, you are going to need 10 seconds JUST to get the data. But if the final result set is 10 MB, you are going to need 0.1 seconds to get the data. (NOTE - these are estimated times and involve rounding and ignore any overhead and other things on the network... just something to give you an idea).
The last question about if the data is going into an application is a two-fold question because it leads to the question "is the performance slow due to the SQL query OR is it slow due to the application?". The second question that comes up is "have you tried sorting the data on the application side instead of the SQL side?".
Now, completely ignoring ALL of the above, my first eyeball thought on performance improvement would be to scrap some of those nested SELECTs in favor of JOINs. Not sure if it'll work for you, but some of those look to be VERY similar chunks of code. Looking at the code for FGMqtyTot and FGMcubesTot, they LOOK to be ALMOST identical pieces of code just with the FGMcubesTot one multiplying by another field. The FROM, JOIN, and WHERE portions look identical. If it was me, I'd put that into a separate JOIN.
I've never been a fan of nested selects inside the SELECT portion of a query. I find them harder to read, debug, and optimize. I generally use CTE's instead of nested selects, but if I DO need a nested select, it always falls into a JOIN or WHERE clause.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 9, 2021 at 6:07 pm
You are hammering the same tables multiple times. You will probably get better perf if you use cross-tab queries to reduce the number of times the tables are read. I have modified your query (UNTESTED) for the "Total by Order" section. You can take a stab at doing the same for the rest of the query
SELECT date = CONVERT( varchar, omprequestedshipdate, 101 )
, sl.ompshippingmethodid
, cmoname
, sl.uomptrucknumber
, sl.uompdropsequence
, sl.ompsalesorderid
, sl.ompclosed
/***************************Total by Order**************************/
, oVal.FGMqty
, oVal.FGMcubes
, oVal.FGMTotal
, oVal.FGIqty
, oVal.FGIcubes
, oVal.FGITotal
, sl.uompvolumetotal
, sl.ompordertotalbase
, sl.ompordercommentstext
/***************************Totals by truck****************************/
/* TODO */
FROM m1_kf.dbo.salesorders AS sl
LEFT OUTER JOIN m1_kf.dbo.organizations
ON cmoorganizationid = ompcustomerorganizationid
OUTER APPLY (
SELECT FGMqty = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN omlorderquantity ELSE 0 END ), 0 )
, FGMcubes = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN uomlbasevolume * omlorderquantity ELSE 0 END ), 0 )
, FGMTotal = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN omlfullunitpricebase * omlorderquantity ELSE 0 END ), 0 )
, FGIqty = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN omlorderquantity ELSE 0 END ), 0 )
, FGIcubes = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN uomlbasevolume * omlorderquantity ELSE 0 END ), 0 )
, FGITotal = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN omlfullunitpricebase * omlorderquantity ELSE 0 END ), 0 )
FROM m1_kf.dbo.salesorders AS sl1
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl1.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid IN ( 'FGM', 'FGI', 'FGP' )
) as oVal(FGMqty, FGMcubes, FGMTotal, FGIqty, FGIcubes, FGITotal)
WHERE omprequestedshipdate >= DATEADD( DAY, -30, GETDATE())
AND uomptrucknumber != ''
AND ompshippingmethodid != 'DC'
ORDER BY sl.ompclosed, CONVERT( varchar, sl.omprequestedshipdate, 101 ), sl.uomptrucknumber, sl.uompdropsequence;
April 9, 2021 at 6:47 pm
That works like a dream...Thank You
SELECT date = CONVERT( varchar, omprequestedshipdate, 101 )
, sl.ompshippingmethodid
, cmoname
, sl.uomptrucknumber
, sl.uompdropsequence
, sl.ompsalesorderid
, sl.ompclosed
/***************************Total by Order**************************/
, oVal.FGMqty
, oVal.FGMcubes
, oVal.FGMTotal
, oVal.FGIqty
, oVal.FGIcubes
, oVal.FGITotal
, uompvolumetotal
, ompordertotalbase
, ompordercommentstext
/***************************Totals by truck****************************/
, bVal.FGMqtyTot
, bVal.FGMcubesTot
, bVal.FGMTotalTot
, bVal.FGIqtyTot
, bVal.FGIcubesTot
, bVal.FGITotalTot
FROM m1_kf.dbo.salesorders AS sl
LEFT OUTER JOIN m1_kf.dbo.organizations
ON cmoorganizationid = ompcustomerorganizationid
OUTER APPLY (
SELECT FGMqty = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN omlorderquantity ELSE 0 END ), 0 )
, FGMcubes = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN uomlbasevolume * omlorderquantity ELSE 0 END ), 0 )
, FGMTotal = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN omlfullunitpricebase * omlorderquantity ELSE 0 END ), 0 )
, FGIqty = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN omlorderquantity ELSE 0 END ), 0 )
, FGIcubes = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN uomlbasevolume * omlorderquantity ELSE 0 END ), 0 )
, FGITotal = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN omlfullunitpricebase * omlorderquantity ELSE 0 END ), 0 )
FROM m1_kf.dbo.salesorders AS sl1
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl1.ompsalesorderid = sl.ompsalesorderid
AND imppartclassid IN ( 'FGM', 'FGI', 'FGP' )
) as oVal(FGMqty, FGMcubes, FGMTotal, FGIqty, FGIcubes, FGITotal)
/*********************************************************************************************************************************/
OUTER APPLY (
SELECT FGMqtyTot = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN omlorderquantity ELSE 0 END ), 0 )
, FGMcubesTot = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN uomlbasevolume * omlorderquantity ELSE 0 END ), 0 )
, FGMTotalTot = ISNULL( SUM( CASE WHEN imppartclassid = 'FGM' THEN omlfullunitpricebase * omlorderquantity ELSE 0 END ), 0 )
, FGIqtyTot = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN omlorderquantity ELSE 0 END ), 0 )
, FGIcubesTot = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN uomlbasevolume * omlorderquantity ELSE 0 END ), 0 )
, FGITotalTot = ISNULL( SUM( CASE WHEN imppartclassid IN ( 'FGI', 'FGP' ) THEN omlfullunitpricebase * omlorderquantity ELSE 0 END ), 0 )
FROM m1_kf.dbo.salesorders AS sl1
LEFT OUTER JOIN m1_kf.dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
LEFT OUTER JOIN m1_kf.dbo.partrevisions
ON imrpartid = omlpartid
LEFT OUTER JOIN m1_kf.dbo.parts
ON imppartid = omlpartid
WHERE sl1.UOMPTRUCKNUMBER = sl.UOMPTRUCKNUMBER
AND imppartclassid IN ( 'FGM', 'FGI', 'FGP' )
group by UOMPTRUCKNUMBER
) as bVal(FGMqtyTot, FGMcubesTot, FGMTotalTot, FGIqtyTot, FGIcubesTot, FGITotalTot)
WHERE omprequestedshipdate >= DATEADD( DAY, -30, GETDATE())
AND uomptrucknumber != ''
AND ompshippingmethodid != 'DC'
ORDER BY sl.ompclosed, CONVERT( varchar, sl.omprequestedshipdate, 101 ), sl.uomptrucknumber, sl.uompdropsequence
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply