July 21, 2008 at 2:00 pm
Hi experts iam executing one stored procedure from query analyser.
sometimes it is executing very fast but sometimes taking lots of time.
Can anyone tell me if there is any problem with the query:
CREATE Procedure SP_CreateSPR
As
/*Set the date format DD/MM/YYYY*/
Set DateFormat DMY
/*Check Whether valid records exists or not*/
If NOT Exists(Select Top 1 ReportId from SPRTemp)
Begin
Select * from SPRTemp
Goto TheEnd
End
/*Map with ITC Code*/
Update SPRTemp Set WDDest = IsNULL(WDC.ITCWDCode, '') From SPRTemp ST, WDCodeMap WDC Where ST.WDDest = WDC.ForumWDCode
/*Updating WDCode by comparing WDDEST with WDMaster_IN.WDDest*/
Update SPRTemp Set WDCode = IsNULL(WDM.WDCode, '') From SPRTemp ST Left Outer Join WDMaster_IN WDM ON ST.WDDest = WDM.WDDest
/*Inserting SPR records into XI table*/
Insert Into SPR_XI_OUT(ReportID, Row_Id, ToDate, WDCode, WDDest, MarketSKU, OPSOH, Receipts, Sales, Damages, CLSOH)
Select
ST.ReportID,
1 as Row_ID,
Convert(DateTime, (CAST(Min(Month(RP.ParameterValue)) as varchar) + '/' + CAST(Min(Day(RP.ParameterValue)) as varchar) + '/' + CAST(Min(Year(RP.ParameterValue)) as varchar)), 101) AS ToDate,
ST.WDCode,
ST.WDDest,
ST.MarketSKU,
Sum(IsNULL(ST.OPSOH,0)) AS [OPSOH],
(Sum(IsNULL(ST.Receipts,0)) - SUM(IsNULL(TD.TotDed,0))) AS [Receipts],
Sum(IsNULL(ST.Sales,0)) AS Sales,
Sum(IsNULL(ST.Damages,0)) AS Damages,
Sum(IsNULL(ST.CLSOH,0)) AS [CLSOH]/*(Sum(IsNULL(ST.CLSOH,0)) - SUM(IsNULL(TD.TotDed,0))) AS [CLSOH]*/
From SPRTemp AS ST
Join Reports R On ST.ReportID = R.ReportID
Join ReportParameters RP On R.Parameters = RP.ParameterID And RP.ParameterName = 'To_Date'
/* Date Validation is added */
and isdate(RP.Parametervalue) = 1
Left Outer Join (Select
STIn.BranchMapID AS BMapID,
IsNULL(SUM(CAST(IsNULL(SBT.ColumnValue,0) AS Decimal(18,6))),0) AS TotDed
From SPRTemp STIn
Left Outer Join SPRBranchTemp SBT ON STIn.BranchMapID = SBT.Row_ID
Join WDCodeMap WDC On Right(SBT.ColumnName, CASE When LEN(SBT.ColumnName) < 8 Then 0 Else LEN(SBT.ColumnName)- 8 End) = WDC.ForumWDCode
Where Replace(SBT.ColumnName, Right(SBT.ColumnName, CASE When LEN(SBT.ColumnName) < 8 Then 0 Else LEN(SBT.ColumnName)- 8 END), WDC.ITCWDCode) In (Select 'TROUT - ' + WDDest From SPRTemp Where ReportID = STIn.ReportID And WDCode = STIn.WD
Code And WDDest = STIn.WDDest And MarketSKU = STIn.MarketSKU)
Group by STIn.BranchMapID) AS TD On ST.BranchMapID = TD.BMapID
Group By ST.ReportID, ST.WDCode, ST.WDDest, ST.MarketSKU Order By ST.ReportID
/*Inserting SPRBranch Records*/
Insert Into SPRBranch_XI_Out(Row_ID, ColumnName, ColumnValue, Status)
Select
BMID.XIMapID AS Row_ID,
WDC.ITCWDCode,
SUM(CAST(IsNULL(SBT.ColumnValue,0) as Decimal(18,6))),
0 AS Status
From SPRBranchTemp SBT
Join WDCodeMap WDC On Right(SBT.ColumnName, CASE When LEN(SBT.ColumnName) < 8 Then 0 Else LEN(SBT.ColumnName)- 8 End) = WDC.ForumWDCode
Join (Select
SPR_XI.BranchMapID as XIMapID,
ST.BranchMapID as STMapID,
CAST(ST.ReportID AS Varchar) + '_' + ST.WDCode + '_' + ST.WDDest + '_' + ST.MarketSKU as Branches
From SPR_XI_OUT SPR_XI
Join SPRTemp ST On SPR_XI.ReportID = ST.ReportID And SPR_XI.WDCode = ST.WDCode And SPR_XI.WDDest = ST.WDDest And SPR_XI.MarketSKU = ST.MarketSKU
) As BMID On SBT.Row_ID = BMID.STMapID
Where Replace(SBT.ColumnName, Right(SBT.ColumnName, CASE When LEN(SBT.ColumnName) < 8 Then 0 Else LEN(SBT.ColumnName)- 8 End), WDC.ITCWDCode) Not In (Select 'TROUT - ' + WDDest From SPRTemp Where CAST(ReportID AS Varchar) + '_' + WDCode + '_' + WDDest +
'_' + MarketSKU = BMID.Branches)
Group By BMID.XIMapID, WDC.ITCWDCode
Having Sum(CAST(IsNULL(SBT.ColumnValue,0) as Decimal(18,6))) > 0
Order By BMID.XIMapID
/*Updating RowIDs of SPR_XI_OUT table*/
Update AA Set Row_ID = BranchMapID - (Select Min(BB.BranchMapID) - 1 From SPR_XI_OUT BB Where BB.ReportID = AA.ReportID) From SPR_XI_OUT AA Where AA.Status = 0
/*Updating status in SPR as transfered*/
Update SPR Set Status = 1 Where Status = 0 and ReportID In (Select Distinct ReportID From SPR_XI_Out Where Status = 0)
TheEnd:
Truncate Table SPRTemp
Truncate Table SPRBranchTemp
July 21, 2008 at 2:25 pm
Yes, I would expect this to run slowly. I'd start rewriting it from the GoTo statements (get rid of them and use Begin...End for the If), change the Not Exists by reversing the logic and using Exists, then I'd get rid of the inline, correlated sub-queries and replace them with a join or two.
That would be a good start, anyway.
- 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
July 21, 2008 at 2:43 pm
Thanks expert if you can write and post it would be great help and can you tell me what is the difference between then replacing with begin if you you could explain on this part .
Thanks
July 21, 2008 at 2:45 pm
I don't have time to rewrite proc for you. If someone does, they'll need you to provide the table structure and some sample data, and the desired results from the sample data. Go ahead and post that, and someone who has the time to rewrite it for you might happen along.
- 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
July 21, 2008 at 3:37 pm
Hi Experts get rid of them and use Begin...End for the If), change the Not Exists by reversing the logic and using Exists, then I'd get rid of the inline, correlated sub-queries and replace them with a join or two.
Can you please tell me what diffrence will it be making by replacing these if you could explain little bit.
Thanks for your time.
July 22, 2008 at 7:50 am
Hi Sandy, realistically we need to see the underlying tables (And ideally some sample data within them) to help here. Can you post table structures? In Enterprise Manager, right-click the table and select "Script table as > Create > To Clipboard" then paste that into a post here.
As regards removing the Goto, use syntax like:
IF @Variable = Condition
BEGIN
--Do some work
END
ELSE
BEGIN
--Do some other work
END
I would expect you could increase performance if you can change the WHERE clause. As there are queries, CASE statements and text functions in the WHERE clause, the query optimiser will not be able to establish how best to proceed. This means SQL must examine every single row in the table, and any indexes that could have helped will not be used.
Please see this link for how to help us help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
July 24, 2008 at 6:27 am
Please find the required table structure attached.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply