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.
Originally posted on Journey to SQL Authority with Pinal Dave:
The first email I read this morning had only one statement in it, and it gave me an idea to write this blog post.
“How to access Previous Row Value and Next Row Value in SELECT statement?”
Very interesting question indeed. The matter of the fact, I have written about this in my earlier blog Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012. Let us quickly understand it in it with the help of script.
For example here is the column where we want to get access to the previous row and the next row in SELECT statement.
FROM Person.Person p
ORDER BY p.BusinessEntityID
Now let us use the new Analytic Function from SQL Server 2012 to get previous and next row values.
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
View original 148 more words