February 3, 2009 at 8:59 am
statement runs very slow what have I done wrong or what could I change to make it better
SELECT A.BUSINESS_UNIT
, A.INV_ITEM_ID
, D.SAFETY_LEVEL
, D.EXCESS_LEVEL
, SUM( E.QTY_REQUESTED)
, (
SELECT CASE WHEN (SUM(T.EXPECTED_PRDN_QTY) - SUM(T.COMPLETED_QTY))
IS NULL THEN 0 ELSE SUM(T.EXPECTED_PRDN_QTY) - SUM(T.COMPLETED_QTY) END
FROM PS_SF_PRDNID_HEADR T
WHERE T.INV_ITEM_ID = B.INV_ITEM_ID
AND T.PROD_STATUS IN ('10', '15', '20', '30', '40')
AND TEXT254 <> 'C'), B.ITEM_FIELD_C6,(
SELECT SUM(OB.QTY_AVAILABLE)
FROM PS_BU_ITEMS_INV OB
WHERE OB.BUSINESS_UNIT IN ('HM001', 'HM002', 'HM005', 'HM007', 'HM008')
AND OB.INV_ITEM_ID = B.INV_ITEM_ID), (
SELECT ISNULL(SUM(IND.QTY_REQUESTED)
, 0)
FROM PS_IN_DEMAND IND
WHERE A.INV_ITEM_ID = IND.INV_ITEM_ID
AND A.BUSINESS_UNIT = IND.BUSINESS_UNIT
AND IND.INTERUNIT_STAT = 'I'), (
SELECT ISNULL(SUM(L1.DEMAND_QTY)
, 0)
FROM PS_HM_OPN_LD_IT_VW L1
, PS_HM_SHIP_TP_DEFN SD1
WHERE L1.BUSINESS_UNIT = A.BUSINESS_UNIT
AND L1.INV_ITEM_ID = A.INV_ITEM_ID
AND SD1.BUSINESS_UNIT = L1.BUSINESS_UNIT
AND L1.SCHED_DATE >= SD1.HM_SHIP1_START_DT
AND L1.SCHED_DATE <= SD1.HM_SHIP1_END_DT) , (
SELECT ISNULL(SUM(L2.DEMAND_QTY)
, 0)
FROM PS_HM_OPN_LD_IT_VW L2
, PS_HM_SHIP_TP_DEFN SD2
WHERE L2.BUSINESS_UNIT = A.BUSINESS_UNIT
AND L2.INV_ITEM_ID = A.INV_ITEM_ID
AND SD2.BUSINESS_UNIT = L2.BUSINESS_UNIT
AND L2.SCHED_DATE >= SD2.HM_SHIP2_START_DT
AND L2.SCHED_DATE <= SD2.HM_SHIP2_END_DT) , (
SELECT ISNULL(SUM(L3.DEMAND_QTY)
, 0)
FROM PS_HM_OPN_LD_IT_VW L3
, PS_HM_SHIP_TP_DEFN SD3
WHERE L3.BUSINESS_UNIT = A.BUSINESS_UNIT
AND L3.INV_ITEM_ID = A.INV_ITEM_ID
AND SD3.BUSINESS_UNIT = L3.BUSINESS_UNIT
AND L3.SCHED_DATE >= SD3.HM_SHIP3_START_DT
AND L3.SCHED_DATE <= SD3.HM_SHIP3_END_DT) , (
SELECT ISNULL(SUM(L4.DEMAND_QTY)
, 0)
FROM PS_HM_OPN_LD_IT_VW L4
, PS_HM_SHIP_TP_DEFN SD4
WHERE L4.BUSINESS_UNIT = A.BUSINESS_UNIT
AND L4.INV_ITEM_ID = A.INV_ITEM_ID
AND SD4.BUSINESS_UNIT = L4.BUSINESS_UNIT
AND L4.SCHED_DATE >= SD4.HM_SHIP4_START_DT
AND L4.SCHED_DATE <= SD4.HM_SHIP4_END_DT) , (
SELECT ISNULL(SUM(L5.DEMAND_QTY)
, 0)
FROM PS_HM_OPN_LD_IT_VW L5
, PS_HM_SHIP_TP_DEFN SD5
WHERE L5.BUSINESS_UNIT = A.BUSINESS_UNIT
AND L5.INV_ITEM_ID = A.INV_ITEM_ID
AND SD5.BUSINESS_UNIT = L5.BUSINESS_UNIT
AND L5.SCHED_DATE >= SD5.HM_SHIP5_START_DT
AND L5.SCHED_DATE <= SD5.HM_SHIP5_END_DT)
FROM PS_BU_ITEMS_INV A , PS_MASTER_ITEM_TBL B , PS_INV_ITEMS C, (PS_PL_ITEM_ATTRIB D LEFT OUTER JOIN PS_HM_IN_DEMND3_VW E ON D.BUSINESS_UNIT = E.BUSINESS_UNIT
AND D.INV_ITEM_ID = E.INV_ITEM_ID)
WHERE B.SETID = C.SETID
AND B.SETID = 'CORP1'
AND A.INV_ITEM_ID = B.INV_ITEM_ID
AND B.INV_ITEM_ID = C.INV_ITEM_ID
AND C.EFFDT = (
SELECT MAX(C_ED.EFFDT)
FROM PS_INV_ITEMS C_ED
WHERE C.SETID = C_ED.SETID
AND C.INV_ITEM_ID = C_ED.INV_ITEM_ID
AND C_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND A.INV_ITEM_ID = D.INV_ITEM_ID
AND B.INV_ITEM_GROUP = 'FIN_GOODS'
GROUP BY A.BUSINESS_UNIT , A.INV_ITEM_ID , D.SAFETY_LEVEL , D.EXCESS_LEVEL , A.QTY_ONHAND , B.ITEM_FIELD_C6, B.INV_ITEM_ID
February 3, 2009 at 9:02 am
Really hard to tell without seeing the table structures and some data.
From first looks, you could make this query better by using INNER JOIN notation to join your tables, also you could use joins instead of all the sub-queries.
February 3, 2009 at 9:12 am
Please post table definitions, index definitions and the execution plan (saved as a .sqlplan file zipped and attached)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2009 at 9:24 am
While waiting for the execution plan (actual, not estimated please), I looked through a bit of the code.
This is going to cause the query to run slow since it has to run this query for every row of data in the FROM clause. Best to move this into the FROM statement and join against it.
,(SELECT CASE WHEN (SUM(T.EXPECTED_PRDN_QTY) - SUM(T.COMPLETED_QTY)) IS NULL
THEN 0
ELSE SUM(T.EXPECTED_PRDN_QTY) - SUM(T.COMPLETED_QTY)
END
FROM PS_SF_PRDNID_HEADR T
WHERE T.INV_ITEM_ID = B.INV_ITEM_ID
AND T.PROD_STATUS IN ('10', '15', '20', '30', '40')
AND TEXT254 <> 'C'
)
It doesn't always help, but frequently I've found BETWEEN works better than:
AND L2.SCHED_DATE >= SD2.HM_SHIP2_START_DT
AND L2.SCHED_DATE <= SD2.HM_SHIP2_END_DT
It won't change performance (necessarily) but you should put your JOIN criteria with the FROM statement instead of in the WHERE clause:
FROM PS_BU_ITEMS_INV A
,PS_MASTER_ITEM_TBL B
,PS_INV_ITEMS C
,(PS_PL_ITEM_ATTRIB D
LEFT OUTER JOIN PS_HM_IN_DEMND3_VW E
ON D.BUSINESS_UNIT = E.BUSINESS_UNIT
AND D.INV_ITEM_ID = E.INV_ITEM_ID
)
Other than that, waiting for the execution plan...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2009 at 9:54 am
Grant Fritchey (2/3/2009)
This is going to cause the query to run slow since it has to run this query for every row of data in the FROM clause. Best to move this into the FROM statement and join against it.
You sure? It's a correlated subquery, true, but it's a direct equality match to the outer query, with no top or order by. The optimiser should be able to get a plan that treats it just as if it was joined.
It doesn't always help, but frequently I've found BETWEEN works better than:
AND L2.SCHED_DATE >= SD2.HM_SHIP2_START_DTAND L2.SCHED_DATE <= SD2.HM_SHIP2_END_DT
The parser or optimiser will change between into >= AND <=. If you've got a case where the two run differently, I'd be very interested in seeing it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2009 at 10:06 am
execution plan add in attachments
February 3, 2009 at 10:09 am
Table definition and index definitions please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2009 at 10:18 am
GilaMonster (2/3/2009)
Grant Fritchey (2/3/2009)
This is going to cause the query to run slow since it has to run this query for every row of data in the FROM clause. Best to move this into the FROM statement and join against it.You sure? It's a correlated subquery, true, but it's a direct equality match to the outer query, with no top or order by. The optimiser should be able to get a plan that treats it just as if it was joined.
Yeah, I think so. I don't have any faith that the optimizer is going to do well with a query like this. Smaller ones, sure, but the larger it gets, the longer it takes the optimizer to try various methods and the more likely you are to get a poor choice that's "good enough"
It doesn't always help, but frequently I've found BETWEEN works better than:
AND L2.SCHED_DATE >= SD2.HM_SHIP2_START_DTAND L2.SCHED_DATE <= SD2.HM_SHIP2_END_DT
The parser or optimiser will change between into >= AND <=. If you've got a case where the two run differently, I'd be very interested in seeing it.
You're right on this one of course. I always forget it. Don't know why I keep blocking it out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2009 at 10:24 am
Also waiting for the Index definitions, but from the plan I noticed a few Key Lookups on the same table for the same fields, I'm sure some useful indexes could take care of those and help the performance.
The percentages look small on the key lookups but I'm sure I've read somewhere that the percentages are not always accurate with regards to some of the operators in the query plan is this correct?
There also seems to be a huge Clustered Index Scan, I'm guessing after looking at the indexes we can come up with a useful index that could hopefully turn that into a seek.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 3, 2009 at 10:24 am
Table definition and index definitions please? I do not know how to get these but I am looking and learning.
February 3, 2009 at 10:31 am
twdavis (2/3/2009)
Table definition and index definitions please? I do not know how to get these but I am looking and learning.
Right Click on the Table in SSMS --> Script Table as --> Create to --> File
February 3, 2009 at 10:34 am
twdavis (2/3/2009)
execution plan add in attachments
First thing I see is several key lookups on the PS_IN_DEMAND table to get QTY_REQUESTED_BASE & QTY_BACKORDER_BASE columns. You could add these as an INCLUDE column to the PSFIN_DEMAND index.
You're getting a clustered index scan from this predicate:
[HMTST].[dbo].[PS_IN_DEMAND].[SCHED_DTTM]<=dateadd(day,(14),CONVERT_IMPLICIT(datetime,CONVERT(varchar(10),getdate(),23),0)) AND ([HMTST].[dbo].[PS_IN_DEMAND].[IN_FULFILL_STATE]='10' OR [HMTST].[dbo].[PS_IN_DEMAND].[IN_FULFILL_STATE]='20' OR [HMTST].[dbo].[PS_IN_DEMAND].[IN_FULFILL_STATE]='30' OR [HMTST].[dbo].[PS_IN_DEMAND].[IN_FULFILL_STATE]='40')
I can't see that statement in the original code you published though. Are some of these tables in the query views or UDF's? Can you post that code as well?
There are a lot Sort operations setting you up for all those Merge Joins. I think, I could be wrong, that's caused by the correlated sub-queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2009 at 10:36 am
Christopher Stobbs (2/3/2009)
The percentages look small on the key lookups but I'm sure I've read somewhere that the percentages are not always accurate with regards to some of the operators in the query plan is this correct?
100% correct. Those values are estimated costs, not actual costs. They shouldn't be taken as a perfect reflection of reality, but instead as a good guide to where the most costly parts of a query are. You still need to look at I/O, CPU, execution time, wait states...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2009 at 11:00 am
Should have seen this from the initial post, this is PeopleSoft. How was/is this query generated and supported? Is it built and maintained using the PeopleSoft tools? If so, you may not be able to do much with it unless you customize it and support it as such. Of course, I'm not a PeopleSoft Admin/Developer but I do help support PeopleSoft on the database side of things.
February 4, 2009 at 2:18 am
HI Grant,
How do I check this: "You still need to look at I/O, CPU, execution time, wait states... " for the different operations in the query plan?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply