June 26, 2012 at 7:19 pm
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
June 26, 2012 at 9:11 pm
hard to say without the execution plan.
June 26, 2012 at 9:31 pm
Better post the Execution Plan.
June 26, 2012 at 11:28 pm
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.
June 26, 2012 at 11:44 pm
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
June 27, 2012 at 4:29 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2012 at 4:46 am
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply