TSQL“滞后”分析功能-出乎意料的糟糕性能

Question

在以前的工作中，我们必须比较项目x和项目x-1以获得大量数据(~10亿行）。由于这是在SQL Server2008R2上完成的，我们必须使用自联接。很慢。

我想我要试验一下滞后函数；如果速度快，这将是非常有价值的。我发现它快了2到3倍，但由于它应该是一个简单的操作，而且它的查询计划/表扫描更简单/大大减少了，所以我非常失望。下面复制的代码。

创建数据库：

IF EXISTS (SELECT name 
           FROM sys.databases 
           WHERE name = N'TestDBForLag')
   DROP DATABASE TestDBForLag
GO

create database TestDBForLag
ALTER DATABASE TestDBForLag SET RECOVERY SIMPLE 
go

use TestDBForLag
go

set nocount on

create table big (g binary(16) not null)
go

begin transaction

declare @c int = 0

while @c < 100
begin
    insert into big(g) values(cast(newid() as binary(16)))
    set @c += 1
end
commit

go 10000 -- n repeats of last batch, "big" now has 1,000,000 rows

alter table big
    add constraint clustered_PK primary key clustered (g)

set statistics time on
set statistics io on

-- new style
select  
    g, lag(g, 1) over (order by g) as xx
from big
order by g

-- old style
select  obig.g, 
(
    select max(g)
    from big as ibig
    where ibig.g < obig.g
) as xx
from big as obig
order by g

(1000000 row(s) affected)
Table 'Worktable'. {edit: everything zero here}.

**Table 'big'. Scan count 1, logical reads 3109**, {edit: everything else is zero here}.

SQL Server Execution Times: CPU time = 1045 ms,  elapsed time = 3516 ms.

---

(1000000 row(s) affected)

**Table 'big'. Scan count 1000001, logical reads 3190609**, {edit: everything else is zero here}.

SQL Server Execution Times:CPU time = 2683 ms,  elapsed time = 3439 ms.

select @@version 

返回：

Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Developer Edition (64-bit) 
on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

编辑：

根据@vnov的评论，在我发帖之前，我确实仔细地打折了客户端开销。我看的是CPU时间，而不是总体时间。测试：

select *
from big

Table 'big'. Scan count 1, logical reads 3109, {rest zero}
SQL Server Execution Times: CPU time = 125 ms,  elapsed time = 2840 ms.

select count(*)
from big

Table 'big'. Scan count 1, logical reads 3109, {rest zero}
SQL Server Execution Times: CPU time = 109 ms,  elapsed time = 129 ms.

select 
    sum(cast(g as tinyint)) as sumg
from (
    select g
    from big
) as xx


select 
    sum(cast(g as tinyint)) as sumg, 
    sum(cast(glag as tinyint)) as sumglag
from (
    select g, lag(g, 1) over (order by g) as glag
    from big
) as xx

---------- nonlagging batch --------------
use TestDBForLag
set nocount on

DECLARE crsr CURSOR FAST_FORWARD READ_ONLY FOR 
select g from big order by g 

DECLARE @g binary(16), @sumg int = 0
OPEN crsr

FETCH NEXT FROM crsr INTO @g
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sumg += cast(@g as tinyint)
    END
    FETCH NEXT FROM crsr INTO @g
END

CLOSE crsr
DEALLOCATE crsr

select @sumg as sumg

go 300


---------- lagging batch --------------
use TestDBForLag
set nocount on

DECLARE crsr CURSOR FAST_FORWARD READ_ONLY FOR 
select g from big order by g

DECLARE @g binary(16), @sumg int = 0 
DECLARE @glag binary(16) = 0, @sumglag int = 0
OPEN crsr

FETCH NEXT FROM crsr INTO @g
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sumg += cast(@g as tinyint)
        set @sumglag += cast(@glag as tinyint)  -- the only ...
        set @glag = @g  -- ... differences
    END
    FETCH NEXT FROM crsr INTO @g
END

CLOSE crsr
DEALLOCATE crsr

select @sumg as sumg, @sumglag as sumglag

go 300

-- trace save duration as microseconds, 
-- divide by 1000 to get back to milli
select 
    cast(textdata as varchar(8000)) as textdata, 
    avg(duration/1000) as avg_duration_ms
from trace
group by cast(textdata as varchar(8000))

对我来说，非滞后光标平均需要13.65秒，光标模拟滞后需要16.04秒。后者的大部分额外时间将来自解释器处理额外语句的开销（我预计如果用C实现会少得多），但无论如何，计算延迟的额外时间都不到20%。

那么，这种解释听起来合理吗？有人能提出为什么lag在select语句中表现如此糟糕吗？

Answer 1

检查这两个变体的执行计划，您就会看到发生了什么。为此，我使用免费版本的SQL Sentry Plan Explorer。

我比较了这三个查询（再加上一个outer apply)：

select count(*)
from big;

-- new style
select  
    g, lag(g) over (order by g) as xx
from big
order by g;

-- old style
select  obig.g, 
(
    select max(g)
    from big as ibig
    where ibig.g < obig.g
) as xx
from big as obig
order by g;

1）lag是使用窗口假脱机实现的，它提供的行数是临时工作表的两倍（1,999,999）（在本例中它在内存中，但仍然在内存中）。窗口假脱机不会缓存工作表中的所有1,000,000行，它只缓存窗口大小。

2）旧风格查询的计划非常好。优化器很聪明，它扫描表一次，并对每行执行top索引搜索，以计算max。是的，它有百万次搜索，但所有内容都在内存中，所以它相对较快。

3）悬停在计划操作符之间的粗箭头上，你会看到实际的数据大小。它是窗口假脱机的两倍。所以，当一切都在内存和CPU限制下时，它就变得很重要了。

4）旧样式的查询可以重写为：

select  obig.g, A.g
from big as obig
OUTER APPLY
(
    SELECT TOP(1) ibig.g
    FROM big as ibig
    WHERE ibig.g < obig.g
    ORDER BY ibig.g DESC
) AS A
order by obig.g;