Is there any solution for reducing execution time of stored procedure

Question

Is there any solution for reducing execution time of stored procedure

tkshiv20 42687

Valued Member

Points: 61
More actions
August 20, 2010 at 6:35 am

#225044

Hi below is my sql stored proc which takes a hell lot of time for execution of almost 30min. Is there any solution to reduce this execution time.
Thanks
Shiv
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[qlty_PC_FailureAnalysis]
@FrmDate varchar(20),
@ToDate varchar(20),
@product varchar(30),
@warranty varchar(30)
AS
Begin
set dateformat dmy;
declare @dateDiff int,
@FailName varchar(50),
@FailMinId int,
@FailMaxId int,
@mon int,
@year int,
@Tmon int,
@TYear int,
@i int
set @mon = month(@FrmDate);
set @year = year(@FrmDate);
set @Tmon = month(@ToDate);
set @TYear = year(@ToDate);
set @dateDiff = datediff(m,@FrmDate,@ToDate)
set @i = 0;
delete from tbl_Failure_Insert;
delete from tbl_Pc_Failure;
Insert into tbl_Failure_Insert(Failure_Name) select Distinct Str_FaultAnalysis1 from tbl_SparesDefectiveUpload
select @FailMinId = min(Fail_Id) from tbl_Failure_Insert;
select @FailMaxId = max(Fail_Id) from tbl_Failure_Insert;
while(@FailMinId < = @FailMaxId)
begin
select @FailName = Failure_Name from tbl_Failure_Insert where Fail_Id = @FailMinId;
while(@i <= @dateDiff)
begin
if(@year <= @TYear)
if(@mon != 13)
begin
Set DateFormat dmy;
Insert into tbl_Pc_Failure(PcFailCount,PcFail,PcMonth,PcYear) select Count(distinct Str_RMANo) as PcFailCount, Str_FaultAnalysis1 as PcFail,@mon as PcMonth,@year as PcYear
from tbl_SparesDefectiveUpload
where Str_ModelDescription in (Select Distinct Str_MatchedModel from tbl_MatchModelMaster where Str_OriginalModel=@Product)
and month(date_confirmationDate) = @mon and year(date_confirmationDate) = @year
and Str_RequestItem<>'NP_LABOUR' and Str_FaultAnalysis1= @FailName
and Str_WarrantyStatus=@warranty group by Str_FaultAnalysis1 order by PcMonth,PcYear asc
set @mon = @mon + 1;
set @i = @i + 1;
end
else
begin
set @mon = 1;
set @year = @year+1;
end
end
set @mon = month(@FrmDate);
set @year = year(@FrmDate);
set @i = 1;
set @FailName = '';
set @FailMinId = @FailMinId + 1;
end
select PcFailCount,PcFail,PcMonth,PcYear from tbl_Pc_Failure order by PcMonth,PcYear;
end

Viewing 4 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

There is definitely some room for improvement.

To test the sample code I would like to have table def (DDL script) including index scipt(s) for the tables involved and some ready to use sample data as described in the first link in my signature. I also would like to see the expected result based on the sample data.

Furthermore, please provide a sample how you''re actualling calling your sproc, especially with reference to your @FrmDate and @ToDate parameter (it's strange to define it as varchar and relying on implicit date conversion at the same time...).

I guess the improvement you'll see will be "significant" 😉

Finally, please describe how you're calling that sproc. Is it called by an app with the parameter as described or is it called from another loop? If the ladder, there might be even more room for improvement... But let's take one step at a time.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 2

I agree with Lutz... we need the Table DDL and sample data (in the form on insert statements) to see what's going on. Just be sure that you sanitize the data you provide.

I see two "while" loops going on... this is most likely the source of your performance problem. They can be as bad as (and frequently worse than) using a cursor.

I also see you using functions on columns in the where clause. Even if there were an index on this field, it couldn't be used. You might want to try replacing that logic with something like:

declare @StartDate datetime;

-- get just the date portion of the date passed in, set to the first of the month.

set @StartDate = DateAdd(month, DateDiff(month, 0, convert(datetime, @FrmDate)),0);

-- get rid of the year/month calcs, and use DateAdd: (no need to check for month=13 anymore!)

set @StartDate = DateAdd(month, 1, @StartDate);

-- in the where clause:

where date_confirmationDate >= @StartDate

and date_confirmationDate < DateAdd(month, 1, @StartDate)

Again, to help you further, we need the Table definition (CREATE TABLE, CREATE INDEX) and insert statements.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

Looks to me like all the crazy looping is date arithmetic. Try this as a replacement for the entire stored procedure - don't forget to put appropriate values into the four variables:

DECLARE

@FrmDate DATETIME,

@ToDate DATETIME,

@product varchar(30),

@warranty varchar(30)

SELECT

@FrmDate = GETDATE()-72,

@ToDate = GETDATE(),

@product = '',

@warranty = ''

SELECT PcFailCount = COUNT(DISTINCT Str_RMANo),

PcFail = Str_FaultAnalysis1,

PcMonth = month(date_confirmationDate),

PcYear = year(date_confirmationDate)

from tbl_SparesDefectiveUpload

where Str_ModelDescription in (

Select Distinct Str_MatchedModel

from tbl_MatchModelMaster

where Str_OriginalModel = @product)

and date_confirmationDate > DATEADD(mm,DATEdiff(mm,0,getdate()),-1)

and date_confirmationDate < DATEADD(mm,DATEdiff(mm,0,getdate())+1,0)

and Str_RequestItem <> 'NP_LABOUR'

and Str_FaultAnalysis1 = @FailName

and Str_WarrantyStatus = @warranty

GROUP BY month(date_confirmationDate),

year(date_confirmationDate),

Str_FaultAnalysis1

ORDER BY month(date_confirmationDate),

year(date_confirmationDate)

^{“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