В SQL постраничный вывод результатов поиска


Я пытаюсь выяснить способ, чтобы упростить реализацию постраничный поиск в SQL сервере.

Мои ограничения:

  1. должен быть в прок.
  2. не можете использовать динамический SQL.

Вот мой пример запроса:

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

Это нужно выполнить постраничный поиск пары таблиц. Вы можете пройти в номер страницы и # записей на странице, а также столбец для сортировки и полный компонент текстового поиска. Это один из простых запросов этот стиль у нас.

Что я пытаюсь выяснить, как оптимизировать это. Самые большие затраты в плане исполнения находятся в отношении полнотекстовый поиск. Есть функция таблица значений [FullTextMatch], которая имеет стоимость 100%.

Идеи?



463
4
задан 11 марта 2011 в 11:03 Источник Поделиться
Комментарии
2 ответа

Интересно, что пакет обновления 2 для SQL 2008 R2 в душу мою поисковым запросам. Ту часть, где у меня был выбор топ( @нумерация страниц * @RecCount ) теперь заставляет его отступить случайных величин.

Я уже сделал несколько вещей. Первым было то, что я избавился от полный текст соответствующие запросы SQL. Это было смехотворно медленно. Во-вторых, я удалил ТОП(@нумерация страниц * @RecCount) части и финального выбора добавлен выбор топ(@RecCount) разработана ...

Кажется, быстро и легко.

Кроме того, следующая статья говорит об использовании ключевой метод поиска, который появляется, чтобы быть очень-очень быстро: http://www.sqlservercentral.com/articles/paging/69892/

3
ответ дан 12 марта 2013 в 03:03 Источник Поделиться

Для меня это выглядит довольно хорошо. С себестоимостью 100% на FullTextMatch, я бы попробуйте запустить стандартное заявление и жесткий код переменные, чтобы увидеть, что план выполнения говорит.
Хотя у меня есть один вопрос. Вашем случае заявление для @Имя_столбца = 'DateAdded'... не эта ошибка, когда @Имя_столбца <> 'DateAdded' , как вы в конечном итоге с чем-то вроде:

order by P.LastName asc, desc

3
ответ дан 20 марта 2011 в 12:03 Источник Поделиться