上周工作中,主要针对cust_statement报表进行优化。优化对象:存储过程。
分析后发现,影响性能的瓶颈在于对于一个游标的使用:
希望达到的效果是:
分析游标对逐条提取的数据的处理过程:
(1)把JOB_ORDER_ID相同的container_no拼凑起来
(2)更新临时表#RESULT_COMP_LIST
对于80000多条数据的临时表,每次提取一条出来处理,要提取80000多次,性能是很差的。
优化方法:
把游标所处理的拼凑过程写成函数:
分析后发现,影响性能的瓶颈在于对于一个游标的使用:
DECLARE CUR_COMP_LIST CURSOR FOR SELECT BUSINESS_ID FROM #RESULT_COMP_LIST
OPEN CUR_COMP_LIST
FETCH NEXT FROM CUR_COMP_LIST INTO @FIELDVALUE
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FILTERFIELD = N''
SELECT @FILTERFIELD = @FILTERFIELD + ISNULL(CONTAINER_NO, '') + nCHAR(13) + nCHAR(10) FROM OZ_BK_CONTAINER WHERE JOB_ORDER_ID = @FIELDVALUE
UPDATE #RESULT_COMP_LIST SET CTN_NO = @FILTERFIELD WHERE BUSINESS_ID = @FIELDVALUE
FETCH NEXT FROM CUR_COMP_LIST INTO @FIELDVALUE
END
临时表#RESULT_COMP_LIST的数据量在八万多条,用游标处理,这条语句消耗时间约70秒。从逻辑上来说:一个JOB_ORDER_ID对应多个箱子编号:
OPEN CUR_COMP_LIST
FETCH NEXT FROM CUR_COMP_LIST INTO @FIELDVALUE
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FILTERFIELD = N''
SELECT @FILTERFIELD = @FILTERFIELD + ISNULL(CONTAINER_NO, '') + nCHAR(13) + nCHAR(10) FROM OZ_BK_CONTAINER WHERE JOB_ORDER_ID = @FIELDVALUE
UPDATE #RESULT_COMP_LIST SET CTN_NO = @FILTERFIELD WHERE BUSINESS_ID = @FIELDVALUE
FETCH NEXT FROM CUR_COMP_LIST INTO @FIELDVALUE
END
JOB_ORDER_ID |
CONTIANER_NO |
001 |
STEF3949023 |
001 |
GRTE9809453 |
001 |
GRTE9809453 |
002 |
PPOP5493054 |
002 |
JJIT4980354 |
003 |
UURE5493850 |
003 |
TREI5890346 |
JOB_ORDER_ID |
CONTAINER_NO |
001 |
STEF3949023 GRTE9809453 |
002 |
PPOP5493054 |
003 |
UURE5493850 |
(1)把JOB_ORDER_ID相同的container_no拼凑起来
(2)更新临时表#RESULT_COMP_LIST
对于80000多条数据的临时表,每次提取一条出来处理,要提取80000多次,性能是很差的。
优化方法:
把游标所处理的拼凑过程写成函数:
CREATE FUNCTION FUN_MERGE(@JOB_ORDER_ID NVARCHAR(200))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @TEMP NVARCHAR(4000)
SET @TEMP = ''
SELECT @TEMP=@TEMP+CONTAINER_NO + NCHAR(13) + NCHAR(10) FROM OZ_BK_CONTAINER
WHERE JOB_ORDER_ID = @JOB_ORDER_ID
RETURN STUFF(@TEMP,1,1,'')
END
存储过程内部一次行直接调用此函数做UPDATE:
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @TEMP NVARCHAR(4000)
SET @TEMP = ''
SELECT @TEMP=@TEMP+CONTAINER_NO + NCHAR(13) + NCHAR(10) FROM OZ_BK_CONTAINER
WHERE JOB_ORDER_ID = @JOB_ORDER_ID
RETURN STUFF(@TEMP,1,1,'')
END
UPDATE #RESULT_COMP_LIST SET CTN_NO = C.ctns
FROM (SELECT JOB_ORDER_ID, dbo.FUN_MERGE(JOB_ORDER_ID) as ctns FROM OZ_BK_CONTAINER ) C
WHERE C.JOB_ORDER_ID = #RESULT_COMP_LIST.BUSINESS_ID
时间从70秒提升到900毫秒以内。FROM (SELECT JOB_ORDER_ID, dbo.FUN_MERGE(JOB_ORDER_ID) as ctns FROM OZ_BK_CONTAINER ) C
WHERE C.JOB_ORDER_ID = #RESULT_COMP_LIST.BUSINESS_ID