如何减少存储过程执行所需的时间 [英] How to reduce the time taken by stored procedure for execution

问题描述

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

此存储过程要花费大量时间才能执行，将近30分钟，如果有任何更改，请指导我，以便它在合理的时间内执行.

谢谢
Shiv

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

推荐答案

您可以尝试摆脱嵌套查询，作为一个开始.

You could try to get rid of the nested queries, for a start.

• 查看存储的proc的执行计划，看看哪一部分比较慢.
• 添加索引以提高性能

这篇关于如何减少存储过程执行所需的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！