After reading forementioned article by Pinal Dave I wanted to compare those methods and I even managed to make the CTE version to run in acceptable time – around 0.5 seconds on 20k records in AW2012 Person table (the original runs over 95 seconds). The trick is to use numbers table – I didn’t bother creating a new one, so I used another CTE that created 1 million of numbers.
Worth noting is the fact that it still is nowhere near the performance of LAG/LEAD functions in 2012, mostly due to I/O required.
with Numbers as ( select (v1.number * 1024) + v2.number as Num from master.dbo.spt_values v1 inner join master.dbo.spt_values v2 on v1.type = v2.type where v1.type = 'P' and v1.number < 1024 and v2.number < 1024 ), ThisPrevNext as (select Num - 1 Prev, Num, Num + 1 Nex from Numbers), CTE AS ( SELECT rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID), p.FirstName FROM Person.Person p) select th1.FirstName, th2.FirstName, th3.FirstName from ThisPrevNext tpn left join CTE th1 on th1.rownum = tpn.Prev left join CTE th2 on th2.rownum = tpn.Num left join CTE th3 on th3.rownum = tpn.Nex where tpn.Num between 1 and (select count(*) from CTE)
The reason for that is that CTE version has to scan underlying table several times, while LEAD/LAG do it in one pass. While the difference is negligible in case of 20k records (on my test VM it’s 172 ms vs 533 ms), it will be clearly visible when the table is larger. I copied Person.Person table into a new table and duplicated it several times so that it contains 639k records.
The results are as follows:
- LEAD/LAG – ~4 seconds, 18630 reads
- CTE with numbers table – ~35 seconds, 59192 reads
- original CTE – after 50 minutes I cancelled the query as it was not yet 20% done 🙂
The outcome is to use LEAD/LAG where possible – if you consider upgrading to 2012 and require such functionality, this might be one of your arguments.