SQL SERVER查询优化----游标

上周工作中，主要针对cust_statement报表进行优化。优化对象：存储过程。       分析后发现，影响性能的瓶颈在于对于一个游标的使用：      

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对应多个箱子编号:       

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 GRTE9809453
002	PPOP5493054 JJIT4980354
003	UURE5493850 TREI5890346

     分析游标对逐条提取的数据的处理过程：      （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：  

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毫秒以内。

转载于:https://www.cnblogs.com/abenmao/archive/2012/08/06/2625213.html