Wie lassen sich die Ergebnisse in SQL Server am besten paginieren?

Question

Was ist der beste Weg (Leistung wise) zu paginieren Ergebnisse in SQL Server 2000, 2005, 2008, 2012, wenn Sie auch die Gesamtzahl der Ergebnisse (vor paginieren) erhalten möchten?

Answer 1

Endlich, Microsoft SQL Server 2012 veröffentlicht wurde, gefällt mir die Einfachheit der Paginierung sehr, man muss keine komplexen Abfragen verwenden, wie sie hier beantwortet werden.

Um die nächsten 10 Zeilen zu erhalten, führen Sie einfach diese Abfrage aus:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned

Wichtige Punkte, die bei der Verwendung zu beachten sind:

• ORDER BY ist zwingend zu verwenden OFFSET ... FETCH Klausel.
• OFFSET Klausel ist obligatorisch bei FETCH . Sie können nicht verwenden ORDER BY ... FETCH .
• TOP kann nicht kombiniert werden mit OFFSET y FETCH in derselben Abfrage Ausdruck.

Answer 2

Die Ermittlung der Gesamtzahl der Ergebnisse und das Paginieren sind zwei unterschiedliche Vorgänge. Für dieses Beispiel nehmen wir an, dass die Abfrage, mit der Sie zu tun haben, lautet

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In diesem Fall würden Sie die Gesamtzahl der Ergebnisse mit ermitteln:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...was ineffizient erscheinen mag, aber eigentlich ziemlich performant ist, vorausgesetzt, alle Indizes usw. sind richtig eingerichtet.

Um die tatsächlichen Ergebnisse in Form von Auslagerungen zu erhalten, wäre die folgende Abfrage am effizientesten:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Dadurch werden die Zeilen 1-19 der ursprünglichen Abfrage zurückgegeben. Das Tolle daran, insbesondere für Webanwendungen, ist, dass Sie außer den Zeilennummern, die zurückgegeben werden sollen, keinen Status behalten müssen.

Answer 3

Unglaublicherweise wurde in keiner anderen Antwort die schnellste Möglichkeit der Paginierung in allen SQL Server-Versionen. Offsets können bei großen Seitenzahlen furchtbar langsam sein, ebenso wie hier im Benchmarking . Es gibt einen ganz anderen, viel schnelleren Weg, die Paginierung in SQL durchzuführen. Dies wird oft als "seek method" oder "keyset pagination" bezeichnet, wie beschrieben in diesen Blogbeitrag hier .

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

Das "Suchprädikat"

Le site @previousScore y @previousPlayerId Werte sind die jeweiligen Werte des letzten Datensatzes der vorherigen Seite. So können Sie die "nächste" Seite abrufen. Wenn die ORDER BY Richtung ist ASC verwenden Sie einfach > stattdessen.

Mit der obigen Methode können Sie nicht sofort zu Seite 4 springen, ohne vorher die vorherigen 40 Datensätze abgerufen zu haben. Aber oft will man ohnehin nicht so weit springen. Stattdessen erhalten Sie eine viel schnellere Abfrage, die je nach Indizierung die Daten in konstanter Zeit abrufen kann. Außerdem bleiben Ihre Seiten "stabil", unabhängig davon, ob sich die zugrunde liegenden Daten ändern (z. B. auf Seite 1, während Sie auf Seite 4 sind).

Dies ist der beste Weg, um eine Paginierung zu implementieren, wenn z. B. mehrere Daten in Webanwendungen langsam geladen werden sollen.

Hinweis: Die "Suchmethode" wird auch als Keyset-Paginierung .

Gesamtzahl der Datensätze vor der Paginierung

Le site COUNT(*) OVER() können Sie die Gesamtzahl der Datensätze "vor der Paginierung" zählen. Wenn Sie SQL Server 2000 verwenden, müssen Sie auf zwei Abfragen zurückgreifen, um die COUNT(*) .

Answer 4

Ab SQL Server 2012 können wir Folgendes verwenden OFFSET y FETCH NEXT Klausel, um die Paginierung zu erreichen.

Versuchen Sie dies, für SQL Server:

In SQL Server 2012 wurde eine neue Funktion in der ORDER BY-Klausel hinzugefügt, zur Abfrageoptimierung eines Datensatzes, was die Arbeit mit Daten erleichtert für jeden, der in T-SQL schreibt, und auch für den gesamten Ausführungs Plan in SQL Server.

Nachfolgend das T-SQL-Skript mit der gleichen Logik wie in der vorherigen Beispiel.

--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10 
SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;

TechNet: Paging einer Abfrage mit SQL Server

Answer 5

MSDN: ROW_NUMBER (Transact-SQL)

Gibt die fortlaufende Nummer einer Zeile innerhalb einer Partition einer Ergebnismenge zurück, beginnend bei 1 für die erste Zeile in jeder Partition.

Das folgende Beispiel gibt Zeilen mit den Nummern 50 bis einschließlich 60 in der Reihenfolge des Bestelldatums zurück.

WITH OrderedOrders AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber, 
        FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
    FROM [dbo].[vSalesPerson]
) 
SELECT RowNumber, 
    FirstName, LastName, Sales YTD 
FROM OrderedOrders 
WHERE RowNumber > 50 AND RowNumber < 60;

  RowNumber FirstName    LastName               SalesYTD
  --- -----------  ---------------------- -----------------
  1   Linda        Mitchell               4251368.54
  2   Jae          Pak                    4116871.22
  3   Michael      Blythe                 3763178.17
  4   Jillian      Carson                 3189418.36
  5   Ranjit       Varkey Chudukatil      3121616.32
  6   José         Saraiva                2604540.71
  7   Shu          Ito                    2458535.61
  8   Tsvi         Reiter                 2315185.61
  9   Rachel       Valdez                 1827066.71
  10  Tete         Mensa-Annan            1576562.19
  11  David        Campbell               1573012.93
  12  Garrett      Vargas                 1453719.46
  13  Lynn         Tsoflias               1421810.92
  14  Pamela       Ansman-Wolfe           1352577.13