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

Einen guten Überblick über die verschiedenen Auslagerungstechniken gibt es unter http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

Ich habe ROWCOUNT-Methode ziemlich oft vor allem mit SQL Server 2000 verwendet (wird mit 2005 & 2008 zu arbeiten, nur messen Leistung im Vergleich zu ROW_NUMBER), es ist blitzschnell, aber Sie müssen sicherstellen, dass die sortierte Spalte (n) haben (meist) eindeutige Werte.

Answer 2

Für SQL Server 2000 können Sie ROW_NUMBER() durch eine Tabellenvariable mit einer IDENTITY-Spalte simulieren:

DECLARE @pageNo int -- 1 based
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20

DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1   -- 1020

DECLARE @orderedKeys TABLE (
  rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
  TableKey int NOT NULL
)

SET ROWCOUNT @lastRecord
INSERT INTO @orderedKeys (TableKey) SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

SET ROWCOUNT 0

SELECT t.*
FROM Orders t
  INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum

Dieser Ansatz kann auf Tabellen mit mehrspaltigen Schlüsseln ausgeweitet werden und hat nicht den Performance-Overhead der OR-Verarbeitung (die die Verwendung von Indizes überspringt). Der Nachteil ist die Menge an temporärem Speicherplatz, die verbraucht wird, wenn der Datensatz sehr groß ist und man sich in der Nähe der letzten Seite befindet. Ich habe die Leistung des Cursors in diesem Fall nicht getestet, aber es könnte besser sein.

Beachten Sie, dass dieser Ansatz für die erste Seite der Daten optimiert werden könnte. Außerdem wurde ROWCOUNT verwendet, da TOP in SQL Server 2000 keine Variable akzeptiert.

Answer 3

Der beste Weg für Paging in Sql Server 2012 ist durch die Verwendung von Offset und fetch next in einer gespeicherten Prozedur. OFFSET Schlüsselwort - Wenn wir Offset mit der Order-By-Klausel verwenden, überspringt die Abfrage die Anzahl der Datensätze, die wir in OFFSET n Rows angegeben haben.

FETCH NEXT Schlüsselwörter - Wenn wir Fetch Next mit einer Order-By-Klausel verwenden, wird nur die Anzahl der Zeilen zurückgegeben, die Sie im Paging anzeigen möchten, ohne Offset wird SQL einen Fehler erzeugen. Hier ist das unten angegebene Beispiel.

create procedure sp_paging
(
 @pageno as int,
 @records as int
)
as
begin
declare @offsetcount as int
set @offsetcount=(@pageno-1)*@records
select id,bs,variable from salary order by id offset @offsetcount rows fetch Next @records rows only
end

können Sie es wie folgt ausführen.

exec sp_paging 2,3

Answer 4

Die folgenden Anwendungsfälle scheinen einfach und schnell zu sein. Legen Sie einfach die Seitenzahl fest.

use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6;
with result as(
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
)
select SalesOrderDetailID, SalesOrderID, ProductID from result
WHERE result.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)

auch ohne CTE

use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
 ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)

Answer 5

Ab 2012 können wir verwenden OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY