sql server存储过程耗费大量CPU资源该怎么办？
0
悬赏园豆：50
[已关闭问题]
数据库有几十万条数据，还会大幅度增长，最近页面一直打不开，我调试了下，发现数据量太大，所写的存储过程使用游标来遍历的，耗费大量的CPU，内存到没什么变化。我就修改了存储过程，去掉游标。但是发现CPU的耗费量还是很大，不知道该怎么办。下面我把我的存储过程附上，请各位大哥大姐帮帮忙！！是进销存查询系统。
代码
1 --毛利统计
2 --2008-12-30 zwf
3 ALTER PROCEDURE [dbo].[GrossQueryByType]
4 (
5 @store_idList varchar(500),
6 @bdate char(10),
7 @edate char(10),
8 @goodstype varchar(30)
9 )
10 AS
11 --生成临时表
12 CREATE TABLE # (
13 [store_id] varchar(15),
14 [goods_id] [varchar] (6) ,
15 [goods_name] varchar(50),
16 [sQty] [decimal](10, 2) NULL ,
17 [sRamt] [decimal](10, 2) NULL ,
18 [eQty] [decimal](10, 2) NULL ,
19 [eRamt] [decimal](10, 2) NULL ,
20 [buyQty] [decimal](10, 2) NULL ,
21 [buyRamt] [decimal](10, 2) NULL ,
22 [saleQty] [decimal](10, 2) NULL ,
23 [saleRamt] [decimal](10, 2) NULL ,
24 [inQty] [decimal](18, 0) NULL ,
25 [inRamt] [decimal](10, 2) NULL ,
26 [outQty] [decimal](10, 2) NULL ,
27 [outRamt] [decimal](10, 2) NULL ,
28 [gross] [decimal](10, 2) NULL ,
29 [grossRate] [decimal](10, 2) NULL ,
30 [saleCost] [decimal](10,2) NULL,
31 [packnum1] int
32 )
33
34 --CREATE INDEX [IX_#] ON #([store_id], [goods_id])
35 declare @sqlStr varchar(5000)
36 declare @store_id varchar(15)
37 CREATE TABLE #A(STORE_ID VARCHAR(15),store_name varchar(50))
38 insert into #A select store_id,store_name from T_Store
39 declare @i int
40 select @i=count(1) from #A
41 while @i>0
42 begin
43 select top 1 @store_id= store_id from #A
44 --分割超市编号
45 --取所有商品
46 insert into # select @store_id,goods_id,goods_name,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,packnum1 from t_goods
47 --生成期初库存
48 update # set sqty=a.sqty,sRamt=a.sRamt
49 from t_goods_jxc_ a
50 where convert(char(10),a.sdate,121)=@bdate and a.store_id=@store_id
51 and #.goods_id=a.goods_id and #.store_id=@store_id
52
53 --生成购进和销售数据及库存调拨数据及毛利
54 update # set buyqty=t.buyqty_,buyRamt=t.buyRamt_,saleqty=t.saleQty_,saleRamt=t.saleRamt_,inqty=t.inQty_,inRamt=t.inRamt_,outqty=t.outQty_,outRamt=t.outRamt_,gross=t.gross_--,eqty=t.eqty_,eramt=t.eramt_
55 from #,(
56 select goods_id,sum(buyQty) buyQty_,sum(buyRamt)/1.17 buyRamt_,sum(saleQty) saleQty_,sum(saleRamt)/1.17 saleRamt_,sum(inQty) inQty_,sum(inRamt) inRamt_,sum(outQty) outQty_,sum(outRamt) outRamt_,sum(gross) gross_,sum(eqty) eqty_,sum(eramt) eramt_
57 from T_Goods_Jxc_
58 where convert(char(10),sdate,121)<=@edate and convert(char(10),sdate,121)>=@bdate and store_id=@store_id
59 group by goods_id
60 ) t
61 where #.goods_id=t.goods_id and #.store_id=@store_id
62
63
64 update # set eqty=a.eqty,eRamt=a.eRamt
65 from t_goods_jxc_ a
66 where convert(char(10),a.sdate,121)=@edate and a.store_id=@store_id
67 and #.goods_id=a.goods_id and #.store_id=@store_id
68
69 --清除从来有发生过进销存业务的商品
70 delete from # where sqty=0 and buyqty=0 and saleqty=0 and eqty=0
71 --fetch next from @shopcursor into @store_id
72 delete #A
73 where STORE_ID in (select top 1 STORE_ID from #A)
74 set @i=@i-1
75 end
76
77 update # set eramt= 0 where eqty=0
78 update # set gross= saleRamt - sramt - inramt - buyramt + outramt + eramt --毛利
79 update # set saleCost=sramt+buyramt+inramt-outramt-eramt --成本
80
81 if( @goodstype is null)
82 select goods_id,goods_name,sum(sQty/packnum1) sQty,sum(sRamt) sRamt,sum(eQty/packnum1) eqty,sum(eRamt) eRamt,--(sum(sramt)+sum(buyramt))/(sum(sqty)+sum(buyqty))*sum(eqty) eramt, --
83 sum(buyQty/packnum1) buyQty ,sum(buyRamt) buyRamt,sum(saleQty/packnum1) saleQty,sum(saleRamt) saleRamt,
84 sum(inQty/packnum1) inQty ,sum(inRamt) inRamt ,sum(outQty/packnum1) outQty ,sum(outRamt) outRamt,sum(gross) gross,(sum(gross)/1.17)/(case (sum(saleRamt)) when 0 then 1 else sum(saleRamt)/1.17 end)*100 grossrate,sum(saleCost) salecost
85 from #
86 group by goods_id,goods_name
87 else --if(not @goodstype is null)
88 select goods_id,goods_name,sum(sQty/packnum1) sQty,sum(sRamt) sRamt,sum(eQty/packnum1) eqty,sum(eRamt) eRamt,--(sum(sramt)+sum(buyramt))/(sum(sqty)+sum(buyqty))*sum(eqty) eramt, --
89 sum(buyQty/packnum1) buyQty ,sum(buyRamt) buyRamt,sum(saleQty/packnum1) saleQty,sum(saleRamt) saleRamt,
90 sum(inQty/packnum1) inQty ,sum(inRamt) inRamt ,sum(outQty/packnum1) outQty ,sum(outRamt) outRamt,sum(gross) gross,(sum(gross)/1.17)/(case (sum(saleRamt)) when 0 then 1 else sum(saleRamt)/1.17 end)*100 grossrate,sum(saleCost) salecost
91 from #
92 where goods_id in (select a.goods_id from T_Goods a,T_Sys_Dictionary b where a.brand = b.code and b.parent_sid=@goodstype)
93 group by goods_id,goods_name
94 GO
其他回答(1)
0
给临时表添加索引，看看能不能不用游标，改写你的存储过程。
我已经去掉游标了 没用游标了
但是好像作用不大
@珠儿:还有就是对你的物理表添加索引,看看查询计划，最耗费时间的是在什么地方，有针对性的优化
0
去掉临时表，用cte ，with 语句；临时表的效率较低，cte应该好一些的
不过你这么多数据，每次都处理，运算量本来也就很大，我想还要想办法减少调用次数（缓存，定时作业之类的）
哦 好的 我再想想办法
没用过CTE with，不知道有没好的文章，网站介绍看看。或者你有经典的例子，参考下
http://zzk.cnblogs.com/s?w=cte
现在博客园看看这些吧：）
@珠儿:
感觉sql再怎么改，这么多的数据运算量也小不了，我想改从优化表结构，减少不必要的运算入手
