4
\$\begingroup\$

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?

\$\endgroup\$
3
\$\begingroup\$

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/

\$\endgroup\$
3
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – NotMe Mar 20 '11 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.