I'm trying to figure out a way to simplify implementing paged searching in sql server.
My restrictions are:
- has to be in a proc.
- 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?
