cursor or set-based

本文目录

• 1、游标语句
• 2、set-based语句
• 3、计划对比
  • 3.1、while对应的执行计划
  • 3.2、set-based对应的执行计划
• 4、验证数据
  • 4.1、逻辑检查
  • 4.2、结果对比
  • 4.3、TableDiff补充

---

标题可能和正文不太相符。我主要是记录工作中遇到使用游标的语句改成普通set-based operation，执行时间快了很多。

返回顶部

1、游标语句

declare @startDate dateTime
declare @endDate dateTime
set @startDate = convert(varchar(10),dateAdd(day,-1,getDate()),120)
set @endDate = convert(varchar(10),getDate(),120)

declare @serverID int
declare loop_cursor cursor for
select distinct serverID from OnLineUserStat with(nolock)
where realTime between @startDate and @endDate
open loop_cursor
fetch next from loop_cursor into @serverID
while @@fetch_status = 0
begin
    declare @loopTime dateTime
    set @loopTime = @startDate
    while @loopTime < @endDate
    begin
        insert into OnLineUserStat2
        (serverID,kindID,OnLineUserCount,playUserCount,RoomName,StatTime,RealTime)
        select top 1 serverID,kindID,OnLineUserCount,playUserCount,RoomName,@looptime,RealTime
        from OnLineUserStat with(nolock)
        where serverID = @serverID and statTime <= @loopTime
        and dateDiff(minute,statTime,@loopTime) <= 7
        order by statTime desc
        
        set @loopTime = dateAdd(minute,5,@loopTime)
    end
    fetch next from loop_cursor into @serverID
end
close loop_cursor
deallocate loop_cursor

OnLineUserStat表的记录如下：

OnLineUserStat2表的记录如下：

游标的目的是针对每一个房间，创建时间基准(每5分钟一个基准，一天总计288个)，针对各个时间基准获取前7分钟内最近的记录。
每天的distinct ServerID个数约400，每个房间创建288个时间基准，每天insert数量约11万。游标语句在服务器上执行耗时17分钟。

返回顶部

2、set-based语句

create table #date(StandTime datetime)
declare @StandTime datetime
select @StandTime=convert(varchar(10),getdate()-1,112)
while @StandTime<convert(varchar(10),getdate(),112)
begin
    insert into #date(StandTime) values(@StandTime)
    set @StandTime=dateadd(mi,5,@StandTime)
end

;with a as(
select a.StandTime,b.* 
,row_number() over(partition by b.ServerID,a.StandTime order by b.ServerID,b.RealTime desc) rankid from #date a
,LK78DB.dbo.OnLineUserStat b with(nolock)
where b.RealTime<=a.StandTime
and b.RealTime>=dateadd(mi,-7,a.StandTime)
)
insert into OnLineUserStat2
select serverID,kindID,OnLineUserCount,playUserCount,RoomName,StandTime as StatTime,RealTime
from a
where rankid=1

drop table #date

借助于临时表生成所有时间基准，然后关联临时表与OnLineUserStat，得到最终结果。此语句耗时3秒。

返回顶部

3、计划对比

至于两者消耗为什么差别这么大，我们来看下它们的主体语句对应的执行计划，为了演示方便这里仅取三条数据。

3.1、while对应的执行计划

3.2、set-based对应的执行计划

while的逻辑读远高于set-based，while外面再套层cursor，需要repeats更多。
实际while语句的消耗在键查找，注意OnLineUserStat表的记录，StatTime和RealTime相同！可将where条件及order by更改为RealTime


相比第一个语句，逻辑读低了很多。。。

返回顶部

4、验证数据

例中将cursor+while修改为set-based，变动还是比较大。修改后我们需要验证语句与修改前是等效的，即修改后得到的结果与修改前得到的结果相同，不然修改的意义何在。

4.1、逻辑检查

语句逻辑是否满足原始需求

4.2、结果对比

最终会将数据写入到数据表，我们可以针对某一天的数据使用TableDiff对比是否存在差异。
我是将要对比的数据导入本地，当然可以直接带上源和目标的用户和密码对比数据(详细参数请参考 TableDiff /?) ，本例使用下面的命令对比

cd C:\Program Files\Microsoft SQL Server\100\COM
TableDiff -sourceserver "127.0.0.1,7777" -sourcedatabase "Test" -sourcetable "OnLineUserStat2_17" -destinationserver "127.0.0.1,7777" -destinationdatabase "Test" -destinationtable "OnLineUserStat2_204" -f "C:\diff"

结果显示源和目标是相同的(identical)

4.3、TableDiff补充

如果对比的两表数据不一致，会产生什么样的结果？为了模拟这种情况，首先更新源OnLineUserStat2_17前7行数据，使其与OnLineUserStat2_204不一致，然后运行对比代码

结果显示有7处不同，并且生成应用目标的sql脚本(C:\diff.sql)

在对应Host->Database执行diff.sql就能让目标与源保持一致(以源为标准)
如果对比的两表没有自增列，会产生什么样的结果？为了模拟这种情况，删除ID自增字段，然后运行对比代码

也就是说对比的两表至少需要有唯一标识字段，否则无法分辨对比什么数据。