存储过程执行速度慢,该怎么处理
存储过程执行速度慢
今日写存储过程执行查询作业，整体数据量有千万条，代码大概如下：
代码稍微比较繁琐，所以删掉了一些字段和关联表，大侠们随意看看就好。
实验结果表明，如果将WHERE语句中的参数改为固定值‘2014-2-8’等，存储过程执行时间为7s钟；
如果传递参数‘2014-2-8’执行存储过程，执行时间为30分钟左右。
网上查询到了参数嗅探等问题，不是很理解。。。
http://blog.****.net/emili/article/details/2192081使用了这位大侠的方法，但是没有解决问题~~
求帮忙！！
------解决思路----------------------
建议把查询语句拆分为若干个子查询,子查询结果分别存为临时表.最后再关联起来.
------解决思路----------------------
这函数也太多了。。
没研究，不过看起来有些建议。
1.最外层的条件，其实有些是可以在内层查询里面过滤的，因为表dbo.buisnessData_lx 在里面是左连接
2. 内层的一些条件，其实可以简化
3. 这种，and两边的判断其实是一样了吧，重复
4. 这两个为同一个表，可以不用查询两次
最后还是看看执行计划，看表被访问的次数，返回的数据量。
表尽量访问1次就行；如果最终数据比较少，读表时却很多，肯定可以提前筛选掉；
为了防止重编译，可以用sp_executesql来参数化；
------解决思路----------------------
聚集索引扫描和表扫描都是要遍历全表，只不过一个上面有聚集索引，另一个是堆表。光知道这个分析不了问题啊，要不然你就把执行计划都贴上来。
今日写存储过程执行查询作业，整体数据量有千万条，代码大概如下：
ALTER proc [dbo].[zd_lxfl_old0228]
@spbh varchar(20)='',
@s_rq varchar(10)='',
@e_rq varchar(10)='',
@danwbh varchar(20)='',
@cd varchar(100)='',
@orgid int ,
@isnlj int,
@xylb int
as
begin
declare @varspbh VARCHAR(20)
declare @vars_rq VARCHAR(10)
declare @vare_rq VARCHAR(10)
declare @vardanwbh VARCHAR(20)
--declare @vart int
declare @varcd VARCHAR(20)
--declare @varghdwbh VARCHAR(100)
declare @varorgid int
declare @varisnlj int
declare @varxylb int
begin
set @varspbh=@spbh;
set @vars_rq=@s_rq;
set @vare_rq=@e_rq;
set @vardanwbh=@danwbh;
--set @vart=@t;
set @varcd=@cd;
--set @varghdwbh=@ghdwbh;
set @varorgid=@orgid;
set @varisnlj=@isnlj;
set @varxylb=@xylb;
end
select row_number() OVER (ORDER BY a.rq,a.djbh ASC) AS id,
a.orgid,
(case when a.lx=0 then a.shl*a.xsshl else 0 end
+ case when a.lx=1 then a.shl*a.hsje else 0 end
+(case when @varisnlj=1 then lxbynxse else 0 end)
- case when b.jsje is not null then b.jsje else 0 end
-case when c.jsje IS not null and c.jsje>0
then round(c.jsje,2) else 0 end) as bcjs,
a.dwbh ,a.annual_TotalSales,
lxbynxse,lxbynxse_contract,
cast(g.credit as varchar)+'~'+cast(g.creditend as varchar) as lxbynxse_credit,
lxbynxseid_contract, h.rate as lxbynxseid_contract_rate,
cast(h.credit as varchar)+'~'+cast(h.creditend as varchar) as lxbynxse_contract_credit,
round (((case when a.lx=0 then a.shl*a.xsshl else 0 end)+(case when a.lx=1 then a.shl*a.hsje else 0 end)+isnull(a.lxbynxse_contract,0)),2) as sale_rebate_contract
-----------a.yesfltj,a.nofltj,-------------------
from (select a.orgid,a.spid,a.spbh,a.spmch,a.shpgg,a.dw,a.shpchd,a.xsshl,a.hsje,
a.danwbh,a.dwmch,a.jiyili,a.rq,a.djbh,a.cgfzr,a.dwbh ,a.bm,
case when len(ltrim(rtrim(b.beizhu)))=6 and ltrim(rtrim(b.beizhu))=left(ltrim(rtrim(a.danwbh)),6) then 1
when len(ltrim(rtrim(b.beizhu)))=5 and ltrim(rtrim(b.beizhu))=left(ltrim(rtrim(a.danwbh)),5) then 1
when len(ltrim(rtrim(b.beizhu)))=4 and ltrim(rtrim(b.beizhu))=left(ltrim(rtrim(a.danwbh)),4) then 1
when len(ltrim(rtrim(b.beizhu)))=len(ltrim(rtrim(a.danwbh))) and ltrim(rtrim(b.beizhu))=ltrim(rtrim(a.danwbh)) then 1
else 0
end yesfltj,
gf.credit as lxbynxsecredit,gf.creditend as lxbynxsecreditend,gf.rate as lxbynxserate,gf.id as lxbynxseid1,
sum(case when gf.type=5 and (datediff(day,gf.start_date,a.rq)>=0 and datediff(day,a.rq,gf.end_date)>=0)
and ((gf.creditend>gf.credit and e.annual_TotalSales>gf.credit and e.annual_TotalSales<=gf.creditend) or(gf.creditend=gf.credit and gf.credit<>0 and e.annual_TotalSales>gf.credit) or (gf.creditend=gf.credit and gf.credit=0))
then gf.id else 0 end) as lxbynxseid,
sum(case when gf.type=5 and (datediff(day,gf.start_date,a.rq)>=0 and datediff(day,a.rq,gf.end_date)>=0)
and ((gf.creditend>gf.credit and e.annual_TotalSales>gf.credit and e.annual_TotalSales<=gf.creditend) or(gf.creditend=gf.credit and gf.credit<>0 and e.annual_TotalSales>gf.credit) or (gf.creditend=gf.credit and gf.credit=0))
then a.hsje * gf.rate else 0 end) as lxbynxse
from dbo.buisnessData_lx a
left join dbo.fl_cg_new_v gf on a.orgid=gf.orgid
and a.spid=gf.spid
and datediff(day,gf.rl_start_date,a.rq)>=0
and datediff(day,a.rq,gf.rl_end_date)>=0
and (gf.type=5)
left join dbo.fl_lx_v b on (a.spid=b.spid and a.orgid=b.orgid and DATEDIFF(DAY,b.ksrq,a.rq)>=0 and DATEDIFF(DAY,a.rq,b.zzrq)>=0)
where not exists(select spid,beizhu
from dbo.fl_lx
where spid=a.spid and ltrim(rtrim(substring(beizhu,charindex('?',beizhu)+1,20)))=
case when len(ltrim(rtrim(substring(beizhu,charindex('?',beizhu)+1,20))))=6 then left(ltrim(rtrim(a.danwbh)),6)
else ltrim(rtrim(a.danwbh)) end and charindex('?',beizhu)>0)
group by a.djbh,a.rq ,a.xsshl,a.bm,b.beizhu,b.id,b.shl,b.lx,b.lxxylb,c.did,gf.credit,
gf.creditend,gf.rate,gf.id, a.spbh ,a.spmch ,a.shpchd,a.danwbh,a.dwmch,
a.shpgg ,a.dw ,a.hsje,a.spid,a.jiyili,a.dwbh,a.cgfzr,a.orgid,e.annual_TotalSales
) a
left join (select djbh,sum(jsje) as jsje,spid,lxflid
from dbo.fl_xsdjbhtemp
group by djbh,spid,lxflid) c
on a.djbh=c.djbh and a.spid=c.spid and a.id=c.lxflid
left join (select djbh,sum(jsje) as jsje,spid,lxflid
from dbo.fl_xsdjbh where iszx=3
group by djbh,spid,lxflid) b
on a.djbh=b.djbh and a.spid=b.spid and a.id=b.lxflid
left join dbo.fl_cg_new_v g on a.lxbynxseid=g.id
left join dbo.fl_cg_new_v h on a.lxbynxseid_contract=h.id
where a.yesfltj=1
and a.rq>=@vars_rq and a.rq<=@vare_rq
and a.spbh like @varspbh+'%'
and a.danwbh like @vardanwbh+'%'
and a.shpchd like'%'+@varcd+'%'
and a.orgid=@varorgid
and (xinyongleibie<0 or (g.cridit_type=@varxylb or a.lxxylb=@varxylb))
end
代码稍微比较繁琐，所以删掉了一些字段和关联表，大侠们随意看看就好。
实验结果表明，如果将WHERE语句中的参数改为固定值‘2014-2-8’等，存储过程执行时间为7s钟；
如果传递参数‘2014-2-8’执行存储过程，执行时间为30分钟左右。
网上查询到了参数嗅探等问题，不是很理解。。。
http://blog.****.net/emili/article/details/2192081使用了这位大侠的方法，但是没有解决问题~~
求帮忙！！
------解决思路----------------------
建议把查询语句拆分为若干个子查询,子查询结果分别存为临时表.最后再关联起来.
------解决思路----------------------
没研究，不过看起来有些建议。
1.最外层的条件，其实有些是可以在内层查询里面过滤的，因为表dbo.buisnessData_lx 在里面是左连接
where a.yesfltj=1
and a.rq>=@vars_rq and a.rq<=@vare_rq
and a.spbh like @varspbh+'%'
and a.danwbh like @vardanwbh+'%'
and a.shpchd like'%'+@varcd+'%'
and a.orgid=@varorgid
and (xinyongleibie<0 or (g.cridit_type=@varxylb or a.lxxylb=@varxylb))
2. 内层的一些条件，其实可以简化
where spid=a.spid
and ltrim(rtrim(substring(beizhu,charindex('?',beizhu)+1,20)))=
case when len(ltrim(rtrim(substring(beizhu,charindex('?',beizhu)+1,20))))=6 then left(ltrim(rtrim(a.danwbh)),6)
else ltrim(rtrim(a.danwbh)) end and charindex('?',beizhu)>0)
3. 这种，and两边的判断其实是一样了吧，重复
when len(ltrim(rtrim(b.beizhu)))=6 and ltrim(rtrim(b.beizhu))=left(ltrim(rtrim(a.danwbh)),6) then 1
when len(ltrim(rtrim(b.beizhu)))=5 and ltrim(rtrim(b.beizhu))=left(ltrim(rtrim(a.danwbh)),5) then 1
when len(ltrim(rtrim(b.beizhu)))=4 and ltrim(rtrim(b.beizhu))=left(ltrim(rtrim(a.danwbh)),4) then 1
4. 这两个为同一个表，可以不用查询两次
left join dbo.fl_lx_v b on (a.spid=b.spid and a.orgid=b.orgid and DATEDIFF(DAY,b.ksrq,a.rq)>=0 and DATEDIFF(DAY,a.rq,b.zzrq)>=0)
where not exists(
select spid,beizhu
from dbo.fl_lx
最后还是看看执行计划，看表被访问的次数，返回的数据量。
表尽量访问1次就行；如果最终数据比较少，读表时却很多，肯定可以提前筛选掉；
为了防止重编译，可以用sp_executesql来参数化；
------解决思路----------------------
聚集索引扫描和表扫描都是要遍历全表，只不过一个上面有聚集索引，另一个是堆表。光知道这个分析不了问题啊，要不然你就把执行计划都贴上来。