我的存储过程执行需要7秒，但是同样的代码用查询分析器只要2秒。
查看了网上以及论坛的资料，发现以下解决方法：
1.建立索引，我也建立过，就有次执行存储过程3秒，重启电脑以后还是7秒、
2.删除存储过程重建，我也做过
呜呜，怎么办呢？因为速度慢，小女被老大骂哭了！！
代码如下：CREATE PROCEDURE crm_getzjyw
@islj varchar(1),
@qsrq varchar(10),
@zzrq varchar(10),
@fzr varchar(5),
@khs_cx int output,
@ywe_cx money output,
@khs_dx int output,
@ywe_dx money output
asbegin transaction
declare @khfl int,@khs int,@ywe money
set @islj=convert(int,@islj)
set @khs=0
set @ywe=0
set @khfl=3
if @khfl=3
begin
execute crm_getqf @qsrq,@zzrq,@fzr '另一个存储过程计算每个客户的欠费
end
else
begin
if not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp1') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp2') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp3') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute crm_getqf @qsrq,@zzrq,@fzr
end
if @islj=1
begin
if @khfl=3 '代表最老客户，计算去年欠费
begin
execute crm_getqf_forskbywe_lj @qsrq,@zzrq,@fzr
end
else
begin
if not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp11') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp12') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp13') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute crm_getqf_forskbywe_lj @qsrq,@zzrq,@fzr
end
endwhile @khfl>=2
begin
if @islj=1 ’需要计算去年的欠费金额但是今年收款的
begin
set @qsrq=convert(datetime,@qsrq)
set @zzrq=convert(datetime,@zzrq)
select @khs=count(distinct F_id),@ywe=isnull(sum(ywe),0) from (select F_id,sum(ywe) as ywe from (select l.F_id,case when isnull(ye,0)>=0 and
isnull(checkmoney,0)>0 then isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)-abs(isnull(ye,0))>0 then
isnull(checkmoney,0)-abs(isnull(ye,0)) end as ywe from crmtemp3 l inner join (select a.F_id ,isnull(sum(isnull(isnull(F_checkmoney,0)*isnull(zk,1),0)),0) as checkmoney,zzrq
from crm_jc_forhs a,a_CLient_xyqd b,client c where signdate>=(case when c.qsrq>=@qsrq then c.qsrq else @qsrq end)
and signdate<(case when c.zzrq<@zzrq then dateadd(day,1,c.zzrq) else @zzrq end) and c.del=0 and c.fzr=@fzr and khfl=@khfl
and b.del=0 and
left(F_sampleid,1)
not in ('A','G') and F_checktype like '委托%' and isnull(invoiceid,'')='' and datediff(day,a.Signdate,b.F_qdrq)<=0 and F_checkmoney>0 and
datediff(day,a.Signdate,(case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end))>=0
and a.F_id=b.F_id and a.F_id=c.F_id group by a.F_id,c.zzrq) m on l.F_id=m.F_id and l.zzrq=m.zzrq where case when isnull(ye,0)>=0 and isnull(checkmoney,0)>0 then
isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)+isnull(ye,0)>0 then isnull(checkmoney,0)+isnull(ye,0) end >0
union all select F_id,sum(ywe) as ywe from (select case when signdate<>'' then c.F_id else d.F_id end as F_id,
case when signdate<>'' then F_checkmoney else convert(money,F_fee) end as ywe from (select F_sampleid,a.F_id,F_checkmoney,signdate from crm_jc_forhs a,client b where signdate>=(case when b.qsrq>=@qsrq then b.qsrq else @qsrq end)
and signdate<(case when b.zzrq<@zzrq then dateadd(day,1,b.zzrq) else @zzrq end) and b.del=0 and b.fzr=@fzr and khfl =@khfl and b.F_id=a.F_id and not exists(select 1 from a_client_xyqd
where del=0 and F_qdrq>=signdate and case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end<=signdate and F_id=a.F_id) and invoiceid<>''
and left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%' ) c full outer join
(select b.F_id,F_sampleid,F_date,F_fee from a_charge b,client a where F_date>=(case when a.qsrq>=@qsrq then a.qsrq else @qsrq end) and F_date<(case when a.zzrq<@zzrq then dateadd(day,1,a.zzrq) else @zzrq end) and
F_isbj=1 and a.del=0 and
a.fzr=@fzr and khfl =@khfl and a.F_id=b.F_id and not exists(select 1 from a_client_xyqd where del=0 and F_qdrq>=F_date and case when isnull(F_sjwcrq,'')='' and isqy=1
then convert(char(10),getdate(),120) else F_sjwcrq end<=F_date and F_id=b.F_id) and exists(select 1 from crm_jc_forhs where left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%'
and signdate<(case when a.zzrq<@qsrq then dateadd(day,1,a.zzrq) else @qsrq end) and signdate>=a.qsrq and F_sampleid=b.F_sampleid)) d on c.F_sampleid=
d.F_sampleid
union all select l.F_id,case when abs(ye)<=nsf then abs(ye) else nsf
end as ywe from crmtemp13 l inner join (select a.F_id,sum(F_je) as nsf from a_client_ztsf a,client b where b.del=0 and b.fzr=@fzr and khfl =@khfl
and case when sj='-' then convert(datetime,'2008-01-01') else convert(datetime,sj) end>=(case when b.qsrq>=@qsrq then b.qsrq else @qsrq end)
and case when sj='-' then convert(datetime,'2008-01-01') else convert(datetime,sj) end<(case when b.zzrq<@zzrq then dateadd(day,1,b.zzrq) else @zzrq end) and a.del=0 and a.F_id=b.F_id group by a.F_id) m on l.F_id=m.F_id where abs(ye)>0) e group by F_id)
n group by F_id) m
end
else
begin
set @qsrq=convert(datetime,@qsrq)
set @zzrq=convert(datetime,@zzrq)
select @khs=count(distinct F_id),@ywe=isnull(sum(ywe),0) from (select F_id,sum(ywe) as ywe from (select l.F_id,case when isnull(ye,0)>=0 and
isnull(checkmoney,0)>0 then isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)-abs(isnull(ye,0))>0 then
isnull(checkmoney,0)-abs(isnull(ye,0)) end as ywe from crmtemp3 l inner join (select a.F_id ,isnull(sum(isnull(isnull(F_checkmoney,0)*isnull(zk,1),0)),0) as checkmoney,zzrq
from crm_jc_forhs a,a_CLient_xyqd b,client c where signdate>=(case when c.qsrq>=@qsrq then c.qsrq else @qsrq end)
and signdate<(case when c.zzrq<@zzrq then dateadd(day,1,c.zzrq) else @zzrq end) and c.del=0 and c.fzr=@fzr and khfl=@khfl
and b.del=0 and
left(F_sampleid,1)
not in ('A','G') and F_checktype like '委托%' and isnull(invoiceid,'')='' and datediff(day,a.Signdate,b.F_qdrq)<=0 and F_checkmoney>0 and
datediff(day,a.Signdate,(case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end))>=0
and a.F_id=b.F_id and a.F_id=c.F_id group by a.F_id,zzrq) m on l.F_id=m.F_id and l.zzrq=m.zzrq where case when isnull(ye,0)>=0 and isnull(checkmoney,0)>0 then
isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)+isnull(ye,0)>0 then isnull(checkmoney,0)+isnull(ye,0) end >0
union all select F_id,sum(ywe) as ywe from (select case when signdate<>'' then c.F_id else d.F_id end as F_id,
case when signdate<>'' then F_checkmoney else convert(money,F_fee) end as ywe from (select F_sampleid,a.F_id,F_checkmoney,signdate from crm_jc_forhs a,client b where signdate>=(case when b.qsrq>=@qsrq then b.qsrq else @qsrq end)
and signdate<(case when b.zzrq<@zzrq then dateadd(day,1,b.zzrq) else @zzrq end) and b.del=0 and b.fzr=@fzr and khfl =@khfl and b.F_id=a.F_id and not exists(select 1 from a_client_xyqd
where del=0 and F_qdrq>=signdate and case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end<=signdate and F_id=a.F_id) and invoiceid<>''
and left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%' ) c full outer join
(select b.F_id,F_sampleid,F_date,F_fee from a_charge b,client a where F_date>=(case when a.qsrq>=@qsrq then a.qsrq else @qsrq end) and F_date<(case when a.zzrq<@zzrq then dateadd(day,1,a.zzrq) else @zzrq end)
and F_isbj=1 and a.del=0 and
a.fzr=@fzr and khfl =@khfl and a.F_id=b.F_id and not exists(select 1 from a_client_xyqd where del=0 and F_qdrq>=F_date and case when isnull(F_sjwcrq,'')='' and isqy=1
then convert(char(10),getdate(),120) else F_sjwcrq end<=F_date and F_id=b.F_id) and exists(select 1 from crm_jc_forhs where left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%'
and signdate<(case when a.zzrq<@qsrq then dateadd(day,1,a.zzrq) else @qsrq end) and signdate>=a.qsrq and F_sampleid=b.F_sampleid)) d on c.F_sampleid=
d.F_sampleid ) e group by F_id)n group by F_id)m
end
if @khfl=3
begin
set @khs_cx=@khs
set @ywe_cx=@ywe
end
else if @khfl=2
begin
set @khs_dx=@khs
set @ywe_dx=@ywe
end
set @khfl=@khfl-1
end
commitGO
很多，因为算法很复杂，不好意思，希望各位帮帮忙，不想再被骂了
查看了网上以及论坛的资料，发现以下解决方法：
1.建立索引，我也建立过，就有次执行存储过程3秒，重启电脑以后还是7秒、
2.删除存储过程重建，我也做过
呜呜，怎么办呢？因为速度慢，小女被老大骂哭了！！
代码如下：CREATE PROCEDURE crm_getzjyw
@islj varchar(1),
@qsrq varchar(10),
@zzrq varchar(10),
@fzr varchar(5),
@khs_cx int output,
@ywe_cx money output,
@khs_dx int output,
@ywe_dx money output
asbegin transaction
declare @khfl int,@khs int,@ywe money
set @islj=convert(int,@islj)
set @khs=0
set @ywe=0
set @khfl=3
if @khfl=3
begin
execute crm_getqf @qsrq,@zzrq,@fzr '另一个存储过程计算每个客户的欠费
end
else
begin
if not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp1') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp2') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp3') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute crm_getqf @qsrq,@zzrq,@fzr
end
if @islj=1
begin
if @khfl=3 '代表最老客户，计算去年欠费
begin
execute crm_getqf_forskbywe_lj @qsrq,@zzrq,@fzr
end
else
begin
if not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp11') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp12') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp13') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute crm_getqf_forskbywe_lj @qsrq,@zzrq,@fzr
end
endwhile @khfl>=2
begin
if @islj=1 ’需要计算去年的欠费金额但是今年收款的
begin
set @qsrq=convert(datetime,@qsrq)
set @zzrq=convert(datetime,@zzrq)
select @khs=count(distinct F_id),@ywe=isnull(sum(ywe),0) from (select F_id,sum(ywe) as ywe from (select l.F_id,case when isnull(ye,0)>=0 and
isnull(checkmoney,0)>0 then isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)-abs(isnull(ye,0))>0 then
isnull(checkmoney,0)-abs(isnull(ye,0)) end as ywe from crmtemp3 l inner join (select a.F_id ,isnull(sum(isnull(isnull(F_checkmoney,0)*isnull(zk,1),0)),0) as checkmoney,zzrq
from crm_jc_forhs a,a_CLient_xyqd b,client c where signdate>=(case when c.qsrq>=@qsrq then c.qsrq else @qsrq end)
and signdate<(case when c.zzrq<@zzrq then dateadd(day,1,c.zzrq) else @zzrq end) and c.del=0 and c.fzr=@fzr and khfl=@khfl
and b.del=0 and
left(F_sampleid,1)
not in ('A','G') and F_checktype like '委托%' and isnull(invoiceid,'')='' and datediff(day,a.Signdate,b.F_qdrq)<=0 and F_checkmoney>0 and
datediff(day,a.Signdate,(case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end))>=0
and a.F_id=b.F_id and a.F_id=c.F_id group by a.F_id,c.zzrq) m on l.F_id=m.F_id and l.zzrq=m.zzrq where case when isnull(ye,0)>=0 and isnull(checkmoney,0)>0 then
isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)+isnull(ye,0)>0 then isnull(checkmoney,0)+isnull(ye,0) end >0
union all select F_id,sum(ywe) as ywe from (select case when signdate<>'' then c.F_id else d.F_id end as F_id,
case when signdate<>'' then F_checkmoney else convert(money,F_fee) end as ywe from (select F_sampleid,a.F_id,F_checkmoney,signdate from crm_jc_forhs a,client b where signdate>=(case when b.qsrq>=@qsrq then b.qsrq else @qsrq end)
and signdate<(case when b.zzrq<@zzrq then dateadd(day,1,b.zzrq) else @zzrq end) and b.del=0 and b.fzr=@fzr and khfl =@khfl and b.F_id=a.F_id and not exists(select 1 from a_client_xyqd
where del=0 and F_qdrq>=signdate and case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end<=signdate and F_id=a.F_id) and invoiceid<>''
and left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%' ) c full outer join
(select b.F_id,F_sampleid,F_date,F_fee from a_charge b,client a where F_date>=(case when a.qsrq>=@qsrq then a.qsrq else @qsrq end) and F_date<(case when a.zzrq<@zzrq then dateadd(day,1,a.zzrq) else @zzrq end) and
F_isbj=1 and a.del=0 and
a.fzr=@fzr and khfl =@khfl and a.F_id=b.F_id and not exists(select 1 from a_client_xyqd where del=0 and F_qdrq>=F_date and case when isnull(F_sjwcrq,'')='' and isqy=1
then convert(char(10),getdate(),120) else F_sjwcrq end<=F_date and F_id=b.F_id) and exists(select 1 from crm_jc_forhs where left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%'
and signdate<(case when a.zzrq<@qsrq then dateadd(day,1,a.zzrq) else @qsrq end) and signdate>=a.qsrq and F_sampleid=b.F_sampleid)) d on c.F_sampleid=
d.F_sampleid
union all select l.F_id,case when abs(ye)<=nsf then abs(ye) else nsf
end as ywe from crmtemp13 l inner join (select a.F_id,sum(F_je) as nsf from a_client_ztsf a,client b where b.del=0 and b.fzr=@fzr and khfl =@khfl
and case when sj='-' then convert(datetime,'2008-01-01') else convert(datetime,sj) end>=(case when b.qsrq>=@qsrq then b.qsrq else @qsrq end)
and case when sj='-' then convert(datetime,'2008-01-01') else convert(datetime,sj) end<(case when b.zzrq<@zzrq then dateadd(day,1,b.zzrq) else @zzrq end) and a.del=0 and a.F_id=b.F_id group by a.F_id) m on l.F_id=m.F_id where abs(ye)>0) e group by F_id)
n group by F_id) m
end
else
begin
set @qsrq=convert(datetime,@qsrq)
set @zzrq=convert(datetime,@zzrq)
select @khs=count(distinct F_id),@ywe=isnull(sum(ywe),0) from (select F_id,sum(ywe) as ywe from (select l.F_id,case when isnull(ye,0)>=0 and
isnull(checkmoney,0)>0 then isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)-abs(isnull(ye,0))>0 then
isnull(checkmoney,0)-abs(isnull(ye,0)) end as ywe from crmtemp3 l inner join (select a.F_id ,isnull(sum(isnull(isnull(F_checkmoney,0)*isnull(zk,1),0)),0) as checkmoney,zzrq
from crm_jc_forhs a,a_CLient_xyqd b,client c where signdate>=(case when c.qsrq>=@qsrq then c.qsrq else @qsrq end)
and signdate<(case when c.zzrq<@zzrq then dateadd(day,1,c.zzrq) else @zzrq end) and c.del=0 and c.fzr=@fzr and khfl=@khfl
and b.del=0 and
left(F_sampleid,1)
not in ('A','G') and F_checktype like '委托%' and isnull(invoiceid,'')='' and datediff(day,a.Signdate,b.F_qdrq)<=0 and F_checkmoney>0 and
datediff(day,a.Signdate,(case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end))>=0
and a.F_id=b.F_id and a.F_id=c.F_id group by a.F_id,zzrq) m on l.F_id=m.F_id and l.zzrq=m.zzrq where case when isnull(ye,0)>=0 and isnull(checkmoney,0)>0 then
isnull(checkmoney,0) when isnull(ye,0)<0 and isnull(checkmoney,0)+isnull(ye,0)>0 then isnull(checkmoney,0)+isnull(ye,0) end >0
union all select F_id,sum(ywe) as ywe from (select case when signdate<>'' then c.F_id else d.F_id end as F_id,
case when signdate<>'' then F_checkmoney else convert(money,F_fee) end as ywe from (select F_sampleid,a.F_id,F_checkmoney,signdate from crm_jc_forhs a,client b where signdate>=(case when b.qsrq>=@qsrq then b.qsrq else @qsrq end)
and signdate<(case when b.zzrq<@zzrq then dateadd(day,1,b.zzrq) else @zzrq end) and b.del=0 and b.fzr=@fzr and khfl =@khfl and b.F_id=a.F_id and not exists(select 1 from a_client_xyqd
where del=0 and F_qdrq>=signdate and case when isnull(F_sjwcrq,'')='' and isqy=1 then convert(char(10),getdate(),120) else F_sjwcrq end<=signdate and F_id=a.F_id) and invoiceid<>''
and left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%' ) c full outer join
(select b.F_id,F_sampleid,F_date,F_fee from a_charge b,client a where F_date>=(case when a.qsrq>=@qsrq then a.qsrq else @qsrq end) and F_date<(case when a.zzrq<@zzrq then dateadd(day,1,a.zzrq) else @zzrq end)
and F_isbj=1 and a.del=0 and
a.fzr=@fzr and khfl =@khfl and a.F_id=b.F_id and not exists(select 1 from a_client_xyqd where del=0 and F_qdrq>=F_date and case when isnull(F_sjwcrq,'')='' and isqy=1
then convert(char(10),getdate(),120) else F_sjwcrq end<=F_date and F_id=b.F_id) and exists(select 1 from crm_jc_forhs where left(F_sampleid,1) not in ('A','G') and F_checktype like '委托%'
and signdate<(case when a.zzrq<@qsrq then dateadd(day,1,a.zzrq) else @qsrq end) and signdate>=a.qsrq and F_sampleid=b.F_sampleid)) d on c.F_sampleid=
d.F_sampleid ) e group by F_id)n group by F_id)m
end
if @khfl=3
begin
set @khs_cx=@khs
set @ywe_cx=@ywe
end
else if @khfl=2
begin
set @khs_dx=@khs
set @ywe_dx=@ywe
end
set @khfl=@khfl-1
end
commitGO
很多，因为算法很复杂，不好意思，希望各位帮帮忙，不想再被骂了
比如说not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp1') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp2') and OBJECTPROPERTY(id, N'IsUserTable') = 1) or
not exists (select * from dbo.sysobjects where id = object_id(N'crmtemp3') and OBJECTPROPERTY(id, N'IsUserTable') = 1)这个一句话就搞定了 你写成了3个
对业务把握好点 可以写出比较好的 过程
你现在建的都是非 聚集索引 like column 跟 like %colum 区别就很大了。 你在column 上建了索引的话 like %colum 就根本上不上索引。 就想跟 isnull 他会忽略掉索引。这个有的说 ，这里说不完。