Как вернуть страницу результатов из SQL?

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

Что является хорошим алгоритмом для реализации этого шаблона без передачи всей таблицы клиенту и последующей фильтрации данных на клиенте. Как вы приносите только те записи, которые вы хотите показать пользователю?

Упрощает ли LINQ решение?

13.08.2008 18:38:02
возможный дубликат результатов подкачки SQL Server 2005
Lukas Eder 26.10.2013 18:29:32
8 ОТВЕТОВ
РЕШЕНИЕ

На MS SQL Server 2005 и выше, ROW_NUMBER (), кажется, работает:

T-SQL: пейджинг с ROW_NUMBER ()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;
11
27.01.2013 14:09:44
ROW_NUMBER()эмуляция подкачки или OFFSET .. FETCHусловие SQL Server 2012 может быть довольно медленным для больших номеров страниц: 4guysfromrolla.com/webtech/042606-1.shtml . В этом случае, метод поиска может быть лучшим выбором, поскольку он позволяет осуществлять пейджинг в постоянное время.
Lukas Eder 26.10.2013 18:34:03

На самом деле, LINQ имеет методы Skip и Take, которые можно комбинировать, чтобы выбрать, какие записи будут выбраны.

Проверьте это.

Для БД: разбиение на страницы в SQL Server 2005

1
27.01.2013 14:09:39

Решение Oracle:

select * from (
    select a.*, rownum rnum from (
        YOUR_QUERY_GOES_HERE -- including the order by
    ) a
    where rownum <= MAX_ROW
 ) where rnum >= MIN_ROW
1
13.08.2008 19:00:32

Я бы порекомендовал либо использовать LINQ, либо попытаться скопировать то, что он делает. У меня есть приложение, в котором я использую методы LINQ Take и Skip для извлечения выгружаемых данных. Код выглядит примерно так:

MyDataContext db = new MyDataContext();
var results = db.Products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

Запуск SQL Server Profiler показывает, что LINQ преобразует этот запрос в SQL, например:

SELECT [ProductId], [Name], [Cost], and so on...
FROM (
    SELECT [ProductId], [Name], [Cost], [ROW_NUMBER]
    FROM (
       SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [ROW_NUMBER], 
           [ProductId], [Name], [Cost]
       FROM [Products]
    )
    WHERE [ROW_NUMBER] BETWEEN 10 AND 20
)
ORDER BY [ROW_NUMBER]


Проще говоря : 1. Отфильтруйте строки и используйте функцию ROW_NUMBER, чтобы добавить номера строк в нужном вам порядке.
2. Фильтр (1), чтобы вернуть только те номера строк, которые вы хотите на своей странице.
3. Сортируйте (2) по номеру строки, который соответствует порядку, который вы хотели (в данном случае, по имени).

7
13.08.2008 19:29:28
Вы случайно не знаете, почему LINQ вдвойне вкладывает фактическое SELECTутверждение? Является ли (было?) Тонкая настройка производительности для какой-либо версии SQL Server? Я чувствую, что 2-й уровень может быть объединен с 1-м уровнем.
Lukas Eder 26.10.2013 18:35:38

Есть несколько решений, которые я использую с MS SQL 2005.

Одним из них является ROW_NUMBER (). Но лично мне не нравится ROW_NUMBER (), потому что он не работает для больших результатов (БД, над которой я работаю, действительно большая - более 1 ТБ данных, выполняющих тысячи запросов в секунду - вы знаете - большие социальные сети сайт).

Вот мое любимое решение.

Я буду использовать вид псевдокода T-SQL.

Давайте найдем 2-ую страницу пользователей, отсортированную по имени, фамилии, где на каждой странице по 10 записей.

@page = 2 -- input parameter
@size = 10 -- can be optional input parameter

if @page < 1 then begin
    @page = 1 -- check page number
end
@start = (@page-1) * @size + 1 -- @page starts at record no @start

-- find the beginning of page @page
SELECT TOP (@start)
    @forename = forename,
    @surname = surname
    @id = id
FROM
    users
ORDER BY
    forename,
    surname,
    id -- to keep correct order in case of have two John Smith.

-- select @size records starting from @start
SELECT TOP (@size)
    id,
    forename,
    surname
FROM
    users
WHERE
    (forename = @forename and surname = @surname and id >= @id) -- the same name and surname, but bigger id
    OR (forename = @forename and surname > @surname) -- the same name, but bigger surname, id doesn't matter
    OR (forename > @forename) -- bigger forename, the rest doesn't matter
ORDER BY
    forename,
    surname,
    id
1
15.08.2008 21:44:25

LINQ в сочетании с лямбда-выражениями и анонимными классами в .Net 3.5 значительно упрощает подобные вещи.

Запрос к базе данных:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select c;

Количество записей на странице:

customers = customers.Skip(pageNum * pageSize).Take(pageSize);

Сортировка по любому столбцу:

customers = customers.OrderBy(c => c.LastName);

Получение только выбранных полей с сервера:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select new
                {
                    CustomerID = c.CustomerID,
                    FirstName = c.FirstName,
                    LastName = c.LastName
                };

Это создает статически типизированный анонимный класс, в котором вы можете получить доступ к его свойствам:

var firstCustomer = customer.First();
int id = firstCustomer.CustomerID;

Результаты запросов по умолчанию загружаются лениво, поэтому вы не обращаетесь к базе данных, пока вам не потребуются данные. LINQ в .Net также значительно упрощает обновления, сохраняя текстовые данные обо всех внесенных вами изменениях и обновляя только те поля, которые вы изменили.

3
15.08.2008 22:07:40
Первый оператор, похоже, извлекает все из БД, а затем второй оператор получает подмножество. Что делать, если вы просто хотите подмножество в первую очередь? У меня 90 000 строк, и я просто хочу страницу 4 из 10 строк.
Snowy 8.09.2010 18:35:07
@ScSub LINQ-выражения загружаются лениво, поэтому первый вызов на самом деле сначала ничего не делает. Вы можете позвонить, customers = customers.Skip(30).Take(10)и это только отзовет, что вы хотите.
Adam Lassek 8.09.2010 21:05:25

Существует дискуссия об этом здесь

Техника получает номер страницы 100 000 из базы данных 150 000 строк за 78 мс

Используя знания оптимизатора и SET ROWCOUNT, первый EmployeeID на запрашиваемой странице сохраняется в локальной переменной для начальной точки. Затем установите ROWCOUNT на максимальное количество записей, которое запрашивается в @maximumRows. Это позволяет разбить результирующий набор более эффективным образом. При использовании этого метода также используются преимущества уже существующих индексов в таблице, поскольку она идет непосредственно к базовой таблице, а не к локальной таблице.

Боюсь, я не могу судить, лучше ли это, чем текущий принятый ответ.

0
11.09.2008 16:04:25
Другим очень быстрым подходом является метод поиска , который позволяет выполнять пагинацию в постоянное время (возможно, даже быстрее, чем 78 мс)
Lukas Eder 26.10.2013 18:36:58

Существуют два способа разбивки на страницы в базе данных (я предполагаю, что вы используете SQL Server):

Использование OFFSET

Другие объяснили, как ROW_NUMBER() OVER()функция ранжирования может использоваться для выполнения страниц. Стоит отметить, что SQL Server 2012 наконец-то включил поддержку стандартного OFFSET .. FETCHпредложения SQL :

SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

Если вы используете SQL Server 2012 и обратная совместимость не является проблемой, вам, вероятно, следует предпочесть это предложение, поскольку оно будет выполняться SQL Server более оптимально в угловых случаях.

Использование метода SEEK

Существует совершенно другой, гораздо более быстрый, но менее известный способ выполнения подкачки в SQL. Это часто называют «методом поиска», как описано в этом посте здесь .

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

Значения @previousScoreи @previousPlayerIdявляются соответствующими значениями последней записи с предыдущей страницы. Это позволяет вам получить «следующую» страницу. Если ORDER BYнаправление ASC, просто используйте >вместо этого.

С помощью описанного выше метода вы не можете сразу перейти к странице 4, не предварительно загрузив предыдущие 40 записей. Но часто, вы все равно не хотите прыгать так далеко. Вместо этого вы получаете намного более быстрый запрос, который может извлекать данные в постоянное время, в зависимости от вашей индексации. Кроме того, ваши страницы остаются «стабильными», независимо от того, изменяются ли базовые данные (например, на странице 1, пока вы находитесь на странице 4).

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

Обратите внимание, что «метод поиска» также называется поиском клавиш .

5
23.05.2017 12:24:56