在编写SQL批处理或存储过程代码的过程中,经常会碰到有些业务逻辑的处理,需要对满足条件的数据记录逐行进行处理,这个时候,大家首先想到的方案大部分是用“游标”进行处理。
举个例子,在订单管理系统中,客服需要对订单日期为2012-09-01的销售订单进行某个批量操作,比如批量发货操作,后台业务逻辑处理时,需要对满足条件的订单记录进行逐行处理。
我首先是采用“游标”编写的业务逻辑存储过程,SQL代码可以如下:
游标 DECLARE @ORDERID VARCHAR(30) -- 声明局部游标:从订单数据表获取订单日期为2012-09-01,订单类型为Sales的订单编号 DECLARE CURSOR_ORDER CURSOR LOCAL FOR SELECT ORDERID FROM ORDERHD H WHERE ORDERDATE = ‘2012-09-01‘ AND H.ORDERTYPE = ‘Sales‘ -- 打开游标 OPEN CURSOR_ORDER FETCH NEXT FROM CURSOR_ORDER INTO @ORDERID WHILE @@FETCH_STATUS = 0 BEGIN /* 此处编写对当前行数据的业务逻辑处理代码 */ -- 得到下一条记录 FETCH NEXT FROM CURSOR_ORDER INTO @ORDERID END -- 关闭游标 CLOSE CURSOR_ORDER -- 释放游标 DEALLOCATE CURSOR_ORDER
功能是实现了,但是客服在实际使用过程中,经常反馈批量操作效率太慢,需要等待较长时间才能完成操作。经过测试发现,速度慢在游标逐行处理过程中,当需要处理的记录数较大,而且游标处理位于数据库事务内时,速度非常慢。
那么,有什么方法可以解决这个处理速度慢的问题吗?
经不断的尝试,终于找到一个方法,那就是用WHILE循环来进行逐行数据处理。首先将需要处理的数据记录获取到一个临时表(此临时表包括2个重要字段:REFID - 记录行号,DealFlg:行处理标识,用1/0标识行是否已处理),然后WHILE循环对临时表进行逐行处理,SQL代码如下:
While 循环 DECLARE @REFID INT , @ORDERID VARCHAR(30) -- 获取待处理的数据记录到临时表 -- 字段说明:REFID:记录行号 / DealFlg:行处理标识 SELECT REFID = IDENTITY(INT , 1, 1), DealFlg = 0, ORDERID INTO #Temp_Lists FROM ORDERHD WHERE ORDERDATE = ‘2012-09-01‘ AND H.ORDERTYPE = ‘Sales‘ -- 获取临时表数据的最小行号 SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0 -- 若最小行号不为空(有需要处理的数据) WHILE @REFID IS NOT NULL BEGIN -- 获取当前处理行的信息 SELECT @ORDERID = ORDERID FROM #Temp_Lists WHERE REFID = @REFID /* 此处编写对当前行数据的业务逻辑处理代码 */ -- 标识当前行已处理完毕 UPDATE #Temp_Lists SET DealFlg = 1 WHERE REFID = @REFID -- 选择下一行号 SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0 AND REFID > @REFID END
经过这样对原存储过程进行修正后,批量操作速度得到显著提升。
有兴趣的朋友,可以尝试使用这个方法替代游标,对比2种方案的处理效率。
SQL Server 中用While循环替代游标(Cursor的解决方案
原文:https://www.cnblogs.com/tc310/p/12877968.html