首页 新闻 搜索 专区 学院

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

 

珠儿的主页 珠儿 | 初学一级 | 园豆:4
提问于:2010-04-14 11:12
< >
分享
其他回答(1)
0

给临时表添加索引,看看能不能不用游标,改写你的存储过程。

清海扬波 | 园豆:768 (小虾三级) | 2010-04-14 12:33
我已经去掉游标了 没用游标了
支持(0) 反对(0) 珠儿 | 园豆:4 (初学一级) | 2010-04-14 12:40
但是好像作用不大
支持(0) 反对(0) 珠儿 | 园豆:4 (初学一级) | 2010-04-14 12:41
@珠儿:还有就是对你的物理表添加索引,看看查询计划,最耗费时间的是在什么地方,有针对性的优化
支持(0) 反对(0) 清海扬波 | 园豆:768 (小虾三级) | 2010-04-14 13:55
0

去掉临时表,用cte ,with 语句;临时表的效率较低,cte应该好一些的

不过你这么多数据,每次都处理,运算量本来也就很大,我想还要想办法减少调用次数(缓存,定时作业之类的)

LittlePeng | 园豆:3395 (老鸟四级) | 2010-04-14 13:32
哦 好的 我再想想办法
支持(0) 反对(0) 珠儿 | 园豆:4 (初学一级) | 2010-04-14 13:38
没用过CTE with,不知道有没好的文章,网站介绍看看。或者你有经典的例子,参考下
支持(0) 反对(0) 珠儿 | 园豆:4 (初学一级) | 2010-04-14 14:04
http://zzk.cnblogs.com/s?w=cte 现在博客园看看这些吧:)
支持(0) 反对(0) LittlePeng | 园豆:3395 (老鸟四级) | 2010-04-14 21:58
@珠儿: 感觉sql再怎么改,这么多的数据运算量也小不了,我想改从优化表结构,减少不必要的运算入手
支持(0) 反对(0) LittlePeng | 园豆:3395 (老鸟四级) | 2010-04-14 22:46
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册