Performance Issue that cause High CPU utilization

Question

Post reply

Performance Issue that cause High CPU utilization

Kaniguan_SQL

Say Hey Kid

Points: 681
More actions
June 26, 2012 at 7:19 pm

#258051

Hi Forumers,
Im just running this script but it takes time to process.
more than 39,000 records that i would like to work on.
it cause high cpu utilization. any idea guys. thank you in advance.
;With CTE
AS
(
Select
v.JOURNALID
,v.TRANSDATE
,v.ITEMID
,v.QTY
,v.COSTAMOUNT
,v.JOURNALTYPE
,v.BOMLINE
From INVENTJOURNAL v with (nolock)
Where v.TRANSDATE >= '2012/06/01' and v.TRANSDATE <'2012/06/05'
and v.JOURNALTYPE=3
and v.BOMLINE=0
),
CTE2 AS
(
Select
t1.JOURNALID
,t1.TRANSDATE
,t1.ITEMID as MotherITEMID
,t1.QTY
,t1.COSTAMOUNT
,t1.JOURNALTYPE
,t1.BOMLINE
,inv.ITEMID as ChildItemid
,inv.QTY as BOMQTY
,inv.COSTAMOUNTPOSTED
,i.WMSLOCATIONID
,inv.STATUSISSUE
,inv.TRANSTYPE
From CTE t1
Cross Apply (Select Itemid, qty , COSTAMOUNTPOSTED, STATUSISSUE, TRANSTYPE, TRANSREFID, INVENTDIMID
from INVENTTRAN with (nolock)
Where TRANSREFID=t1.JOURNALID) inv
Left Outer Join INVENTDIN i with (nolock)
On i.INVENTDIMID=inv.INVENTDIMID
)
Select
JournalID
,Transdate
,Case When STATUSISSUE=0 Then MotherITEMID End as MotherItemID
,Case When STATUSISSUE=0 Then Qty End as Quantity
,Case When STATUSISSUE=0 Then COSTAMOUNT End as CostAmount
,Case When STATUSISSUE=1 Then ChildItemid End as ChildItemid
,Case When STATUSISSUE=1 Then BOMQTY End as BOMQty
,Case When STATUSISSUE=1 Then COSTAMOUNTPOSTED End as CostAmountPosted
,WMSLOCATIONID
From CTE2
Order by JOURNALID, BOMQty asc

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SpringTownDBA SSCertifiable Points: 6806 More actions · Answer 1

SpringTownDBA

SSCertifiable

Points: 6806

June 26, 2012 at 9:11 pm

#1505943

hard to say without the execution plan.

SQL Show SSCertifiable Points: 6143 More actions · Answer 2

SQL Show

SSCertifiable

Points: 6143

June 26, 2012 at 9:31 pm

#1505945

Better post the Execution Plan.

Kaniguan_SQL Say Hey Kid Points: 681 More actions · Answer 3

Thank you guys for the reply.

I made some adjustment in the Query. put additional filter inside the Cross Apply and the process takes only seconds.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Why nolock? You like randome, hard-to-reproduce data errors?

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

I'd need to see the actual execution plan.

And I'm with Gail (as usual) nolock = no fun.[/url]

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

There's no need at all for all the CTE's, it simply makes a trivial query look more complex than it is. If this doesn't work first time, it won't take much to fix.

Select

t1.JOURNALID

,t1.TRANSDATE

,Case When inv.STATUSISSUE=0 Then t1.ITEMID End as MotherItemID

,Case When inv.STATUSISSUE=0 Then t1.QTY End as Quantity

,Case When inv.STATUSISSUE=0 Then t1.COSTAMOUNT End as CostAmount

,Case When inv.STATUSISSUE=1 Then inv.ITEMID End as ChildItemid

,Case When inv.STATUSISSUE=1 Then inv.QTY End as BOMQty

,Case When inv.STATUSISSUE=1 Then inv.COSTAMOUNTPOSTED End as CostAmountPosted

,i.WMSLOCATIONID

From INVENTJOURNAL t1 with (nolock)

LEFT JOIN INVENTTRAN inv with (nolock) ON inv.TRANSREFID=t1.JOURNALID

Left Outer Join INVENTDIN i with (nolock) On i.INVENTDIMID=inv.INVENTDIMID

Where t1.TRANSDATE >= '2012/06/01' and t1.TRANSDATE <'2012/06/05'

and t1.JOURNALTYPE=3

and t1.BOMLINE=0

Order by

t1.JOURNALID,

Case When inv.STATUSISSUE=1 Then inv.QTY End asc

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden