This is the fourth post in the series of finding previous row and next row value in SELECT Statement. Read all the blog post before continuing reading this blog post for complete idea.
In the very first part I discussed that performance with the help of CTE is very poor and I encouraged users to use LEAD and LAG function of SQL Server 2012. My friend and SQL Server Expert Szymon Wojcik have written a fantastic post about this subject. I encourage everyone to read that blog post. He has demonstrated that with the help of numbers table, we can further improve the performance of the query.
Now let us compare all the various solutions which I have presented in my earlier blog post.
Before continuing with the performance comparison, I suggest you to change the database context to AdventureWorks and also enable STATISTICS IO settings on for performance comparison.
USE AdventureWorks2012 GO SET STATISTICS IO ON;
SELECT LAG(p.FirstName) OVER(ORDER BY p.BusinessEntityID) PreviousValue, p.FirstName, LEAD(p.FirstName) OVER(ORDER BY p.BusinessEntityID) NextValue FROM Person.Person p GO
WITH CTE AS( SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.BusinessEntityID), p.FirstName FROM Person.Person p ) SELECT prev.FirstName PreviousValue, CTE.FirstName, nex.FirstName NextValue FROM CTE LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1 LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1 GO
CREATE TABLE# TempTable(rownum INT, FirstName VARCHAR(256)); INSERT INTO# TempTable(rownum, FirstName) SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.BusinessEntityID), p.FirstName FROM Person.Person p; SELECT prev.FirstName PreviousValue, TT.FirstName, nex.FirstName NextValue FROM# TempTable TT LEFT JOIN# TempTable prev ON prev.rownum = TT.rownum - 1 LEFT JOIN# TempTable nex ON nex.rownum = TT.rownum + 1; GO
SELECT rownum = IDENTITY(INT, 1, 1), p.FirstName INTO# TempTable FROM Person.Person p ORDER BY p.BusinessEntityID; SELECT prev.FirstName PreviousValue, TT.FirstName, nex.FirstName NextValue FROM# TempTable TT LEFT JOIN# TempTable prev ON prev.rownum = TT.rownum - 1 LEFT JOIN# TempTable nex ON nex.rownum = TT.rownum + 1; GO
All of the above examples will return the following results as a result.
Now let us compare the performance for each of the queries with the help of message displayed in the result set.
|Worktable Logical Reads||Person Logical Read||Total Logical Read|
You can clearly see that Query 1 which is using Lead and Lag functions of SQL Server 2012 has the most optimized performance. This gives you one more reason to upgrade to SQL Server 2012.
Reference: Pinal Dave (http://blog.SQLAuthority.com)