May 25, 2016 at 1:54 pm
I have a query that I am forced to improve. I can't add indexes or fix the tables in any way. This turd must be polished until it shines so... if anyone can take a look at this mess and make a suggestion or two that would be great. Please refrain from phrases like 'maybe you could...'. If you know a better join/where clause, great. If you know a structure change that would help, great. Please no academia.
I have tried numerous things but the execution plan doesn't get any better.
In the sub query, I have tried removing the existing where clause and replacing it with just:
WHERE ( O.ShipMethod IN ( 'EXPEDITED', 'GROUND' )
AND OA.[Type] IN ( 1, 7, 31, 36, 39 )
)
OR
( O.ShipMethod NOT IN ( 'EXPEDITED', 'GROUND' )
AND OA.[Type] IN ( 1, 4, 7, 31, 36 )
)
AND OA.ActDate > @LastRunDate
But it is missing/not getting all the results. The original is just where-ing the orderno's that are 'in' the exact where clause the original select .... thought it would work.
Any ideas?...
Thanks:)
-- used in where clause later
DECLARE @LastRunDate datetime;
Set @LastRunDate = (SELECT LTRIM(RIGHT(Comment,
LEN(Comment)
- CHARINDEX(':',
Comment))) AS LastStatusUpdate
FROM VendorComments
WHERE VendorNo = 'Admin'
AND Comment LIKE 'Last Inventory Adjustment Export:%')
SELECT A.StoreNo ,
LEFT(A.OrderNo,
LEN(A.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(A.OrderNo)))) + '-'
+ '00000000000009999' AS WebOrderNo ,
A.SKUNo ,
A.GTIN ,
A.HostSKUNo ,
A.Quantity ,
A.ActionCode ,
MAX(A.ActionDate) AS ActionDate ,
A.LineNum ,
A.GroupNum ,
A.GroupLineNum
FROM ( SELECT CASE WHEN O.[Status] = 65
OR O.VendorNo IN ( 'DEFAULT', 'HOT', 'HOTSI',
'HOTSM', 'HOTMI' )
THEN '0000'
ELSE O.VendorNo
END AS StoreNo ,
O.SalesPerson AS WebOrderNo ,
VI.SKUNo ,
VI.GTIN ,
I.SKUNo AS HostSKUNo ,
OI.Quantity ,
OI.LineNum ,
OI.GroupNum ,
OASC.GroupLineNum ,
OI.[Status] ,
O.OrderNo ,
CASE WHEN O.[Status] = 65
AND OI.[Status] NOT IN ( 1, 7 ) THEN 'O'
ELSE CASE WHEN OI.[Status] IN ( 1, 7 ) THEN 'X'
WHEN OI.[Status] = 0
OR ( OI.[Status] = 2
AND OI.GroupNum IS NULL
) THEN 'A'
WHEN ( OI.[Status] = 2
AND OI.GroupNum IS NOT NULL
AND O.ShipMethod NOT IN (
'EXPEDITED', 'GROUND' )
)
OR ( OI.[Status] IN ( 2, 4 )
AND OI.GroupNum IS NOT NULL
AND OASC.GroupLineNum IS NULL
AND O.ShipMethod IN ( 'EXPEDITED',
'GROUND' )
) THEN 'P'
WHEN ( OI.[Status] = 4
AND O.ShipMethod NOT IN (
'EXPEDITED', 'GROUND' )
)
OR ( OI.[Status] = 4
AND OASC.GroupLineNum IS NOT NULL
AND O.ShipMethod IN ( 'EXPEDITED',
'GROUND' )
) THEN 'S'
END
END AS ActionCode ,
CASE WHEN OI.[Status] = 0
OR ( OI.[Status] = 2
AND OI.GroupNum IS NULL
)
AND OA.ActDate IS NULL
THEN ( SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] IN ( 0, 36, 56 )
)
WHEN OI.[Status] = 4
AND OA.ActDate IS NULL
THEN ( SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] = 4
)
WHEN OI.[Status] = 7
AND OA.ActDate IS NULL
THEN ( SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] = 7
)
ELSE CONVERT(VARCHAR(23), OA.ActDate, 121)
END AS ActionDate
FROM Orders O
INNER JOIN OrderItems OI ON OI.OrderNo = O.OrderNo
INNER JOIN OrderDetails OD ON OD.OrderNo = O.OrderNo
AND OI.LineNum = OD.LineNum
INNER JOIN Items I ON I.ItemId = OD.ItemId
INNER JOIN VendorItems VI ON VI.ItemId = OD.ItemId
AND VI.VendorNo = 'Admin'
AND VI.fgSpecialPO = CASE
WHEN O.fgSpecialPO IN (
1, 3, 5 ) THEN 1
WHEN O.fgSpecialPO IN (
0, 2, 4 ) THEN 0
END
LEFT JOIN OrderActions OASC ON OASC.OrderNo = O.OrderNo
AND OASC.[Type] = 39
AND OASC.GroupLineNum = OI.GroupNum
LEFT JOIN OrderActions OA ON OA.OrderNo = O.OrderNo
AND OA.[Type] IN ( 1, 7, 31,
36 )
AND OA.GroupLineNum = OI.GroupNum
LEFT JOIN ItemIdSkuNoXref IX ON IX.ItemId = OD.ItemId
WHERE LEFT(O.OrderNo,
LEN(O.OrderNo) - CHARINDEX('-',
REVERSE(RTRIM(O.OrderNo)))) IN (
SELECT DISTINCT
LEFT(OO.OrderNo,
LEN(OO.OrderNo) - CHARINDEX('-',
REVERSE(RTRIM(OO.OrderNo))))
FROM Orders OO
INNER JOIN OrderActions OOA ON OOA.OrderNo = OO.OrderNo
WHERE ( ( OO.ShipMethod IN ( 'EXPEDITED', 'GROUND' )
AND OOA.[Type] IN ( 1, 7, 31, 36, 39 )
)
OR ( OO.ShipMethod NOT IN ( 'EXPEDITED',
'GROUND' )
AND OOA.[Type] IN ( 1, 4, 7, 31, 36 )
)
)
)
) A
WHERE A.StoreNo NOT LIKE 'REDIR%' AND A.ActionDate > @LastRunDate
GROUP BY A.StoreNo ,
A.WebOrderNo ,
A.HostSKUNo ,
A.GTIN ,
A.SKUNo ,
A.Quantity ,
A.ActionCode ,
A.LineNum ,
A.GroupNum ,
A.GroupLineNum ,
A.OrderNo
ORDER BY A.WebOrderNo ,
A.StoreNo;
May 25, 2016 at 2:21 pm
Can you post the execution plan?
Can you provide some sample data and the table definitions?
See the link in my signature on how to do that.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 25, 2016 at 2:23 pm
You have functions around columns in the where clause. Game over until you can eliminate that. In aggregate that is the second worst thing I see in my performance tuning consulting work (which I have been doing for 20 years now).
The OR isn't going to help here either.
If you want real help here you have to give us the actual execution plan of a run, FULL table definitions to include all keys and indexes.
Did you try OPTION (RECOMPILE) on the query?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2016 at 2:35 pm
Here is your code formatted so that we can read it.
-- used in where clause later
DECLARE @LastRunDate DATETIME;
SET @LastRunDate = (
SELECT LTRIM(RIGHT(Comment, LEN(Comment) - CHARINDEX(':', Comment))) AS LastStatusUpdate
FROM VendorComments
WHERE VendorNo = 'Admin'
AND Comment LIKE 'Last Inventory Adjustment Export:%'
)
SELECT A.StoreNo,
LEFT(A.OrderNo, LEN(A.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(A.OrderNo)))) + '-' + '00000000000009999' AS WebOrderNo,
A.SKUNo,
A.GTIN,
A.HostSKUNo,
A.Quantity,
A.ActionCode,
MAX(A.ActionDate) AS ActionDate,
A.LineNum,
A.GroupNum,
A.GroupLineNum
FROM (
SELECT CASE
WHEN O.[Status] = 65
OR O.VendorNo IN ('DEFAULT', 'HOT', 'HOTSI', 'HOTSM', 'HOTMI')
THEN '0000'
ELSE O.VendorNo
END AS StoreNo,
O.SalesPerson AS WebOrderNo,
VI.SKUNo,
VI.GTIN,
I.SKUNo AS HostSKUNo,
OI.Quantity,
OI.LineNum,
OI.GroupNum,
OASC.GroupLineNum,
OI.[Status],
O.OrderNo,
CASE
WHEN O.[Status] = 65
AND OI.[Status] NOT IN (1, 7)
THEN 'O'
ELSE CASE
WHEN OI.[Status] IN (1, 7)
THEN 'X'
WHEN OI.[Status] = 0
OR (
OI.[Status] = 2
AND OI.GroupNum IS NULL
)
THEN 'A'
WHEN (
OI.[Status] = 2
AND OI.GroupNum IS NOT NULL
AND O.ShipMethod NOT IN ('EXPEDITED', 'GROUND')
)
OR (
OI.[Status] IN (2, 4)
AND OI.GroupNum IS NOT NULL
AND OASC.GroupLineNum IS NULL
AND O.ShipMethod IN ('EXPEDITED', 'GROUND')
)
THEN 'P'
WHEN (
OI.[Status] = 4
AND O.ShipMethod NOT IN ('EXPEDITED', 'GROUND')
)
OR (
OI.[Status] = 4
AND OASC.GroupLineNum IS NOT NULL
AND O.ShipMethod IN ('EXPEDITED', 'GROUND')
)
THEN 'S'
END
END AS ActionCode,
CASE
WHEN OI.[Status] = 0
OR (
OI.[Status] = 2
AND OI.GroupNum IS NULL
)
AND OA.ActDate IS NULL
THEN (
SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] IN (0, 36, 56)
)
WHEN OI.[Status] = 4
AND OA.ActDate IS NULL
THEN (
SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] = 4
)
WHEN OI.[Status] = 7
AND OA.ActDate IS NULL
THEN (
SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] = 7
)
ELSE CONVERT(VARCHAR(23), OA.ActDate, 121)
END AS ActionDate
FROM Orders O
INNER JOIN OrderItems OI ON OI.OrderNo = O.OrderNo
INNER JOIN OrderDetails OD ON OD.OrderNo = O.OrderNo
AND OI.LineNum = OD.LineNum
INNER JOIN Items I ON I.ItemId = OD.ItemId
INNER JOIN VendorItems VI ON VI.ItemId = OD.ItemId
AND VI.VendorNo = 'Admin'
AND VI.fgSpecialPO = CASE
WHEN O.fgSpecialPO IN (1, 3, 5)
THEN 1
WHEN O.fgSpecialPO IN (0, 2, 4)
THEN 0
END
LEFT JOIN OrderActions OASC ON OASC.OrderNo = O.OrderNo
AND OASC.[Type] = 39
AND OASC.GroupLineNum = OI.GroupNum
LEFT JOIN OrderActions OA ON OA.OrderNo = O.OrderNo
AND OA.[Type] IN (1, 7, 31, 36)
AND OA.GroupLineNum = OI.GroupNum
LEFT JOIN ItemIdSkuNoXref IX ON IX.ItemId = OD.ItemId
WHERE LEFT(O.OrderNo, LEN(O.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(O.OrderNo)))) IN (
SELECT DISTINCT LEFT(OO.OrderNo, LEN(OO.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(OO.OrderNo))))
FROM Orders OO
INNER JOIN OrderActions OOA ON OOA.OrderNo = OO.OrderNo
WHERE (
(
OO.ShipMethod IN ('EXPEDITED', 'GROUND')
AND OOA.[Type] IN (1, 7, 31, 36, 39)
)
OR (
OO.ShipMethod NOT IN ('EXPEDITED', 'GROUND')
AND OOA.[Type] IN (1, 4, 7, 31, 36)
)
)
)
) A
WHERE A.StoreNo NOT LIKE 'REDIR%'
AND A.ActionDate > @LastRunDate
GROUP BY A.StoreNo,
A.WebOrderNo,
A.HostSKUNo,
A.GTIN,
A.SKUNo,
A.Quantity,
A.ActionCode,
A.LineNum,
A.GroupNum,
A.GroupLineNum,
A.OrderNo
ORDER BY A.WebOrderNo,
A.StoreNo;
For starters, remove the DISTINCT in the IN. It's not needed, and it will likely cause a scan.
WHERE LEFT(O.OrderNo, LEN(O.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(O.OrderNo)))) IN (
SELECT DISTINCT LEFT(OO.OrderNo, LEN(OO.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(OO.OrderNo))))
This is what is referred to as a non-sargable WHERE clause:
WHERE LEFT(O.OrderNo, LEN(O.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(O.OrderNo))))
There are lots of links about this, here is a good read:
http://www.sql-server-performance.com/2007/t-sql-where/
Finally, try this:
-- used in where clause later
DECLARE @LastRunDate DATETIME;
SET @LastRunDate = (
SELECT LTRIM(RIGHT(Comment, LEN(Comment) - CHARINDEX(':', Comment))) AS LastStatusUpdate
FROM VendorComments
WHERE VendorNo = 'Admin'
AND Comment LIKE 'Last Inventory Adjustment Export:%'
)
SELECT LEFT(OO.OrderNo, LEN(OO.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(OO.OrderNo)))) as OrderNumber
INTO #TmpTable
FROM Orders OO
INNER JOIN OrderActions OOA ON OOA.OrderNo = OO.OrderNo
WHERE ((OO.ShipMethod IN ('EXPEDITED', 'GROUND')
AND OOA.[Type] IN (1, 7, 31, 36, 39))
OR (OO.ShipMethod NOT IN ('EXPEDITED', 'GROUND')
AND OOA.[Type] IN (1, 4, 7, 31, 36)))
SELECT A.StoreNo,
LEFT(A.OrderNo, LEN(A.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(A.OrderNo)))) + '-' + '00000000000009999' AS WebOrderNo,
A.SKUNo,
A.GTIN,
A.HostSKUNo,
A.Quantity,
A.ActionCode,
MAX(A.ActionDate) AS ActionDate,
A.LineNum,
A.GroupNum,
A.GroupLineNum
FROM (
SELECT CASE
WHEN O.[Status] = 65
OR O.VendorNo IN ('DEFAULT', 'HOT', 'HOTSI', 'HOTSM', 'HOTMI')
THEN '0000'
ELSE O.VendorNo
END AS StoreNo,
O.SalesPerson AS WebOrderNo,
VI.SKUNo,
VI.GTIN,
I.SKUNo AS HostSKUNo,
OI.Quantity,
OI.LineNum,
OI.GroupNum,
OASC.GroupLineNum,
OI.[Status],
O.OrderNo,
CASE
WHEN O.[Status] = 65
AND OI.[Status] NOT IN (1, 7)
THEN 'O'
ELSE CASE
WHEN OI.[Status] IN (1, 7)
THEN 'X'
WHEN OI.[Status] = 0
OR (
OI.[Status] = 2
AND OI.GroupNum IS NULL
)
THEN 'A'
WHEN (
OI.[Status] = 2
AND OI.GroupNum IS NOT NULL
AND O.ShipMethod NOT IN ('EXPEDITED', 'GROUND')
)
OR (
OI.[Status] IN (2, 4)
AND OI.GroupNum IS NOT NULL
AND OASC.GroupLineNum IS NULL
AND O.ShipMethod IN ('EXPEDITED', 'GROUND')
)
THEN 'P'
WHEN (
OI.[Status] = 4
AND O.ShipMethod NOT IN ('EXPEDITED', 'GROUND')
)
OR (
OI.[Status] = 4
AND OASC.GroupLineNum IS NOT NULL
AND O.ShipMethod IN ('EXPEDITED', 'GROUND')
)
THEN 'S'
END
END AS ActionCode,
CASE
WHEN OI.[Status] = 0
OR (
OI.[Status] = 2
AND OI.GroupNum IS NULL
)
AND OA.ActDate IS NULL
THEN (
SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] IN (0, 36, 56)
)
WHEN OI.[Status] = 4
AND OA.ActDate IS NULL
THEN (
SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] = 4
)
WHEN OI.[Status] = 7
AND OA.ActDate IS NULL
THEN (
SELECT CONVERT(VARCHAR(23), MAX(ActDate), 121)
FROM OrderActions OA
WHERE OA.OrderNo = O.OrderNo
AND OA.[Type] = 7
)
ELSE CONVERT(VARCHAR(23), OA.ActDate, 121)
END AS ActionDate,
LEFT(O.OrderNo, LEN(O.OrderNo) - CHARINDEX('-', REVERSE(RTRIM(O.OrderNo)))) OrderNumber
FROM Orders O
INNER JOIN OrderItems OI ON OI.OrderNo = O.OrderNo
INNER JOIN OrderDetails OD ON OD.OrderNo = O.OrderNo
AND OI.LineNum = OD.LineNum
INNER JOIN Items I ON I.ItemId = OD.ItemId
INNER JOIN VendorItems VI ON VI.ItemId = OD.ItemId
AND VI.VendorNo = 'Admin'
AND VI.fgSpecialPO = CASE
WHEN O.fgSpecialPO IN (1, 3, 5)
THEN 1
WHEN O.fgSpecialPO IN (0, 2, 4)
THEN 0
END
LEFT JOIN OrderActions OASC ON OASC.OrderNo = O.OrderNo
AND OASC.[Type] = 39
AND OASC.GroupLineNum = OI.GroupNum
LEFT JOIN OrderActions OA ON OA.OrderNo = O.OrderNo
AND OA.[Type] IN (1, 7, 31, 36)
AND OA.GroupLineNum = OI.GroupNum
LEFT JOIN ItemIdSkuNoXref IX ON IX.ItemId = OD.ItemId) A
WHERE EXISTS(SELECT T.OrderNumber FROM #TmpTable T WHERE T.OrderNumber = A.OrderNumber)
AND A.StoreNo NOT LIKE 'REDIR%'
AND A.ActionDate > @LastRunDate
GROUP BY A.StoreNo,
A.WebOrderNo,
A.HostSKUNo,
A.GTIN,
A.SKUNo,
A.Quantity,
A.ActionCode,
A.LineNum,
A.GroupNum,
A.GroupLineNum,
A.OrderNo
ORDER BY A.WebOrderNo,
A.StoreNo;
And, I agree with Kevin.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 25, 2016 at 6:38 pm
There are 2 ways to go.
1. Pay to a qualified SQL developer who knows about normalisation rules and tends to follow them to develop a new, proper database to support your application.
2. Pay for a new hardware, a new version of SQL Server, move the data to in-memory storage and pray that the hardware capacity will grow faster than the scale of your database.
There is not much what can be improved in your query.
It's database design which has to be improved.
_____________
Code for TallyGenerator
May 27, 2016 at 7:19 am
So, thanks to Michael L John and TheSQLGuru for actual helpful pertinent information that followed my question.
I have followed your pattern somewhat and gone with CTEs to gather the 'base' and then join on that minus the non-sargable nonsense. It was overwhelming coming into a 10 year old project that wasn't designed right in the first place so, sorry for simply throwing up SQL all over the place.
May 27, 2016 at 11:04 am
beaudamore (5/27/2016)
So, thanks to Michael L John and TheSQLGuru for actual helpful pertinent information that followed my question.I have followed your pattern somewhat and gone with CTEs to gather the 'base' and then join on that minus the non-sargable nonsense. It was overwhelming coming into a 10 year old project that wasn't designed right in the first place so, sorry for simply throwing up SQL all over the place.
Welcome.
And welcome to my world!! This is the kind of thing I have been doing for clients for going on 2 decades now. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply