SQLWebpedia

Since many of us write sql code for websites, I thought I would share this performance tip I found while improving a proc to get list with start and end positions using row numbers - what I am calling pagination. 

Using common table expressions (CTE) in SQL 2005 is the best way to do this (that I know of). Changes I made are highlighted: 

Declare @FutureDate varchar(10)
Set @FutureDate = convert(varchar(10), dateadd(d, 1+ convert(varchar, @day), getdate()),101) --Note the +1 here is so we get to the end of the day (business requirement).

Set @SQL='with DN as(
SELECT TOP (' + cast(@EndPosition as varchar) + ') DomainNameID, ROW_NUMBER()
OVER(ORDER BY ' + @SortBy + ') AS RowNumber
FROM DenormTable with (nolock)
WHERE ExpDate <= ''' + @FutureDate + ''')
Insert into #Results (ID, DomainNameId, TotalResults)
Select ROWNUMBER, DomainNameID, (Select max(RowNumber) from DN) as TotalResults
From DN where ROWNUMBER between ' + cast(@StartPosition as varchar) + ' and ' + cast(@EndPosition as varchar) 

Explanation of changes:

For: ExpDate <= ''' + @FutureDate + ''')
It used to be:Set @sql = @sql + ' and datediff(d, getdate(), ExpDate) <= ' + convert(varchar, @Day)

Notice that the function is on the left side. The query optimizer doesn’t know what the value is, so it will have to scan the index when it looks at the data. Changing to function on right side of operand will make what is called a SARGable query. 

For:  TOP (' + cast(@EndPosition as varchar) + ')

We didn’t use a TOP value previously, but this will limit the result set that the CTE will need to use. It must be the end position value, otherwise it will prematurely truncate your result set. 

For: with (nolock)

We use set transaction isolation level read uncommitted  in our procs, but because this is dynamic sql, you need to add as it is run in another context.

-Chuck