存储过程比单个语句慢，为什么？(50分)

gxcddnk · 2008-04-24

我编写SQL语句，原来是用go一条一条运行，很快，后来把这些语句放在一个存储过程，这个存储过程还没有参数，但是发现存储过程运行起来没有用go一条一条运行快，慢很多，这是什么原因啊？

ycluo · 2008-04-24

那肯定啊，过程是用来实现一个复杂功能的， 只一条语句没有必要用过程，

shifenglei · 2008-04-24

啥语句，贴出来看看啊

gxcddnk · 2008-04-25

以下是我的存储过程 CREATE procedure yxh as begin set nocount on --------前两个月的成交量有效户 select left(ls.bizdate,6) ym,ls.fundid,ls.moneytype,sum(ls.matchamt) cjl,sum(ls.fee_jsxf) jsxf        into #Bcjlyxh        from his..h_logasset ls ,run..brokercust br ,run..custbaseinfo base        where  ls.fundid=br.fundid               and ls.fundid=base.custid               and substring(cast(br.brokerid as char(12)),5,2)='92'               and (ls.bizdate>=20080201 and bizdate<=20080331)               and (base.opendate>=20080201 and base.opendate<=20080331)               and ls.fee_jsxf>0          group by left(ls.bizdate,6),ls.fundid,ls.moneytype        having  (sum(ls.matchamt)>=10000  and moneytype=0)                or (sum(ls.matchamt)>=10000/0.9 and moneytype=1)                or (sum(ls.matchamt)>=10000/7.0 and moneytype=2) --------前两个月的市值有效户 select zj.fundid,zj.moneytype,max(fundbal+marketvalue) gsz,max(marketvalue) valsz into #szyxh          from run..hiszjk zj,run..brokercust br        where zj.fundid=br.fundid              and (rq>=20080201 and rq<=20080331)              and substring(cast(br.brokerid as char(12)),5,2)='92'        group by zj.fundid,zj.moneytype        having  (max(marketvalue)>=20000 and moneytype=0)                or (max(marketvalue)>=20000/0.9 and moneytype=1)                or (max(marketvalue)>=20000/7.0 and  moneytype=2) ------ 以下为前两个月已转化的全部有效户详细信息 select moneytype,fundid into #allB1B2m from #Bcjlyxh        union          select moneytype,fundid jsxf from #szyxh select t.*,fundname,opendate into #allB1B2m1        from #allB1B2m t,run..fundinfo base        where t.fundid=base.fundid     select t.*,base.fullname khjlname,br.brokerid custid into #allB1B2m2        from #allB1B2m1 t,run..custbaseinfo base,run..brokercust br        where t.fundid=br.fundid and br.brokerid=base.custid select moneytype,fundid,fundname fullname,opendate,0 gsz,0 valsz,0 cjl,0 jsxf,khjlname,custid,1 Valid        into #allB1B2m3        from #allB1B2m2 where opendate>=20080201   ----- 以上为前两个月已转化的全部有效户详细信息 select distinct fundid into #allB1B2 from #allB1B2m ------当月有效户计算 select ls.fundid,ls.moneytype,base.fullname,base.opendate,sum(ls.matchamt) cjl,sum(ls.fee_jsxf) jsxf        into #ttt        from his..h_logasset ls,run..brokercust br ,run..custbaseinfo base        where     ls.fundid=br.fundid              and ls.fundid=base.custid                and substring(cast(br.brokerid as char(12)),5,2)='92'              and (ls.bizdate>=20080401 and ls.bizdate<=20080430)              and (base.opendate>=20080201 and base.opendate<=20080430)              and ls.fee_jsxf>0          group by ls.fundid,ls.moneytype,base.fullname,base.opendate        having     (sum(ls.matchamt)>=10000  and moneytype=0)                or (sum(ls.matchamt)>=10000/0.9 and moneytype=1)                or (sum(ls.matchamt)>=10000/7.0 and moneytype=2) select #ttt.*,base.fullname khjlname,base.custid into #cjlt        from #ttt,run..brokercust br,run..custbaseinfo base        where #ttt.fundid=br.fundid and br.brokerid=base.custid select zj.fundid,zj.moneytype,max(fundbal+marketvalue) gsz,max(marketvalue) valsz into #tvalt3        from run..hiszjk zj,run..brokercust br        where     zj.fundid=br.fundid              and (rq>=20080401 and rq<=20080430)                and substring(cast(br.brokerid as char(12)),5,2)='92'        group by zj.fundid,zj.moneytype        having     (max(marketvalue)>=20000 and moneytype=0)                or (max(marketvalue)>=20000/0.9 and moneytype=1)                or (max(marketvalue)>=20000/7.0 and  moneytype=2) select #tvalt3.*,base.fullname khjlname,base.custid into #tvalt4        from #tvalt3,run..brokercust br,run..custbaseinfo base        where #tvalt3.fundid=br.fundid and br.brokerid=base.custid select #tvalt4.* into #tvalt5        from #tvalt4,run..custbaseinfo base        where     #tvalt4.fundid=base.custid              and (base.opendate>=20080201 and base.opendate<=20080430) select #tvalt5.moneytype,#tvalt5.fundid,base.fullname,base.opendate,#tvalt5.gsz,#tvalt5.valsz,#tvalt5.khjlname,        #tvalt5.custid        into #valt        from #tvalt5,run..custbaseinfo base        where #tvalt5.fundid=base.custid select moneytype,fundid,fullname,opendate,gsz,valsz,0 cjl,0 jsxf,khjlname,custid into #tallt        from #valt        union all        select moneytype,fundid,fullname,opendate,0 gsz,0 valsz,cjl,jsxf,khjlname,custid from #cjlt select moneytype,fundid,fullname,opendate,sum(gsz) gsz,sum(valsz) valsz,sum(cjl) cjl,sum(jsxf) jsxf,        khjlname,custid        into #allt        from #tallt        group by moneytype,fundid,fullname,opendate,khjlname,custid select *,2 Valid into #yallrt        from #allt        where fundid not in (select fundid from #allB1B2) elect #yallrt.*, mem.groupid gid,mem.groupname name into #y1             from #yallrt             left join xm..brokergroupmem mem             on #yallrt.custid=mem.brokerid select identity(int,1,1) as odr,moneytype,fundid,fullname,opendate,             gsz,valsz,cjl,jsxf,khjlname,custid,gid,name,Valid into #yaaw             from #y1             order by custid asc,Valid desc,fundid asc select * from #yaaw end 如果直接运行  exec yxh 则时间很长 但是如果不用存储过程，这样：   set nocount on --------前两个月的成交量有效户 select left(ls.bizdate,6) ym,ls.fundid,ls.moneytype,sum(ls.matchamt) cjl,sum(ls.fee_jsxf) jsxf        into #Bcjlyxh        from his..h_logasset ls ,run..brokercust br ,run..custbaseinfo base        where  ls.fundid=br.fundid               and ls.fundid=base.custid               and substring(cast(br.brokerid as char(12)),5,2)='92'               and (ls.bizdate>=20080201 and bizdate<=20080331)               and (base.opendate>=20080201 and base.opendate<=20080331)               and ls.fee_jsxf>0          group by left(ls.bizdate,6),ls.fundid,ls.moneytype        having  (sum(ls.matchamt)>=10000  and moneytype=0)                or (sum(ls.matchamt)>=10000/0.9 and moneytype=1)                or (sum(ls.matchamt)>=10000/7.0 and moneytype=2) go --------前两个月的市值有效户 select zj.fundid,zj.moneytype,max(fundbal+marketvalue) gsz,max(marketvalue) valsz into #szyxh          from run..hiszjk zj,run..brokercust br        where zj.fundid=br.fundid              and (rq>=20080201 and rq<=20080331)              and substring(cast(br.brokerid as char(12)),5,2)='92'        group by zj.fundid,zj.moneytype        having  (max(marketvalue)>=20000 and moneytype=0)                or (max(marketvalue)>=20000/0.9 and moneytype=1)                or (max(marketvalue)>=20000/7.0 and  moneytype=2) go ------ 以下为前两个月已转化的全部有效户详细信息 select moneytype,fundid into #allB1B2m from #Bcjlyxh        union          select moneytype,fundid jsxf from #szyxh go select t.*,fundname,opendate into #allB1B2m1        from #allB1B2m t,run..fundinfo base        where t.fundid=base.fundid go   select t.*,base.fullname khjlname,br.brokerid custid into #allB1B2m2        from #allB1B2m1 t,run..custbaseinfo base,run..brokercust br        where t.fundid=br.fundid and br.brokerid=base.custid go select moneytype,fundid,fundname fullname,opendate,0 gsz,0 valsz,0 cjl,0 jsxf,khjlname,custid,1 Valid        into #allB1B2m3        from #allB1B2m2 where opendate>=20080201 go   ----- 以上为前两个月已转化的全部有效户详细信息 select distinct fundid into #allB1B2 from #allB1B2m go ------当月有效户计算 select ls.fundid,ls.moneytype,base.fullname,base.opendate,sum(ls.matchamt) cjl,sum(ls.fee_jsxf) jsxf        into #ttt        from his..h_logasset ls,run..brokercust br ,run..custbaseinfo base        where     ls.fundid=br.fundid              and ls.fundid=base.custid                and substring(cast(br.brokerid as char(12)),5,2)='92'              and (ls.bizdate>=20080401 and ls.bizdate<=20080430)              and (base.opendate>=20080201 and base.opendate<=20080430)              and ls.fee_jsxf>0          group by ls.fundid,ls.moneytype,base.fullname,base.opendate        having     (sum(ls.matchamt)>=10000  and moneytype=0)                or (sum(ls.matchamt)>=10000/0.9 and moneytype=1)                or (sum(ls.matchamt)>=10000/7.0 and moneytype=2) go select #ttt.*,base.fullname khjlname,base.custid into #cjlt        from #ttt,run..brokercust br,run..custbaseinfo base        where #ttt.fundid=br.fundid and br.brokerid=base.custid go select zj.fundid,zj.moneytype,max(fundbal+marketvalue) gsz,max(marketvalue) valsz into #tvalt3        from run..hiszjk zj,run..brokercust br        where     zj.fundid=br.fundid              and (rq>=20080401 and rq<=20080430)                and substring(cast(br.brokerid as char(12)),5,2)='92'        group by zj.fundid,zj.moneytype        having     (max(marketvalue)>=20000 and moneytype=0)                or (max(marketvalue)>=20000/0.9 and moneytype=1)                or (max(marketvalue)>=20000/7.0 and  moneytype=2) go select #tvalt3.*,base.fullname khjlname,base.custid into #tvalt4        from #tvalt3,run..brokercust br,run..custbaseinfo base        where #tvalt3.fundid=br.fundid and br.brokerid=base.custid go select #tvalt4.* into #tvalt5        from #tvalt4,run..custbaseinfo base        where     #tvalt4.fundid=base.custid              and (base.opendate>=20080201 and base.opendate<=20080430) go select #tvalt5.moneytype,#tvalt5.fundid,base.fullname,base.opendate,#tvalt5.gsz,#tvalt5.valsz,#tvalt5.khjlname,        #tvalt5.custid        into #valt        from #tvalt5,run..custbaseinfo base        where #tvalt5.fundid=base.custid go select moneytype,fundid,fullname,opendate,gsz,valsz,0 cjl,0 jsxf,khjlname,custid into #tallt        from #valt        union all        select moneytype,fundid,fullname,opendate,0 gsz,0 valsz,cjl,jsxf,khjlname,custid from #cjlt go select moneytype,fundid,fullname,opendate,sum(gsz) gsz,sum(valsz) valsz,sum(cjl) cjl,sum(jsxf) jsxf,        khjlname,custid        into #allt        from #tallt        group by moneytype,fundid,fullname,opendate,khjlname,custid go select *,2 Valid into #yallrt        from #allt        where fundid not in (select fundid from #allB1B2) go elect #yallrt.*, mem.groupid gid,mem.groupname name into #y1             from #yallrt             left join xm..brokergroupmem mem             on #yallrt.custid=mem.brokerid go select identity(int,1,1) as odr,moneytype,fundid,fullname,opendate,             gsz,valsz,cjl,jsxf,khjlname,custid,gid,name,Valid into #yaaw             from #y1             order by custid asc,Valid desc,fundid asc go select * from #yaaw go 就会快很多，我算了一下，会快10倍，不知道什么原因

shifenglei · 2008-04-25

不知道啥原因，用查询分析器里面的查询－>显示执行计划，看看执行存储过程的时候哪消耗资源最多吧。 表的索引啥的都建好了吗？

power255 · 2008-04-25

试试commit;

gxcddnk · 2008-04-25

中间生成了很多的临时结果集，那些临时结果集都没有索引， 我想是否是事务的原因，用go一条一条运行每条语句都是一个事务，执行完就写盘了，如果编成存储过程，一个存储过程是一个事务，中间那些结果集不会马上写盘，而且会写大量的日志，占用很多的内存，所以速度慢， 该如何解决呢，加commit也不起作用，因为一个存储过程就是一个事务，加了也没用， 能不能让一个存储过程运行时不要事务处理？

power255 · 2008-04-25

我的意思是每条语句后面都加commit

gxcddnk · 2008-04-25

试过了，每条语句都加上       begin tran             commit tran 也没用

存储过程比单个语句慢，为什么？(50分)

gxcddnk

Unregistered / Unconfirmed

ycluo

Unregistered / Unconfirmed

shifenglei

Unregistered / Unconfirmed

gxcddnk

Unregistered / Unconfirmed

shifenglei

Unregistered / Unconfirmed

power255

Unregistered / Unconfirmed

gxcddnk

Unregistered / Unconfirmed

power255

Unregistered / Unconfirmed

gxcddnk

Unregistered / Unconfirmed

Similar threads