谁来帮我看看,这个存储过程怎么优化???

[mrhu7002]

原问题来自http://bbs.2zhichan.cn/thread-1162-1-1.html

这是我写的一个存储过程,现在的执行时间为16秒,老板说还要快点!哪位大哥有办法???帮帮我!!!

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
ALTER         procedure rep_daqjcrb
--漳村煤矿安全监控日报表CH4%
(
@kjid int,
@year int,
@month int,
@day int 
)
as 
SET NOCOUNT ON 
create table #temp_daqjcrb(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,tptid nvarchar(100),t1 nvarchar(100),t2 real,t3 real,t4 nvarchar(100),t5 int,t6 nvarchar(100),t7 int,t8  nvarchar(100),t9 int,tptid1 nvarchar(100),t10 nvarchar(100),t11 real,t12 real,t13 nvarchar(100),t14 int,t15 nvarchar(100),t16 int,t17  nvarchar(100),t18 int/*,t19 nvarchar(100),t20 real,t21 real,t22 nvarchar(100),t23 nvarchar(100),t24 nvarchar(100),t25  nvarchar(100),t26 int,t27  nvarchar(100),t28  nvarchar(100)*/)
create table #temp_row(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,ptplace nvarchar(100),ptdata real,pjdata real,ptid nvarchar(100),ptprop int);
create table #diftimesum(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
create table #diftimesumdd(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
declare @timestrat smalldatetime,@timeend smalldatetime;
declare @ptplace nvarchar(100),@ptplace1 nvarchar(100),@ptplace2 nvarchar(100), @ptdata real,@pjdata real,@pttime nvarchar(100),@ddptstate int,@ddtime nvarchar(100), @bjptstate int,@bjtime int;
declare @isHead int,@bjt nvarchar(10),@bjt1 int;
declare @ptid  nvarchar(100),@ptprop int;
declare @trow int,@tnr int ,@controlid int,@difrow int,@difw int;
--处理日期
set @timestrat=cast(cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@day as varchar) as smalldatetime);
set @timeend=DATEADD(day,+1,@timestrat);
set @isHead = 0;
set @bjt='';
set @bjt1='';
insert into #temp_row select ptplace ,max(ptdata) ptdata,avg(pjdata) pjdata,ptid,ptprop from KJ95daymax where kjid=@kjid and jctime>=@timestrat and jctime<=@timeend and ptprop='1'  group by ptplace ,ptid,ptprop order by ptid,Ptplace 
insert into #diftimesum select diftime,ptid from kj95cxdt where ptstate=1 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1';
insert into #diftimesumdd select diftime,ptid from kj95cxdt where ptstate=3 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1';
select @trow=count(*) from #temp_row;
set @tnr=1;
------CH4%-------------------------------------------------------------------------------------------------------------------------------------------------------------------
while(@tnr<=@trow)
begin
  select @ptplace=ptplace,@ptdata=ptdata,@pjdata=pjdata,@ptid=ptid from #temp_row where prow=@tnr;
  if @isHead=0 
  begin
   insert into #temp_daqjcrb(t1,t2,t3,tptid) values(@ptplace,@ptdata,@pjdata,@ptid);
   select @controlid=IDENT_CURRENT('#temp_daqjcrb');
   set @isHead=1;
  end
  else 
  begin 
   update #temp_daqjcrb set t10=@ptplace,t11=@ptdata,t12=@pjdata,tptid1=@ptid where prow=@controlid;
   set @isHead=0;
  end
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----最大值出现时间
  if @isHead=1
   begin
    select top 1 @ptplace1=ptid, @pttime=CONVERT(nvarchar(10),jctime,108)  
    from KJ95daymax 
    where kjid=@kjid and  jctime>=@timestrat and jctime<=@timeend and ptprop='1' and ptid=@ptid 
    and ptdata= (select top 1 t2 from #temp_daqjcrb where tptid=@ptid) order by jctime;
    update #temp_daqjcrb set t4=@pttime where prow=@controlid and tptid=@ptplace1
   end
  else
   begin
    select top 1 @ptplace1=ptid, @pttime=CONVERT(nvarchar(10),jctime,108)  
    from KJ95daymax  
    where kjid=@kjid and  jctime>=@timestrat and jctime<=@timeend and ptprop='1' and ptid=@ptid 
    and ptdata= (select top 1 t11 from #temp_daqjcrb where tptid1=@ptid) order by jctime;
    update #temp_daqjcrb set t13=@pttime where prow=@controlid and tptid1=@ptplace1
   end
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------报警次数
  select Distinct @ddptstate=count(ptstate) ,@ptplace2=ptid  
  from kj95cxdt 
  where ptstate=1 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1' and ptid=@ptid 
  group by  ptid
  if @isHead=1
   begin
    update #temp_daqjcrb set t5=@ddptstate where prow=@controlid and tptid=@ptplace2
   end
  else
   begin   
    update #temp_daqjcrb set t14=@ddptstate where prow=@controlid and tptid1=@ptplace2
   end 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------报警时间#diftimesum
create table #diftime(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
insert into #diftime select dbo.XC(diftime) ,ptid from #diftimesum where ptid=@ptid;
set @difw=1;
set @bjt1=0;
set @bjt=null;
set @bjtime=0;
select @difrow=count(*) from #diftime;
while(@difw<=@difrow)
  begin
   select @bjtime=diftime from #diftime where  ptid=@ptid and prow=@difw;
   set @bjt1=@bjt1+@bjtime ;
   set @bjt= dbo.f_SecondsToHourMinuteSecond(@bjt1);
   if  @isHead=1
    begin
     update #temp_daqjcrb set t6=@bjt where prow=@controlid and tptid=@ptid
    end
   else
    begin
     update #temp_daqjcrb set t15=@bjt where prow=@controlid and tptid1=@ptid
    end
  set @difw=@difw+1;
  end
drop table #diftime;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------断电次数
set @bjptstate=null
select Distinct @bjptstate=count(ptstate) ,@ptplace2=ptid  
  from kj95cxdt 
  where ptstate=3 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1' and ptid=@ptid 
  group by  ptid
  if @isHead=1
   begin
    update #temp_daqjcrb set t7=@bjptstate where prow=@controlid and tptid=@ptid
   end
  else
   begin   
    update #temp_daqjcrb set t16=@bjptstate where prow=@controlid and tptid1=@ptid
   end
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----断电时间 
create table #diftimedd(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
insert into #diftimedd select dbo.XC(diftime) ,ptid from #diftimesumdd where ptid=@ptid;
set @difw=1;
set @bjt1=0;
set @bjt=null;
set @bjtime=0;
set @difrow=0;
select @difrow=count(*) from #diftimedd;
while(@difw<=@difrow)
  begin
   select @bjtime=diftime from #diftimedd where  ptid=@ptid and prow=@difw;
   set @bjt1 =@bjt1+@bjtime ;
   set @bjt=dbo.f_SecondsToHourMinuteSecond(@bjt1);
   if  @isHead=1
    begin
     update #temp_daqjcrb set t8=@bjt where prow=@controlid and tptid=@ptid
    end
   else
    begin
     update #temp_daqjcrb set t17=@bjt where prow=@controlid and tptid1=@ptid
    end
  set @difw=@difw+1;
  end
drop table #diftimedd;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set @tnr=@tnr+1;
end
select * from #temp_daqjcrb
return;
--  rep_daqjcrb 2,2008,1,25
--PRINT @trow;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[liangCK]

够长的.帮顶.

[mrhu7002]

谢谢，各位好心的哥们，帮忙呀`````

[dobear_0922]

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
ALTER         procedure rep_daqjcrb
--漳村煤矿安全监控日报表CH4%
(
@kjid int,
@year int,
@month int,
@day int 
)
as 
SET NOCOUNT ON 
create table #temp_daqjcrb(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,tptid nvarchar(100),t1 nvarchar(100),t2 real,t3 real,t4 nvarchar(100),t5 int,t6 nvarchar(100),t7 int,t8  nvarchar(100),t9 int,tptid1 nvarchar(100),t10 nvarchar(100),t11 real,t12 real,t13 nvarchar(100),t14 int,t15 nvarchar(100),t16 int,t17  nvarchar(100),t18 int/*,t19 nvarchar(100),t20 real,t21 real,t22 nvarchar(100),t23 nvarchar(100),t24 nvarchar(100),t25  nvarchar(100),t26 int,t27  nvarchar(100),t28  nvarchar(100)*/)
create table #temp_row(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,ptplace nvarchar(100),ptdata real,pjdata real,ptid nvarchar(100),ptprop int);
create table #diftimesum(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
create table #diftimesumdd(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
declare @timestrat smalldatetime,@timeend smalldatetime;
declare @ptplace nvarchar(100),@ptplace1 nvarchar(100),@ptplace2 nvarchar(100), @ptdata real,@pjdata real,@pttime nvarchar(100),@ddptstate int,@ddtime nvarchar(100), @bjptstate int,@bjtime int;
declare @isHead int,@bjt nvarchar(10),@bjt1 int;
declare @ptid  nvarchar(100),@ptprop int;
declare @trow int,@tnr int ,@controlid int,@difrow int,@difw int;
--处理日期
set @timestrat=cast(cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@day as varchar) as smalldatetime);
set @timeend=DATEADD(day,+1,@timestrat);
set @isHead = 0;
set @bjt='';
set @bjt1='';
insert into #temp_row select ptplace ,max(ptdata) ptdata,avg(pjdata) pjdata,ptid,ptprop from KJ95daymax where kjid=@kjid and jctime between @timestrat and @timeend and ptprop='1'  group by ptplace ,ptid,ptprop order by ptid,Ptplace 
insert into #diftimesum select diftime,ptid from kj95cxdt where ptstate=1 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1';
insert into #diftimesumdd select diftime,ptid from kj95cxdt where ptstate=3 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1';
select @trow=count(1) from #temp_row;
set @tnr=1;
------CH4%-------------------------------------------------------------------------------------------------------------------------------------------------------------------
while(@tnr<=@trow)
begin
  select @ptplace=ptplace,@ptdata=ptdata,@pjdata=pjdata,@ptid=ptid from #temp_row where prow=@tnr;
  if @isHead=0 
  begin
   insert into #temp_daqjcrb(t1,t2,t3,tptid) values(@ptplace,@ptdata,@pjdata,@ptid);
   select @controlid=IDENT_CURRENT('#temp_daqjcrb');
   set @isHead=1;
  end
  else 
  begin 
   update #temp_daqjcrb set t10=@ptplace,t11=@ptdata,t12=@pjdata,tptid1=@ptid where prow=@controlid;
   set @isHead=0;
  end


----最大值出现时间
--不明白这里为什么用为么多 TOP 1，　子查询中用了TOP，却没有ORDER BY
  if @isHead=1
   begin
    select top 1 @ptplace1=ptid, @pttime=CONVERT(nvarchar(10),jctime,108)  
    from KJ95daymax 
    where kjid=@kjid and  jctime>=@timestrat and jctime<=@timeend and ptprop='1' and ptid=@ptid 
    and ptdata= (select top 1 t2 from #temp_daqjcrb where tptid=@ptid) order by jctime;
    update #temp_daqjcrb set t4=@pttime where prow=@controlid and tptid=@ptplace1
   end
  else
   begin
    select top 1 @ptplace1=ptid, @pttime=CONVERT(nvarchar(10),jctime,108)  
    from KJ95daymax  
    where kjid=@kjid and  jctime>=@timestrat and jctime<=@timeend and ptprop='1' and ptid=@ptid 
    and ptdata= (select top 1 t11 from #temp_daqjcrb where tptid1=@ptid) order by jctime;
    update #temp_daqjcrb set t13=@pttime where prow=@controlid and tptid1=@ptplace1
   end


----------报警次数
  select Distinct @ddptstate=count(ptstate) ,@ptplace2=ptid  
  from kj95cxdt 
  where ptstate=1 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1' and ptid=@ptid 
  group by  ptid
  if @isHead=1
   begin
    update #temp_daqjcrb set t5=@ddptstate where prow=@controlid and tptid=@ptplace2
   end
  else
   begin   
    update #temp_daqjcrb set t14=@ddptstate where prow=@controlid and tptid1=@ptplace2
   end 


-------报警时间#diftimesum
create table #diftime(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
insert into #diftime select dbo.XC(diftime) ,ptid from #diftimesum where ptid=@ptid;
set @difw=1;
set @bjt1=0;
set @bjt=null;
set @bjtime=0;
select @difrow=count(1) from #diftime;
if  @isHead=1
	while(@difw<=@difrow)
	  begin
	   select @bjtime=diftime from #diftime where  ptid=@ptid and prow=@difw;
	   set @bjt1=@bjt1+@bjtime ;
	   set @bjt= dbo.f_SecondsToHourMinuteSecond(@bjt1);
	   update #temp_daqjcrb set t6=@bjt where prow=@controlid and tptid=@ptid	       
	   set @difw=@difw+1;
	  end
else
	while(@difw<=@difrow)
	  begin
	   select @bjtime=diftime from #diftime where  ptid=@ptid and prow=@difw;
	   set @bjt1=@bjt1+@bjtime ;
	   set @bjt= dbo.f_SecondsToHourMinuteSecond(@bjt1);	   
	   update #temp_daqjcrb set t15=@bjt where prow=@controlid and tptid1=@ptid    
	   set @difw=@difw+1;
	  end
drop table #diftime;


-------断电次数
set @bjptstate=null
select Distinct @bjptstate=count(ptstate) ,@ptplace2=ptid  
  from kj95cxdt 
  where ptstate=3 and kjid=@kjid and  begintime>=@timestrat and endtime<=@timeend and ptprop='1' and ptid=@ptid 
  group by  ptid
  if @isHead=1
   begin
    update #temp_daqjcrb set t7=@bjptstate where prow=@controlid and tptid=@ptid
   end
  else
   begin   
    update #temp_daqjcrb set t16=@bjptstate where prow=@controlid and tptid1=@ptid
   end


-----断电时间 
create table #diftimedd(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,diftime nvarchar(10),ptid nvarchar(10));
insert into #diftimedd select dbo.XC(diftime) ,ptid from #diftimesumdd where ptid=@ptid;
set @difw=1;
set @bjt1=0;
set @bjt=null;
set @bjtime=0;
set @difrow=0;
select @difrow=count(1) from #diftimedd;
if  @isHead=1
	while(@difw<=@difrow)
	  begin
	   select @bjtime=diftime from #diftimedd where  ptid=@ptid and prow=@difw;
	   set @bjt1 =@bjt1+@bjtime ;
	   set @bjt=dbo.f_SecondsToHourMinuteSecond(@bjt1);
	   update #temp_daqjcrb set t8=@bjt where prow=@controlid and tptid=@ptid	   
	   set @difw=@difw+1;
	  end
else
	while(@difw<=@difrow)
	  begin
	   select @bjtime=diftime from #diftimedd where  ptid=@ptid and prow=@difw;
	   set @bjt1 =@bjt1+@bjtime ;
	   set @bjt=dbo.f_SecondsToHourMinuteSecond(@bjt1);	   
	   update #temp_daqjcrb set t17=@bjt where prow=@controlid and tptid1=@ptid
	   set @difw=@difw+1;
	  end
drop table #diftimedd;


set @tnr=@tnr+1;
end
select * from #temp_daqjcrb
return;
--  rep_daqjcrb 2,2008,1,25
--PRINT @trow;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[artmouse]

就这样一段代码很难分析，使用循环可能是导致效率不高的原因，看能不能用join 语句加 group 语句代替

[yujiang930]

循环的效率是不高，看看能不用update代替

update t_1
set col_1 = val_1,
col_2 = val_2
where t_1 a
inner join t_2 b on (a.con = b.con)
inner join t_3 c on (a.con = c.con)

[ChenHuaWen]

没有数据结构和业务需求说明.

单纯看语句来优化,对于一些明显示的冗余语句可能可以优化, 但整体效果并不佳. 脱离应用的优化没有意义.

[zefuzhang2008]

先把需要的索引加上，再考虑逻辑是否能简化

[zhuyx808]

帮顶

[newqq]

long long long ...

[Sorder]

使用到的查询字段有没有建立索引,
索引的建立对查询速度有很大关系

[中国风]

把一以代码建临时表，改为生成临时表..
把临时表的条件列建组合索引
------------
判断那一段语句占用的时间，选中所有语句用Ctrl+L