CODEWITHSUNDEEP
sqlsql-serverperformancelagwindowed
Andrew Usachov
Andrew Usachov

Reputation: 51

In Microsoft SQL Server, is it possible to speed up Lag() in some way?

It would seem to be (almost) as easy to scan a table over a clustered index, summarizing a field from the "previous record", as just to summarize that field over the entire table. But no :( Is there anything I can do?

create table #Tmp(n int not null primary key)
insert into #Tmp values(0)

declare @k int = 1

while @k < 1024 * 1024 * 32
begin
    insert into #Tmp
    select n + @k
    from #Tmp

    select @k = @k + @k
end

declare @dummy bigint

declare @d1 datetime = GetDate()

select
    @dummy = Sum(Convert(bigint, n))
from
    #Tmp

declare @d2 datetime = GetDate()

select
    @dummy = Sum(convert(bigint, n0))
from
    (
    select
        n0 = Lag(n) over (order by n)
    from
        #Tmp
    ) as Q

declare @d3 datetime = GetDate()

select Convert(time(3), @d2 - @d1), Convert(time(3), @d3 - @d2)
-- 00:00:01.460, 00:00:46.273

drop table #Tmp

Upvotes: 2

Views: 1023

Answers (2)

lptr
lptr

Reputation: 6788

..for sql2019..

select
    @dummy = Sum(convert(bigint, n0)) * (1+APPROX_COUNT_DISTINCT(n0)-APPROX_COUNT_DISTINCT (n0))   
from …

Upvotes: 0

Stu
Stu

Reputation: 32579

Using SQL server prior to 2019 you can utilise some benefits of Batch Mode by creating a dummy table with a columnstore index.

Running the window function query on SQL Server 2016 it took 25 seconds:

enter image description here

However, after creating the following table

create table dbo.bmode (Id int not null)
create nonclustered columnstore index CC_BatchModeHack on dbo.bmode (Id)

I can include it in the query using an outer join. It actually plays no part in the query at all however tricks the optimizer into selecting batch mode for the window function

select @dummy = Sum(Convert(bigint, n0))
from (
  select n0 = Lag(n) over (order by n)
  from #Tmp
  left join bmode on 1=0
)q

the result is execution time fell to almost 2 seconds

enter image description here

Upvotes: 1

Related Questions