set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[qlty_NPC_FailureAnalysis]
@FrmDate varchar(20),
@ToDate varchar(20),
@Product varchar(30),
@warranty varchar(30)
AS
Begin
SET NOCOUNT ON
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_NPC_Failure_Insert;
delete from tbl_NPc_Failure;
Insert into tbl_NPC_Failure_Insert(Failure_Name) select Distinct Str_FaultAnalysis1 from tbl_SparesDefectiveUpload
select @FailMinId = min(Fail_Id) from tbl_NPC_Failure_Insert;
select @FailMaxId = max(Fail_Id) from tbl_NPC_Failure_Insert;
while(@FailMinId < = @FailMaxId)
begin
select @FailName = Failure_Name from tbl_NPC_Failure_Insert where tbl_NPC_Failure_Insert.Fail_Id = @FailMinId;
while(@i <= @dateDiff)
begin
if(@year <= @TYear)
if(@mon != 13)
begin
Set DateFormat dmy;
Insert into tbl_NPc_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 s
where s.Str_ModelDescription in (Select Distinct Str_MatchedModel from tbl_MatchModelMaster where Str_OriginalModel=@Product)
and month(s.date_confirmationDate) = @mon and year(s.date_confirmationDate) = @year
and s.Str_RequestItem = 'NP_LABOUR' and s.Str_FaultAnalysis1= @FailName
and s.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 * from tbl_NPc_Failure;
end
This stored procedure is taking a hell lot of time to execute, almost 30 min, Please guide me if any changes so that it executes in reasonable amount of time
Thanks
Shiv