SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – from Pinal Dave

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
 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
 (select Num - 1 Prev, Num, Num + 1 Nex
 from Numbers),
 rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
 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:

  1. LEAD/LAG – ~4 seconds, 18630 reads
  2. CTE with numbers table – ~35 seconds, 59192 reads
  3. 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.

One thought on “SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – from Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s