那年,我负责维护一家食品企业的一个内部销售管理系统。在经销商库存管理模块,有经销商库存产品生产日期管理功能, 如下是登记页面, 业务描述为,根据当天对经销商的产品库存的盘点,记录各产品在不同生产日期的库存量,从而可以了解产品的库存新鲜度,这在供应链和库存管理上是一个很重要的概念。
database中有2张表,列举主要字段如下表:
字段名 | 字段类型 | 说明 | |
经销商库存表 Sale_Ty PK:EffDt+CustCd+PrdCd | EffDt | Char(8) | 盘库日期,存储的数据格式如“20120217” |
CustCd | Char(8) | 经销商编号 | |
PrdCd | Char(7) | 产品编号 | |
RealQty | Decimal(9,1) | 盘库库存量(所有生产日期的) | |
库存产品生产日期表 SalPrdDate_Td PK:EffDt+CustCd+PrdCd+PrdDate | EffDt | Char(8) | 盘库日期,存储的数据格式如“20120217” |
CustCd | Char(8) | 经销商编号 | |
PrdCd | Char(7) | 产品编号 | |
PrdDate | Char(8) | 产品的生产日期,存储的数据格式如“20120217” | |
RealQty | Decimal(9,1) | 产品在该生产日期的库存量 |
目前系统大约有经销商3000家,产品80条。
Sale_Ty存放经销商的每日库存, 即每天约产生3000*80=24w条记录;
由于每月要对所有经销商做库存产品生产日期登记, 则每个月SalPrdDate_Td要产生24w*N(假定N=4)=160w条记录。
在表优化方面,对Sale_Ty按年做了分区。
在查询以上登记信息时,出现性能瓶颈, 先看展示页面:
摘写Stored Procedure中主要Sql如下:
1 SELECT
2 AREA1C, AREA1D, AREA2C, AREA2D, CASE WHEN @FLDLIST & 8 = 8 THEN AREA3C ELSE '' END AREA3C, AREA3D,
3 CASE WHEN @FLDLIST & 256 = 256 THEN B.CustCd ELSE '' END CustCd,
4 CASE WHEN @FLDLIST & 256 = 256 THEN B.CusDsc ELSE '' END CusDsc,
5 P.PrdKind, PrdKindNm, P.PrdCls1, PrdCls1Nm, PrdNm,
6 E.PrdDate,
7 SUM(A.RealQty) RealQty, SUM(ISNULL(E.RealQty, A.RealQty)) SalPrdRealQty
8 FROM Sale_Ty A
9 INNER JOIN SALDB..Customer_Bh B ON A.CustCd = B.CustCd
10 INNER JOIN #DEPT D ON B.DeptCd = D.AREA3C
11 INNER JOIN #PRD P ON A.PrdCd = P.PrdCd
12 LEFT OUTER JOIN SalPrdDate_Td E ON A.EffDt = E.EffDt AND A.CustCd = E.CustCd AND A.PrdCd = E.PrdCd
13 WHERE A.EffDt = @EffDt
14 AND A.CustCd LIKE CASE WHEN @CUSTCD = '1' THEN '%' ELSE @CUSTCD END
15 GROUP BY E.PrdDate,
16 AREA1C, AREA1D, AREA2C, AREA2D, CASE WHEN @FLDLIST & 8 = 8 THEN AREA3C ELSE '' END, AREA3D,
17 CASE WHEN @FLDLIST & 256 = 256 THEN B.CustCd ELSE '' END,
18 CASE WHEN @FLDLIST & 256 = 256 THEN B.CusDsc ELSE '' END,
19 P.PrdKind, PrdKindNm, P.PrdCls1, PrdCls1Nm, PrdNm
每次点查询,即执行Stored Procedure时,duration约为5min,用户不能容忍
优化为:在where中的A.EffDt = @EffDt后面加入如下条件:
AND E.EffDt = @EffDt
优化后,duration为不超过5seconds,用户反响很好。
以上性能优化日期2010年3月8日