In Webanwendungen ist die Paginierung einer großen Datenbank-Ergebnismenge ein bekanntes Problem. Einfach ausgedrückt: Sie möchten nicht, dass alle Abfragedaten auf einer einzigen Seite angezeigt werden. Daher ist die Anzeige mit Paging besser geeignet. Obwohl dies in herkömmlichem ASP keine einfache Aufgabe ist, vereinfacht das DataGrid-Steuerelement in ASP.NET diesen Prozess auf nur wenige Codezeilen. Daher ist das Paging in asp.net sehr einfach, aber das standardmäßige DataGrid-Paging-Ereignis liest alle Datensätze aus der Datenbank und fügt sie in die asp.net-Webanwendung ein. Wenn Sie mehr als eine Million Daten haben, führt dies zu ernsthaften Leistungsproblemen (wenn Sie das nicht glauben, können Sie eine Abfrage in Ihrer Anwendung ausführen und den Speicherverbrauch von aspnet_wp.exe im Task-Manager überprüfen). Dies ist der Grund Es ist notwendig, das Paging-Verhalten anzupassen, um sicherzustellen, dass nur die Datensätze abgerufen werden, die für die aktuelle Seite erforderlich sind.
Es gibt viele Artikel und Beiträge zu diesem Problem im Internet sowie einige ausgereifte Lösungen. Mein Zweck beim Schreiben dieses Artikels besteht nicht darin, Ihnen eine gespeicherte Prozedur zu zeigen, die alle Ihre Probleme löst, sondern darin, vorhandene Methoden zu optimieren und Ihnen eine Anwendung zum Testen zur Verfügung zu stellen, damit Sie diese entsprechend Ihren Anforderungen durchführen können.
Allerdings bin ich mit den derzeit online angebotenen Methoden nicht sehr zufrieden. Zunächst wird traditionelles ADO verwendet, das offensichtlich für „altes“ ASP geschrieben ist. Bei den übrigen Methoden handelt es sich um gespeicherte SQL Server-Prozeduren, und einige von ihnen sind aufgrund zu langsamer Antwortzeiten unbrauchbar, wie Sie an den Leistungsergebnissen am Ende des Artikels sehen können, aber es gibt einige, die meine Aufmerksamkeit erregt haben.
Verallgemeinerung:
Ich möchte die drei derzeit häufig verwendeten Methoden sorgfältig analysieren: temporäre Tabellen (TempTable), dynamisches SQL (DynamicSQL) und Zeilenanzahl (Rowcount). Im Folgenden nenne ich die zweite Methode lieber die (aufsteigend-absteigende) Asc-Desc-Methode. Ich glaube nicht, dass dynamisches SQL ein guter Name ist, da Sie dynamische SQL-Logik auch in einer anderen Methode anwenden können. Das gemeinsame Problem all dieser gespeicherten Prozeduren besteht darin, dass Sie abschätzen müssen, nach welchen Spalten Sie sortieren möchten, und nicht nur nach den Primärschlüsselspalten (PK-Spalten), was zu einer Reihe von Problemen führen kann – Sie müssen dies für jede Abfrage tun Zeigen Sie es durch Paging an, was bedeutet, dass Sie für jede verschiedene Sortierspalte viele verschiedene Paging-Abfragen haben müssen, was bedeutet, dass Sie entweder für jede Sortierspalte eine andere gespeicherte Prozedur ausführen (unabhängig davon, welche Paging-Methode verwendet wird) oder Sie müssen Fügen Sie diese Funktionalität mithilfe von dynamischem SQL in eine gespeicherte Prozedur ein. Diese beiden Methoden haben einen geringen Einfluss auf die Leistung, erhöhen jedoch die Wartbarkeit, insbesondere wenn Sie diese Methode zum Anzeigen verschiedener Abfragen verwenden müssen. Daher werde ich in diesem Artikel versuchen, dynamisches SQL zu verwenden, um alle gespeicherten Prozeduren zusammenzufassen. Aus bestimmten Gründen können wir jedoch nur eine teilweise Universalität erreichen, sodass Sie für komplexe Abfragen immer noch unabhängige gespeicherte Prozeduren schreiben müssen.
Das zweite Problem beim Zulassen aller Sortierfelder, einschließlich Primärschlüsselspalten, besteht darin, dass keine dieser Methoden hilfreich ist, wenn diese Spalten nicht ordnungsgemäß indiziert sind. Bei all diesen Methoden muss zuerst eine Paging-Quelle sortiert werden. Bei großen Datentabellen ist der Aufwand für die Sortierung mithilfe von Nicht-Index-Spalten vernachlässigbar. In diesem Fall können aufgrund der langen Antwortzeit nicht alle gespeicherten Prozeduren in tatsächlichen Situationen verwendet werden. (Die entsprechende Zeit variiert je nach Größe der Tabelle und dem ersten abzurufenden Datensatz zwischen einigen Sekunden und einigen Minuten.) Indizes für andere Spalten können zu zusätzlichen unerwünschten Leistungsproblemen führen. Beispielsweise kann es sehr langsam werden, wenn Sie jeden Tag viele Daten importieren.
Temporäre Tabelle
Zunächst werde ich über die Methode der temporären Tabelle sprechen. Dies ist eine weithin empfohlene Lösung, auf die ich in meinen Projekten mehrmals gestoßen bin. Werfen wir einen Blick auf das Wesentliche dieser Methode:
CREATE TABLE #Temp(
ID int IDENTITÄT PRIMÄRSCHLÜSSEL,
PK /*hieregoesPKtype*/
)
INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn
SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow
durch Kopieren aller Zeilen nach temporär In der Tabelle können wir die Abfrage weiter optimieren (SELECT TOP EndRow...), aber der Schlüssel ist das Worst-Case-Szenario – eine Tabelle mit 1 Million Datensätzen generiert eine temporäre Tabelle mit 1 Million Datensätzen.
Angesichts dieser Situation und der Betrachtung der Ergebnisse des obigen Artikels habe ich beschlossen, in meinem Test auf dieaufsteigend-absteigende
Methode zu verzichten.
Diese Methode verwendet die Standardsortierung in der Unterabfrage und die umgekehrte Sortierung in der Hauptabfrage.
DECLARE @temp TABLE(
PK /* PKType */
NICHT NULL PRIMÄR
)
INSERT INTO @temp SELECT TOP @PageSize PK FROM
(
SELECT TOP(@StartRow + @PageSize)
PK,
SortColumn /* Wenn sich die Sortierspalte vom PK unterscheidet, muss SortColumn dies tun
ebenfalls abgeholt werden, ansonsten ist nur der PK notwendig
*/
ORDER BY SortColumn
/*
Standardreihenfolge – normalerweise ASC
*/
)
ORDER BY SortColumn
/*
umgekehrte Standardreihenfolge – normalerweise DESC
*/
SELECT FROM Table JOIN @Temp temp ON Table .PK= temp .PK
ORDER BY SortColumn
/*
Standardbestellung
*/
Zeilenzählung
basiert auf dem SET ROWCOUNT-Ausdruck in SQL, sodass unnötige Zeilen übersprungen und die erforderlichen Zeilendatensätze erhalten werden können:
DECLARE @Sort /* der Typ der Sortierspalte */
SET ROWCOUNT @ StartRow
SELECT @Sort=SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
Es gibt zwei weitereUnterabfragemethoden
, die ich in Betracht gezogen habe, und ihre Quellen sind unterschiedlich. Die erste ist die bekannte Triple Query- oder Selbstabfragemethode. In diesem Artikel verwende ich auch eine ähnliche gemeinsame Logik, die alle anderen gespeicherten Prozeduren umfasst. Die Idee hier ist, eine Verbindung zum gesamten Prozess herzustellen. Ich habe einige Reduzierungen am ursprünglichen Code vorgenommen, da die Datensatzanzahl in meinen Tests nicht benötigt wird)
SELECT FROM Table WHERE PK IN(
SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
(
SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
ORDER BY SortColumn)
ORDER BY SortColumn
Cursor
Beim Durchsuchen der Google-Diskussionsgruppe bin ich auf die letzte Methode gestoßen. Diese Methode verwendet einen serverseitigen dynamischen Cursor. Viele Leute versuchen, die Verwendung von Cursorn zu vermeiden, da sie aufgrund ihrer Ordnungsmäßigkeit irrelevant und ineffizient sind. Rückblickend ist das Paging jedoch eine geordnete Aufgabe. Egal welche Methode Sie verwenden, Sie müssen zur Startzeile zurückkehren. Bei der vorherigen Methode wählen Sie zunächst alle Zeilen vor Beginn der Aufzeichnung aus, fügen die erforderlichen Zeilen für die Aufzeichnung hinzu und löschen dann alle vorherigen Zeilen. Dynamische Cursor verfügen über eine FETCH RELATIVE-Option, die magische Sprünge ausführt. Die Grundlogik ist wie folgt:
DECLARE @PK /* PKType */
DECLARE @tblPK
TABLE(
PK /*PKType*/ NICHT NULL PRIMÄRSCHLÜSSEL
)
DECLARE PagingCursor CURSOR DYNAMICREAD_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK
WHILE @PageSize>0 AND @@FETCH_STATUS =0
BEGINNEN
INSERT @tblPK(PK) VALUES(@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
ENDE
SCHLIESSEN
PagingCursor
ZUORDNUNG AUFHEBEN
PagingCursor
SELECT FROM Table JOIN @tblPK temp ON Table .PK= temp .PK
Generalisierung komplexer Abfragen
in ORDER BY SortColumn
Ich habe zuvor darauf hingewiesen, dass alle gespeicherten Prozeduren dynamisches SQL verwenden, um eine Generalisierung zu erreichen, sodass sie theoretisch jede Art komplexer Abfragen verwenden können. Nachfolgend finden Sie ein Beispiel für eine komplexe Abfrage basierend auf der Northwind-Datenbank.
SELECT Customers.ContactName AS Customer, Customers.Address + ' , ' + Customers.City + ', '+ Customers.Country
AS-Adresse, SUM([OrderDetails].UnitPrice*[OrderDetails] .Quantity)
AS [Totalmoneyspent]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GRUPPE NACH „Kunden.Kontaktname“, „Kunden.Adresse“, „Kunden.Stadt“, „Kunden.Land“.
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000
ORDER BY Customer DESC ,Address DESC
gibt den Paging-Speicheraufruf der zweiten Seite wie folgt zurück:
EXEC ProcedureName
/*Tables */
'
Kunden
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* PK */
'
Kunden.Kunden-ID
'
,
/* ORDERBY */
'
Customers.ContactName DESC,Customers.AddressDESC
'
,
/*Seitennummer */
2
,
/*PageSize */
10
,
/*Felder */
'
Kunden.Kontaktname AS Kunde,
Customers.Address+'' , '' +Customers.City+ '' , '' +Customers.Country ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,
/*Filter */
'
Customers.Country<>'' USA '' ANDCustomers.Country<> '' Mexiko ''' ,
/*GroupBy */
'
Customers.CustomerID,Customers.ContactName,Customers.Address,
Kunden.Stadt,Kunden.Land
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000
'
Es ist erwähnenswert, dass Sie in der ursprünglichen Abfrage Aliase in der ORDER BY-Anweisung verwendet haben. Sie sollten dies jedoch nicht in einer ausgelagerten gespeicherten Prozedur tun, da das Überspringen von Zeilen vor Beginn der Aufzeichnung zeitaufwändig ist. Tatsächlich gibt es viele Implementierungsmethoden, aber das Prinzip besteht nicht darin, alle Felder am Anfang einzubeziehen, sondern nur die Primärschlüsselspalte (entspricht der Sortierspalte in der RowCount-Methode) einzuschließen, was den Abschluss beschleunigen kann Aufgabe. Erst auf der Anfrageseite werden alle Pflichtfelder abgerufen. Darüber hinaus gibt es in der endgültigen Abfrage keine Feldaliase, und bei Abfragen zum Überspringen von Zeilen müssen Indexspalten im Voraus verwendet werden.
Es gibt ein weiteres Problem mit der gespeicherten Prozedur RowCount: In der ORDER BY-Anweisung ist nur eine Spalte zulässig. Dies ist auch ein Problem mit der aufsteigend-absteigenden Methode, obwohl sie mehrere Spalten sortieren können Es muss darauf geachtet werden, dass im Primärschlüssel nur ein Feld vorhanden ist. Ich denke, das könnte mit dynamischerem SQL gelöst werden, aber meiner Meinung nach lohnt es sich nicht. Obwohl solche Situationen möglich sind, kommen sie nicht sehr oft vor. Normalerweise können Sie die oben genannten Prinzipien auch verwenden, um die gespeicherten Prozeduren unabhängig voneinander zu blättern.
Leistungstests
Beim Testen habe ich vier Methoden verwendet. Wenn Sie eine bessere Methode haben, würde mich das gerne wissen. Wie auch immer, ich muss diese Methoden vergleichen und ihre Leistung bewerten. Zunächst besteht meine erste Idee darin, eine asp.net-Testanwendung zu schreiben, die ein Paging-DataGrid enthält, und dann die Seitenergebnisse zu testen. Dies spiegelt natürlich nicht die tatsächliche Antwortzeit der gespeicherten Prozedur wider, sodass eine Konsolenanwendung besser geeignet ist. Ich habe auch eine Webanwendung eingefügt, aber nicht für Leistungstests, sondern als Beispiel für die Zusammenarbeit von benutzerdefinierter DataGrid-Paginierung und gespeicherten Prozeduren.
Im Test habe ich eine automatisch generierte große Datentabelle verwendet und etwa 500.000 Daten eingefügt. Wenn Sie nicht über eine solche Tabelle zum Experimentieren verfügen, können Sie hier klicken, um einen Tabellenentwurf und ein gespeichertes Prozedurskript zum Generieren von Daten herunterzuladen. Anstatt eine automatisch inkrementierende Primärschlüsselspalte zu verwenden, habe ich einen eindeutigen Bezeichner verwendet, um den Datensatz zu identifizieren. Wenn ich das oben erwähnte Skript verwende, könnten Sie erwägen, nach der Generierung der Tabelle eine Spalte für die automatische Inkrementierung hinzuzufügen. Dies bedeutet auch, dass Sie beabsichtigen, eine paginierte gespeicherte Prozedur zu verwenden mit Primärschlüsselsortierung, um die Daten der aktuellen Seite zu erhalten.
Um den Leistungstest zu implementieren, habe ich eine bestimmte gespeicherte Prozedur mehrmals über eine Schleife aufgerufen und dann die durchschnittliche Antwortzeit berechnet. Unter Berücksichtigung von Caching-Gründen ist die Zeit, die dieselbe Seite benötigt, um Daten für mehrere Aufrufe einer gespeicherten Prozedur abzurufen, normalerweise nicht für die Auswertung geeignet, um die tatsächliche Situation genauer zu modellieren. Die für jeden Aufruf angeforderte Seitennummer sollte zufällig sein. Natürlich müssen wir davon ausgehen, dass die Anzahl der Seiten fest ist, 10 bis 20 Seiten, und Daten mit unterschiedlichen Seitenzahlen möglicherweise viele Male, aber zufällig, abgerufen werden.
Wir können leicht erkennen, dass die Antwortzeit durch den Abstand der zu erhaltenden Seitendaten von der Startposition der Ergebnismenge bestimmt wird. Je weiter von der Startposition der Ergebnismenge entfernt, desto mehr Datensätze werden vorhanden sein übersprungen. Dies ist auch der Grund, warum ich die Top 20 nicht in meine Zufallsreihenfolge einbeziehe. Alternativ würde ich 2^n Seiten verwenden und die Größe der Schleife ist die Anzahl der benötigten verschiedenen Seiten * 1000, sodass jede Seite fast 1000 Mal abgerufen wird (aus zufälligen Gründen wird es definitiv eine Abweichung geben).
Ergebnisse
hier sind meine Testergebnisse: 
Fazit:
Die Tests wurden in der Reihenfolge von der besten zur schlechtesten Leistung durchgeführt – Zeilenanzahl, Cursor, aufsteigend-absteigend, Unterabfrage. Eine interessante Sache ist, dass die Leute die Seiten nach den ersten fünf Seiten normalerweise nur noch selten besuchen. Daher kann die Unterabfragemethode in diesem Fall Ihren Anforderungen entsprechen, abhängig von der Größe Ihres Ergebnissatzes und der Entfernung, in der er die Häufigkeit des Vorkommens der Seite vorhersagt , werden Sie wahrscheinlich auch eine Kombination dieser Methoden verwenden. Wenn ich es wäre, würde ich auf jeden Fall die Zeilenzählungsmethode bevorzugen, sie funktioniert ziemlich gut, sogar für die erste Seite. Das „jeder Fall“ stellt hier einige Fälle dar, in denen eine Verallgemeinerung schwierig ist. In diesem Fall würde ich sie verwenden ein Cursor. (Ich würde wahrscheinlich die Unterabfragemethode für die ersten beiden verwenden und danach die Cursormethode.)