May 23, 2012 at 11:09 am
Hi All, i have this query:
SELECT B.BUSINESS_UNIT, B.INV_ITEM_ID, C.DESCR, B.DESCR,
B.UNIT_MEASURE_STD, B.INV_PROD_FAM_CD, B.INV_ITEM_GROUP,
B.CATEGORY_CD, B.CUMULATIVE_QTY, B.CM_UNIT_COST,
B.TOTAL_COST, D.DESCR, B.DESCR60,C.SETID,C.INV_PROD_FAM_CD,
(CONVERT(CHAR(10),C.EFFDT,121)),D.SETID,D.INV_ITEM_GROUP,(CONVERT(CHAR(10),D.EFFDT,121))
FROM PS_CO_RUN_STKVAL A
, ((PS_CO_IN_STCK_C_VW B LEFT OUTER JOIN PS_INV_ITEM_FAM C
ON C.INV_PROD_FAM_CD = B.INV_PROD_FAM_CD AND C.SETID = 'COSAP' )
LEFT OUTER JOIN PS_INV_ITEM_GROUP D ON B.INV_ITEM_GROUP = D.INV_ITEM_GROUP AND D.SETID = 'COSAP' )
WHERE A.OPRID = 'CO883362'
AND ( (B.BUSINESS_UNIT = A.BUSINESS_UNIT OR A.BUSINESS_UNIT = '')
AND A.OPRID = 'CO883362'
AND A.RUNCNTLID = '1'
AND (( A.CO_IN_ITEM_OPT = '00' AND ( B.BUSINESS_UNIT = A.BUSINESS_UNIT OR A.BUSINESS_UNIT = ''))
OR ( A.CO_IN_ITEM_OPT = '01' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID = A.INV_ITEM_ID)
OR ( A.CO_IN_ITEM_OPT = '02' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT E.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL E WHERE E.SETID = 'COSAP' AND E.INV_ITEM_GROUP = A.INV_ITEM_GROUP))
OR ( A.CO_IN_ITEM_OPT = '03' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT F.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL F WHERE F.SETID = 'COSAP' AND F.INV_PROD_FAM_CD = A.INV_PROD_FAM_CD))
OR ( A.CO_IN_ITEM_OPT = '04' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT G.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL G, PS_MS_ITM_INV_VW H WHERE G.SETID = H.SETID AND G.INV_ITEM_ID = H.INV_ITEM_ID
AND G.SETID = 'COSAP' AND H.CATEGORY_CD = A.CATEGORY_CD)))
AND B.DT_TIMESTAMP = (SELECT MAX( I.DT_TIMESTAMP)
FROM PS_CO_IN_KARDEX I WHERE (I.BUSINESS_UNIT = B.BUSINESS_UNIT) AND I.INV_ITEM_ID = B.INV_ITEM_ID
AND I.TRANSACTION_DATE <= '10/05/2012')
AND B.SEQ_NBR = (SELECT MAX( J.SEQ_NBR) FROM PS_CO_IN_KARDEX J HERE (J.BUSINESS_UNIT = B.BUSINESS_UNIT) AND J.INV_ITEM_ID = B.INV_ITEM_ID
AND J.DT_TIMESTAMP = B.DT_TIMESTAMP) )
The delay is on the table PS_CO_IN_KARDEX where there are millions of records
Can anyone Help me please.
____________________________________________________________________________
Rafo*
May 23, 2012 at 11:15 am
Well, that is step one.
Please read the second article I reference below in my signature block regarding performance problems. We need the DDL of the tables including indexes, the actual execution plan for the query, probably some sample data. The article will walk you through getting and posting what we need to help you.
May 23, 2012 at 11:20 am
Overall the plan looks OK. You are burning some time in a key lookup. Have you considered including those columns in another index?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 23, 2012 at 11:24 am
This is probaby the part that's killing your query speed:
AND (( A.CO_IN_ITEM_OPT = '00' AND ( B.BUSINESS_UNIT = A.BUSINESS_UNIT OR A.BUSINESS_UNIT = ''))
OR ( A.CO_IN_ITEM_OPT = '01' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID = A.INV_ITEM_ID)
OR ( A.CO_IN_ITEM_OPT = '02' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT E.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL E WHERE E.SETID = 'COSAP' AND E.INV_ITEM_GROUP = A.INV_ITEM_GROUP))
OR ( A.CO_IN_ITEM_OPT = '03' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT F.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL F WHERE F.SETID = 'COSAP' AND F.INV_PROD_FAM_CD = A.INV_PROD_FAM_CD))
OR ( A.CO_IN_ITEM_OPT = '04' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT G.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL G, PS_MS_ITM_INV_VW H WHERE G.SETID = H.SETID AND G.INV_ITEM_ID = H.INV_ITEM_ID
AND G.SETID = 'COSAP' AND H.CATEGORY_CD = A.CATEGORY_CD)))
All those complex OR clauses, with sub-queries added to them, are making SQL Server give up on finding a good execution plan and just brute-force the whole thing.
Depending on how this query is used, you might be able to break it into multiple queries, possibly as insert statements into a temp table, run them sequentially, and then query the final result.
The execution plan supports that analysis. Look at all the index scans! Add in a few key lookups (which it has) and you have a death-row query (it's a "killer execution plan").
Can you break it up?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 5:44 pm
Edit: Removed post. Stepped over the line.
May 23, 2012 at 6:03 pm
You're not wrong, i work with peoplesoft.
____________________________________________________________________________
Rafo*
May 23, 2012 at 6:24 pm
xRafo (5/23/2012)
You're not wrong, i work with peoplesoft.
Thought so. I provided SQL Server support to a team of 4 PeopleSoft Developers (one was also the PeopleSoft Administrator) for five years at a previous employer. Although I didn't actually touch the software, I worked very closely with them.
May 23, 2012 at 7:26 pm
xRafo (5/23/2012)
The delay is on the table PS_CO_IN_KARDEX where there are millions of records. Can anyone Help me please.
There are a number of potentially serious problems in the estimated query plan you attached, but a proper analysis requires the execution plan captured after a successful execution of the query (that is, with the SSMS Actual Execution Plan option selected). That plan will how runtime row counts, which are essential for diagnosis.
That said, the query is almost certainly too big and complex as it is. The SQL itself is large and difficult to read even after reformatting. This is a problem both for humans (comprehension and maintenance) and for the SQL Server's query optimizer (plan selection). The chances of it producing good plans reliably over time are quite small. That leaves us with two main options:
(1) Rewrite the query into logical parts, and store small intermediate result sets in one or more temporary tables, as Gsquared suggested. This will simplify things and, if done well, will allow the query optimizer to produce much better plans much more reliably. This is very much the preferred option.
(2) Use hints to force a particular plan shape. Which hints are required and where will depend on data in the actual execution plan, and knowledge of the data, schema, and indexing currently in place.
There are some odd constructions in that query, for example nested joins to force execution order, that suggest this query has had significant tuning effort before, and also that it may have grown significantly more complex over time. Realistically, the time has come to rewrite the monolith using standard syntax, and take it from there.
You may be able to add an index or two, add a join hint, or tweak the syntax to solve your immediate problem, but the nature of the thing means that these issues are likely to arise again and again, until the root causes are addressed.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply