cursor or set-based

2023-08-05 18:27:34

标题可能和正文不太相符。我主要是记录工作中遇到使用游标的语句改成普通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\\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自增字段，然后运行对比代码

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

码农公寓