库存产品日期查询功能sql优化

那些年，我负责维护一家快消品企业内部的销售管理系统。在经销商库存管理模块，有经销商库存产品生产日期管理功能， 如下是登记表单， 业务描述为，根据当天对经销商的产品库存的盘点，记录各产品在不同生产日期的库存量，从而可以了解产品的库存新鲜度，这在供应链和库存管理上是一个很重要的概念。

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代表不同的产品生产日期，假定N=4）=160w条记录。 

在表优化方面，对Sale_Ty按年做了分区。

 

在查询以上登记的数据时，出现性能瓶颈， 先看展示页面：

 

这个销售管理系统的功能逻辑主要基于SQLServer数据库的存储过程（写Stored Procedure，后文简拼为SP）来实现。

摘这个SP中主要Sql如下：

SELECT 
    AREA1C, AREA1D,  AREA2C, AREA2D,  CASE WHEN @FLDLIST & 8 = 8 THEN AREA3C ELSE '' END AREA3C, AREA3D, 
    CASE WHEN @FLDLIST & 256 = 256 THEN B.CustCd ELSE '' END CustCd,
    CASE WHEN @FLDLIST & 256 = 256 THEN B.CusDsc ELSE '' END CusDsc,
    P.PrdKind, PrdKindNm,  P.PrdCls1, PrdCls1Nm,  PrdNm, 
    E.PrdDate,
    SUM(A.RealQty) RealQty, SUM(ISNULL(E.RealQty, A.RealQty)) SalPrdRealQty
FROM Sale_Ty A
INNER JOIN SALDB..Customer_Bh B ON A.CustCd = B.CustCd
INNER JOIN #DEPT D ON B.DeptCd = D.AREA3C
INNER JOIN #PRD P ON A.PrdCd = P.PrdCd
LEFT OUTER JOIN SalPrdDate_Td E ON A.EffDt = E.EffDt AND A.CustCd = E.CustCd AND A.PrdCd = E.PrdCd
WHERE A.EffDt = @EffDt
    AND A.CustCd LIKE CASE WHEN @CUSTCD = '1' THEN '%' ELSE @CUSTCD END
GROUP BY E.PrdDate,
    AREA1C, AREA1D,  AREA2C, AREA2D,  CASE WHEN @FLDLIST & 8 = 8 THEN AREA3C ELSE '' END, AREA3D,
    CASE WHEN @FLDLIST & 256 = 256 THEN B.CustCd ELSE '' END,
    CASE WHEN @FLDLIST & 256 = 256 THEN B.CusDsc ELSE '' END, 
    P.PrdKind, PrdKindNm,  P.PrdCls1, PrdCls1Nm,  PrdNm

 

每次点查询，即执行这个SP时，duration长达5minutes，销售内勤不能容忍！

 

优化为：在where中的A.EffDt = @EffDt后面再加一个条件AND E.EffDt = @EffDt对SalPrdDate_Td这个大表进行过滤。即，优化后的SQL为：

 

...FROM Sale_Ty A...LEFT OUTER JOIN SalPrdDate_Td E ON A.EffDt = E.EffDt AND A.CustCd = E.CustCd AND A.PrdCd = E.PrdCd WHERE A.EffDt = @EffDt and E.EffDt = @EffDt ...

 

 

优化后，duration为不超过5seconds，用户反响很好！

 

以上性能优化日期2010年3月8日