November 15, 2007 at 2:05 am
Hi all,
Can someone help me in suggesting how to improve the performance of an SP.
I have an SP which takes the level Id and parameter Id as input parameters and give out an output parameter which is of type float.
I have 150 levels and 30 parameters which gives me around 150*30=4500 records. Now for these 4500 combinations i need to get the output parameter which i have to store in some other table. So, here I am using a while loop for 4500 records and getting the output value and inserting into the table. This is executing for around 1 hour:(
Can you please suggest me if there is any better way??
Thanks in advance....
November 15, 2007 at 3:19 am
Some of my thoughts below - Hope it helps
1. Using profiler findout the time taken for each of the step
Getting Levels
Getting Parameters
Calculating output
Inserting to other table
You get an Idea about where most of the time is consumed
2. Check the time taken by Stored procedure that cacuates the output value
Since this is called in a loop, this could be taking most of the time
for example if takes about 500 ms, it could easily go up to 35 to 40 minutes as this is called 4600 times.
Tune this stored procedure. Any small improvement here will give you ~4600 times total gain .
3. Once the above steps are completed, the question about whether looping is correct or not is difficult to answer without looking at the code
See whether the following are possible
a) Change the procedure that calculates the output value to a function
Insert the level and parameter first into third table (can this be done in one sql rather than 4600 insert statements?),
and call one update statement to update the output using the function above
b) or even better
insert into output table (level, parameter, output)
Select level, parameter, dbo.GetOutput(level,parameter)
from LevelTable l
cross join parametertable p
November 15, 2007 at 6:09 am
Post your SPs code and we may be able to help more.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 15, 2007 at 11:49 pm
yes..... surely..... but i didnt do that because the SP is quite big 🙂
ALTER PROCEDURE [dbo].[usp_FormulaBuilder_Levels] (@Month int, @year int) AS
SET NOCOUNT on
declare @MonthStr table(id int,mnt varchar(200))
insert into @MonthStr select 1,'jan'
union select 2,'feb'
union select 3,'mar'
union select 4,'apr'
union select 5,'may'
union select 6,'jun'
union select 7,'jul'
union select 8,'aug'
union select 9,'sep'
union select 10,'oct'
union select 11,'nov'
union select 12,'dec'
Declare @Qnum int
if @Month in (1,2,3)
set @Qnum=1
else if @Month in (4,5,6)
set @Qnum=2
else if @Month in (7,8,9)
set @Qnum=3
else if @Month in (10,11,12)
set @Qnum=4
Declare @ParamLocDetails table
(
Time_id int,
Parameter_id int,
Param_Formula Varchar(500),
Location_id int,
Period Varchar(100)--,
--repeat_pattern Varchar(100),
)
Declare @ParamDetails table
(
Pri_key int identity(1,1),
Time_id int,
Parameter_id int,
Param_Formula Varchar(500),
LEVEL_id int,
Period Varchar(100)--,
--repeat_pattern Varchar(100),
primary key (Pri_key)
)
declare @facDATA table
(
time_id int,
parameter_id int,
lEVEL_id int,
mth_val decimal(25,3),
ytd_val decimal(25,3),
goal decimal(25,3),
updated_date datetime,
inserted_date datetime,
primary key(time_id, parameter_id,lEVEL_id)
)
Insert into @ParamLocDetails (Time_id,Parameter_id,Param_Formula,Location_id,Period)
SELECT DISTINCT C.TIME_ID,
A.PARAMETER_ID,
B.PARAM_FORMULA,
A.LOCATION_ID,
A.PERIOD
FROM MyDB.DBO.PM_GO_PROGRAM_PARAM_VALUES A
RIGHT OUTER JOIN DIM_TIME_YYMM C
ON RIGHT(A.PERIOD,4) = C.THE_YEAR
INNER JOIN MyDB.DBO.PM_GO_PARAMETERS B
ON A.PARAMETER_ID = B.PARAMETER_ID
WHERE C.THE_YEAR = YEAR(GETDATE()) - @year
AND B.PARAMETER_TYPE = 'DerivativeFromOtherKPI'
AND A.STATUS = 'active'
AND B.STATUS = 'active'
--and
AND ((LEFT(A.PERIOD,3) = LEFT(C.THE_MONTH,3)
AND C.MONTH_OF_YEAR = @month)
OR (LEFT(A.PERIOD,2) = C.QUARTER
AND C.THE_MONTH = 'Quarter' + CAST(@Qnum AS VARCHAR(10))))
AND A.LOCATION_ID <> 0
ORDER BY 1,2,4
Insert into @ParamDetails (Time_id,Parameter_id,Param_Formula,Period,Level_id)
SELECT DISTINCT A.TIME_ID,
A.PARAMETER_ID,
A.Param_Formula,
A.PERIOD,
B.LEVEL_ID
FROM @ParamLocDetails A
INNER JOIN DIM_LEVELSOFLOCATION B
ON A.LOCATION_ID = B.LOCATION_ID
ORDER BY 1,2,5
DELETE a FROM FAC_DB_LEVELS_DATA a join @ParamDetails b on a.TIME_ID=b.Time_ID AND a.PARAMETER_ID=b.Parameter_Id AND
a.LEVEL_ID=b.LEVEL_ID
Declare @i int
set @i=1
Declare @MaxLoop int
SELECT @MaxLoop=COUNT(*) FROM @ParamDetails
WHILE @i<=@MaxLoop
BEGIN
DECLARE @Time_ID INT
DECLARE @Parameter_Id INT
DECLARE @Param_Formula VARCHAR(500)
DECLARE @LEVEL_ID int
DECLARE @Period VARCHAR(100)
Declare @value Decimal(25,3)
Declare @YTDValue Decimal(25,3)
Declare @YTDPeriods varchar(500)
Declare @loopvar int
Declare @QrtrPeriod varchar(500)
Declare @MthPeriods varchar(500)
Declare @strYear varchar(10)
set @strYear=cast(year(getdate())-@year as varchar(10))
select @Time_ID=Time_ID, @Parameter_Id=Parameter_Id, @Param_Formula=Param_Formula,@LEVEL_ID=LEVEL_ID,
@Period=Period FROM @ParamDetails WHERE Pri_key=@i
set @MthPeriods=@Period
if left(@Period,1)='Q'
begin
set @QrtrPeriod=''
select @QrtrPeriod='Q'+cast(@Qnum as varchar(10))+'-'+@strYear
if @Qnum=1
set @QrtrPeriod=@QrtrPeriod+',Jan-'+@strYear+',Feb-'+@strYear+',Mar-'+@strYear
if @Qnum=2
set @QrtrPeriod=@QrtrPeriod+',Apr-'+@strYear+',May-'+@strYear+',Jun-'+@strYear
if @Qnum=3
set @QrtrPeriod=@QrtrPeriod+',Jul-'+@strYear+',Aug-'+@strYear+',Sep-'+@strYear
if @Qnum=4
set @QrtrPeriod=@QrtrPeriod+',Oct-'+@strYear+',Nov-'+@strYear+',Dec-'+@strYear
set @MthPeriods=@QrtrPeriod
end
if left(@Period,1)<>'Q' and len(@Period)>4
begin
set @loopvar=1
set @YTDPeriods=''
while @loopvar<=@month
begin
if @YTDPeriods=''
set @YTDPeriods=Left(DATENAME(MONTH, cast(@loopvar as varchar)+'/1/2000') ,3)+'-'+cast(year(getdate())-@year as varchar(10))
else
set @YTDPeriods=@YTDPeriods+','+Left(DATENAME(MONTH, cast(@loopvar as varchar)+'/1/2000') ,3)+'-'+@strYear
set @loopvar=@loopvar+1
end
end
else if left(@Period,1)='Q'
begin
if @month >= 1
set @YTDPeriods='Q1-'+@strYear+',Jan-'+@strYear+',Feb-'+@strYear+',Mar-'+@strYear
if @month >= 4
set @YTDPeriods=@YTDPeriods+',Q2-'+@strYear+',Apr-'+@strYear+',May-'+@strYear+'Jun-'+@strYear
if @month >= 7
set @YTDPeriods=@YTDPeriods+',Q3-'+@strYear+',Jul-'+@strYear+',Aug-'+@strYear+'Sep-'+@strYear
if @month >= 10
set @YTDPeriods=@YTDPeriods+',Q4-'+@strYear+',Oct-'+@strYear+',Nov-'+@strYear+'Dec-'+@strYear
end
Exec MyDB.dbo.[usp_Ehs_Evaluate_Formula_WithYTD] @Param_Formula,@MthPeriods,@YTDPeriods,'',@LEVEL_ID,@Value output, @YTDValue output
INSERT INTO @facDATA (time_id,parameter_id,level_id,mth_val,ytd_val,goal,updated_date,inserted_date)
values (@Time_ID,@Parameter_Id,@Level_ID, @value,@YTDValue,0,getdate(),getdate())
SET @i=@i+1
END
INSERT INTO FAC_DB_LEVELS_DATA (time_id,parameter_id,level_id,mth_val,ytd_val,goal,updated_date,inserted_date)
select * from @facDATA
SET NOCOUNT off
This is the SP
November 15, 2007 at 11:52 pm
As Rajesh suggested me I have tried by removing the insert statement in the while loop and using update statement instead of that...... but it is taking more time than what it used to take with insert statement. 🙁
November 16, 2007 at 1:40 am
🙁
1. Did you put the trace and find out the time taken by the queries in
side the stored procedure. I looked at the SP, see whether you can give the execution time for following steps
a) time taken for Inserting into @ParamLocDetails
b) time taken by each call to usp_Ehs_Evaluate_Formula_WithYTD
c) total time taken for one loop
2) can you post the code of usp_Ehs_Evaluate_Formula_WithYTD?
3) The @ParamLocDetails table filling sql contains join clause as RIGHT(A.PERIOD,4) = C.THE_YEAR. These type of expressions might force a table scan. If the usp_Ehs_Evaluate_Formula_WithYTD also has similar scripts then these may be slowing the process.
November 16, 2007 at 2:42 am
the code the the SP usp_Ehs_Evaluate_Formula_WithYTD is as follows:
ALTER Procedure [dbo].[usp_Ehs_Evaluate_Formula_WithYTD]
(
@Formula Varchar(2000),
@Period Varchar(500),
@YTDPeriod Varchar(500),
@LocId Varchar(10),
@LevId int,
@val Varchar(2000) Output,
@YTDVal Varchar(2000) Output
)
As
Begin
SET NOCOUNT ON
Declare @ParamId Varchar(10)
Declare @ParamStartPosition Int
Declare @ParamEndPosition Int
Declare @ParamValue Varchar(50)
Declare @YTDParamValue Varchar(50)
Declare @err Int
Declare @YTDFormula Varchar(2000)
set @YTDFormula = @Formula
Set @ParamId = ''
Set @ParamStartPosition = 0
Set @ParamEndPosition = 0
declare @Temp Table
(
Val decimal(25,3)
)
declare @YTDTemp table
(
YTDVal decimal(25,3)
)
declare @tabLoc table
(
locID int primary key
)
declare @tabPeriod table
(
periodID Varchar(500) primary key
)
declare @tabYTDPeriod table
(
ytdPeriodID Varchar(500) primary key
)
insert into @tabLoc select items from dbo.split(@LocId,',')
insert into @tabPeriod select items from dbo.split(@Period,',')
insert into @tabYTDPeriod select items from dbo.split(@YTDPeriod,',')
while CharIndex('[',@Formula)<>0
Begin
Set @ParamStartPosition = CharIndex('[',@Formula)+1
Set @ParamEndPosition = CharIndex(']',@Formula)
Set @ParamId = Substring(@Formula,@ParamStartPosition,@ParamEndPosition-@ParamStartPosition)
set @ParamValue=''
set @YTDParamValue=''
if @LocId<>''
begin
Select @ParamValue=isnull(cast(Sum(cast(Corp_Conv_Value as decimal(25,3))) as varchar(50)),0) From
PM_GO_PROGRAM_PARAM_VALUES a,@tabLoc b,@tabPeriod c Where a.Parameter_id=@ParamId And a.Period =c.periodID And
a.Location_Id =b.locID And a.Status='Active'
Select @YTDParamValue=isnull(cast(Sum(cast(Corp_Conv_Value as decimal(25,3))) as varchar(50)),0) From
PM_GO_PROGRAM_PARAM_VALUES a,@tabLoc b,@tabYTDPeriod c Where a.Parameter_id=@ParamId And a.Period =c.ytdperiodID And
a.Location_Id =b.locID And a.Status='Active'
end
else
begin
if @ParamId='47'
begin
Select @ParamValue=isnull(cast(Sum(cast(Corp_Conv_Value as decimal(25,3))) as varchar(50)),0) From
PM_GO_PROGRAM_PARAM_VALUES a, @tabPeriod b Where a.Parameter_id=@ParamId And a.Period =b.periodID And
Level_Id=@LevId And Status='Active'
Select @YTDParamValue=isnull(cast(Sum(cast(Corp_Conv_Value as decimal(25,3))) as varchar(50)),0) From
PM_GO_PROGRAM_PARAM_VALUES a, @tabYTDPeriod b Where a.Parameter_id=@ParamId And a.Period =b.ytdperiodID And
Level_Id=@LevId And Status='Active'
end
if (@ParamId<>'47' or @ParamValue='0' or @ParamValue='' )
begin
Select @ParamValue=isnull(cast(Sum(cast(Corp_Conv_Value as decimal(25,3))) as varchar(50)),0) From
PM_GO_PROGRAM_PARAM_VALUES a, @tabPeriod b, DB_datamart.dbo.dim_levelsoflocation c Where Parameter_id=@ParamId And Period =b.periodID And
a.Location_Id = c.location_id and c.level_id=@LevId And a.Status='Active'
Select @YTDParamValue=isnull(cast(Sum(cast(Corp_Conv_Value as decimal(25,3))) as varchar(50)),0) From
PM_GO_PROGRAM_PARAM_VALUES a, @tabYTDPeriod b, DB_datamart.dbo.dim_levelsoflocation c Where Parameter_id=@ParamId And Period =b.ytdperiodID And
a.Location_Id = c.location_id and c.level_id=@LevId And a.Status='Active'
end
end
set @ParamValue='('+@ParamValue+'*1.0)'
set @YTDParamValue='('+@YTDParamValue+'*1.0)'
Set @val = Replace(@Formula,'['+@ParamId+']',@ParamValue)
set @Formula = @val
Set @YTDVal = Replace(@YTDFormula,'['+@ParamId+']',@YTDParamValue)
set @YTDFormula = @YTDVal
End
begin try
Insert Into @Temp(Val) Exec ('Select cast(('+@Val+') as decimal(25,3))')
end try
begin catch
Insert Into @Temp(Val) select 0
end catch
begin try
Insert Into @YTDTemp(YTDVal) Exec ('Select cast(('+@YTDVal+') as decimal(25,3))')
end try
begin catch
Insert Into @YTDTemp(YTDVal) select 0
end catch
Select @val=Val From @Temp
Select @YTDVal=YTDVal From @YTDTemp
SET NOCOUNT OFF
end
November 16, 2007 at 2:45 am
In the above SP the following statement is found to be time consuming.... i can say it is taking 17 ms everytime it is being executed...
Select @YTDParamValue=isnull(cast(Sum(cast(Corp_Conv_Value as decimal(25,3))) as varchar(50)),0) From PM_GO_PROGRAM_PARAM_VALUES a,
@tabYTDPeriod b Where a.Parameter_id=@ParamId And a.Period =b.ytdperiodID And
Level_Id=@LevId And Status='Active'
November 16, 2007 at 7:23 am
There's not much you can do to "tune" this proc... it needs to be written to use proper set-based code instead of the RBAR-on-Steriods that you're using in both this code and the sproc it calls near the end. AND, you need to really figure out what you really want to do so you don't end up with things like the following in the code...
declare @MonthStr table(id int,mnt varchar(200))
insert into @MonthStr select 1,'jan'
union select 2,'feb'
union select 3,'mar'
union select 4,'apr'
union select 5,'may'
union select 6,'jun'
union select 7,'jul'
union select 8,'aug'
union select 9,'sep'
union select 10,'oct'
union select 11,'nov'
union select 12,'dec'
What's wrong with that? I'll tell you... @MonthStr isn't used anywhere in the code. How many other pieces of code do you have in the sproc that will never be used?
Like I said, the only way to tune this poor puppy is to rewrite it using set based code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply