SQL paged search results

Question

I'm trying to figure out a way to simplify implementing paged searching in sql server.

My restrictions are:

1. has to be in a proc.
2. can't use dynamic sql.

Here's my sample query:

DECLARE @TenantId int
DECLARE @MemberStatusId int
DECLARE @SearchTerm varchar(50)
DECLARE @ColumnName varchar(30)
DECLARE @PageNum int
DECLARE @RecCount int
;
WITH RowPages AS (
 SELECT TOP( @PageNum * @RecCount )
 RowNumber = ROW_NUMBER() OVER( ORDER BY 
 CASE
 WHEN @ColumnName = 'Name' THEN P.LastName
 WHEN @ColumnName = 'PhoneNumber' THEN M.PhoneNumber
 WHEN @ColumnName = '' THEN P.LastName
 END ASC,
 CASE 
 WHEN @ColumnName = 'DateAdded' THEN P.DateAdded
 END DESC
 ),
 TotalCount = COUNT(M.MemberId) OVER ( PARTITION BY NULL),
 M.MemberId, M.ExternalId, M.PhoneNumber, P.FirstName, P.LastName, P.EmailAddress
 FROM membership.Members M (nolock)
 INNER JOIN core.Persons P (nolock) on (P.PersonId = M.MemberId)
 WHERE (M.TenantId = @TenantId)
 AND ((@MemberStatusId = 0) or (M.MemberStatusId = @MemberStatusId))
 AND (
 (@SearchTerm = '""')
 OR (CONTAINS( (P.FirstName, P.LastName, P.EmailAddress), @SearchTerm))
 )
)
SELECT TotalCount, MemberId, ExternalId, PhoneNumber,
 FirstName, LastName, EmailAddress
 FROM RowPages
 WHERE RowNumber > ((@PageNum - 1) * @RecCount)
 ORDER BY RowNumber

What this does is perform a paged search of a couple tables. You can pass in the page number and # records per page as well as the column to sort by and it has a full text search component. This is one of the simpler queries of this style we have.

What I'm trying to figure out is how to optimize this. The biggest costs in the execution plan are in regards to the full text search. There is a table value function [FullTextMatch] which has a cost of 100%.

Ideas?

Answer 1

Interestingly, the SP2 to SQL 2008 R2 wacked my search queries. The part where I had SELECT TOP( @PageNum * @RecCount ) now causes it to pull back random values.

I've since done several things. First was that I got rid of the SQL full text search matching. It was ludicrously slow. Second, I removed the TOP(@PageNum * @RecCount) part and in the final select added SELECT TOP(@RecCount) TotalCount, ...

Seems fast and easy.

Also, the following article talks about using a key seek method that appears to be very very fast: http://www.sqlservercentral.com/articles/paging/69892/

Answer 2

It looks pretty good to me. With a cost of 100% on the FullTextMatch, I'd consider running this as a standard statement and hard code the variables just to see what the execution plan says. I do have one question though. Your case statement for @ColumnName = 'DateAdded'... doesn't this throw an error when @ColumnName <> 'DateAdded' as you end up with something like:

order by P.LastName asc, desc

• ChrisLively: Actually, no it doesn't throw an error. I'm not entirely sure how the parser figures it out, but that part works just fine.