Frage an sql-server-2005, sql-server, sql – Effiziente Methode zum Abrufen von @@ rowcount aus einer Abfrage mit row_number

30

Ich habe eine teure Abfrage mit der Funktion row_number over () in SQL Server 2005. Ich gebe nur eine Unterliste dieser Datensätze zurück, da die Abfrage paginiert ist. Ich möchte jedoch auch die Gesamtzahl der Datensätze zurückgeben, nicht nur die paginierte Teilmenge. Zweimaliges effektives Ausführen der Abfrage, um die Anzahl zu ermitteln, kommt nicht in Frage.

Die Auswahl von count (*) kommt ebenfalls nicht in Frage, da die Leistung absolut schrecklich ist, wenn ich das ausprobiert habe.

Was ich wirklich lieben würde, ist @@ ROW_NUMBERROWCOUNT :-)

Deine Antwort

4   die antwort
0

Ich tue dies, indem ich die gesamte Ergebnismenge mit der row_number in eine temporäre Tabelle lege, dann die @@ rowcount daraus verwende und die Abfrage dazu verwende, um die Seite mit den Daten zurückzugeben, die ich benötige.

36

Sehen Sie sich das COUNT (*) Aggregat an, wenn Sie es mit OVER (PARTITON BY ..) verwenden, wie folgt:

<code>    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns
</code>

Dies ist meiner Meinung nach der beste Weg, dies zu tun, ohne zwei Abfragen durchführen zu müssen.

In der Tat sehr gute Lösung !, obwohl Sie der Abfrage eine Spalte hinzufügen, die Sie nur für die erste Zeile benötigen. Aber ich denke, es ist schneller und verbraucht weniger Ressourcen als die beiden getrennten Abfragen ... Kat Lim Ruiz
Danke, genau das, wonach ich gesucht habe !!! Phil Bennett
36

Im Laufe der Jahre ist ein Haufen Entwickler-Schweiß in effiziente Ergebnismengen für das Blättern geflossen. Es gibt jedoch keine Antwort - es hängt von Ihrem Anwendungsfall ab. Ein Teil des Anwendungsfalls besteht darin, Ihre Seite effizienter zu gestalten und herauszufinden, wie viele Zeilen sich in einer vollständigen Ergebnismenge befinden. Tut mir leid, wenn ich mich ein bisschen ins Paging verirrt habe, aber die beiden sind in meinem Kopf ziemlich eng miteinander verbunden.

Es gibt viele Strategien, von denen die meisten schlecht sind, wenn Sie Datenvolumen haben und nicht zum Anwendungsfall passen. Dies ist zwar keine vollständige Liste, es folgen jedoch einige Optionen .....

Separat ausführenCount(*)Führen Sie eine separate Abfrage aus, die eine einfache "select count (*) from MyTable" ausführt.einfach und leicht für einen kleinen TischGut für eine ungefilterte große Tabelle, die entweder schmal ist oder einen kompakten, nicht gruppierten Index hat, den Sie verwenden könnenbricht zusammen, wenn Sie eine komplizierte habenWHERE/JOIN Kriterien, weil die AusführungWHERE/JOIN zweimal ist teuer.Bricht bei einem breiten Index zusammen, weil die Anzahl der Lesevorgänge steigt.KombinierenROW_Number() OVER() undCOUNT(1) OVER(PARTITION By 1)Dies wurde von @RBarryYoung vorgeschlagen. Es hat den Vorteil, dass es einfach zu implementieren und sehr flexibel ist.Die Kehrseite ist, dass es viele Gründe gibt, die schnell extrem teuer werden können.In einer Datenbank, in der ich gerade arbeite, gibt es beispielsweise eine Medientabelle mit etwa 6000 Zeilen. Es ist nicht besonders breit, hat eine ganzzahlige gruppierte PK und einen kompakten eindeutigen Index. Trotzdem eine einfacheCOUNT(*) OVER(PARTITION BY 1) as TotalRows ergibt ~ 12.000 Lesevorgänge. Vergleichen Sie das mit einem einfachenSELECT COUNT(*) FROM Media - 12 liest. Wowzer.

UPDATE - Das von mir erwähnte Leseproblem ist ein bisschen Red Herring. Es stellt sich heraus, dass bei Fensterfunktionen die Einheit, mit der die Lesevorgänge gemessen werden, gemischt ist. Das Nettoergebnis scheint eine enorme Anzahl von Lesevorgängen zu sein. Sie können mehr zu diesem Thema hier sehen:Warum sind logische Lesevorgänge für Fensteraggregatfunktionen so hoch?

Temporäre Tabellen / TabellenvariablenEs gibt viele Strategien, die eine Ergebnismenge verwenden und relevante Schlüssel oder Segmente von Ergebnissen in temporäre Tabellen / Tabellenvariablen einfügen.Für kleine und mittlere Ergebnismengen kann dies großartige Ergebnisse liefern.Diese Art von Strategie funktioniert auf nahezu jeder Plattform / Version von SQL.Es ist auch einfach, eine Ergebnismenge mehrmals zu bearbeiten (häufig eine Anforderung).Der Nachteil ist, dass das Einfügen einiger Millionen Zeilen in eine temporäre Tabelle Kosten verursacht, wenn Sie mit großen Ergebnismengen arbeiten.Das Problem wird noch dadurch verschärft, dass bei einem hohen Systemvolumen der Druck auf TempDB durchaus ein Faktor sein kann und Temp-Tabellen in TempDB effektiv funktionieren.Gaußsche Summe / doppelte ZeilennummerDiese Idee beruht aufTeilmenge von etwas, was der Mathematiker Gauß herausgefunden hat (wie man eine Reihe von Zahlen summiert). Die Teilmenge gibt an, wie die Zeilenzahl von einem beliebigen Punkt in der Tabelle abgerufen werden kann.Aus einer Reihe von Zahlen (Row_Number()) die Zeilenzahl für 1 bis N ist(N + 1) - 1. Weitere Erklärung in den Links.Die Formel scheint nur N zu sein, aber wenn Sie sich an die Formel halten, passiert etwas Interessantes, und Sie können die Zeilenzahl auf einer Seite in der Mitte der Tabelle ermitteln.Das Nettoergebnis ist SieROW_Number() OVER(Order by ID) undROW_Number() OVER(Order by ID DESC) dann summiere die beiden Zahlen und subtrahiere 1.Am Beispiel meiner Medientabelle gingen meine Lesezahlen von 12.000 auf etwa 75 zurück.Auf einer größeren Seite haben Sie Daten viele Male wiederholt, aber der Versatz bei Lesevorgängen kann sich lohnen.Ich habe dies nicht in zu vielen Szenarien getestet, so dass es in anderen Szenarien auseinanderfallen kann.Top (@n) / SET ROWCOUNTDies sind an sich keine spezifischen Strategien, sondern Optimierungen, die auf dem Wissensstand des Abfrageoptimierers basieren.Kreative Verwendung von Top (@n) [top kann eine Variable in SQL 2008 sein] oder SET ROWCOUNT kann Ihre Arbeitsmenge reduzieren ... selbst wenn Sie eine mittlere Seite einer Ergebnismenge ziehen, können Sie das Ergebnis dennoch eingrenzenDiese Ideen funktionieren aufgrund des Verhaltens des Abfrageoptimierers. Ein Service Pack / Hotfix kann das Verhalten ändern (obwohl dies wahrscheinlich nicht der Fall ist).In bestimmten Fällen kann SET ROWCOUNT etwas ungenau seinDiese Strategie berücksichtigt nicht die vollständige Zeilenanzahl, sondern macht das Paging effizienterWas muss ein Entwickler tun?

Lesen Sie, mein guter Mann, lesen Sie. Hier sind einige Artikel, auf die ich mich gestützt habe ...

Eine effizientere Methode zum Blättern durch große ErgebnismengenOptimieren des serverseitigen Paging - Teil IOptimieren des serverseitigen Paging - Teil IIErklärung der Gaußschen SummeZurückgeben von bewerteten Ergebnissen mit Microsoft SQL Server 2005ROW_NUMBER () OVER Nicht schnell genug mit großer ErgebnismengeAbrufen der ersten N Datensätze aus einer SQL-AbfrageServerseitiges Paging mit SQL Server 2005Warum sind logische Lesevorgänge für Fensteraggregatfunktionen so hoch?

Hoffentlich hilft das.

Wow, tolles Detail! Dies sollte die Antwort sein (obwohl es dir nicht DAS gibt :)) Kat Lim Ruiz
@Martin - Ich habe auch nicht alles geklärt, aber ein paar Dinge bemerkt. Erstens, ich vermute, Sie sehen in Memory Reads (d. H. Ihre Tabelle ist zwischengespeichert). Auf einem größeren Datensatz würde die Leistung implodieren. Zweitens scheint es, den Ausführungsplan für jede Fensterfunktion zu verzweigen (als hätten Sie einen Join durchgeführt). Drittens werden häufig viele wichtige Suchanfragen ausgeführt. Weitere Informationen finden Sie unter "Erste Lösung" und "Alternative Methoden" im Artikel "Optimieren des serverseitigen Paging - Teil I". EBarr
@Martin - habe gerade die Antwort gesehen. Danke für die Information. EBarr
Ja, sie sind definitiv in Erinnerung. Es füllt die Spool und verwendet dieselbe Spool mit Zurückspulen und Zurückbinden in den anderen Bereichen des Plans. Martin Smith
4

Wenn count (*) langsam ist, müssen Sie dieses Problem zuerst beheben, indem Sie Ihre Indizes sorgfältig untersuchen und sicherstellen, dass Ihre Statistiken auf dem neuesten Stand sind.

Nach meiner Erfahrung gibt es nichts Besseres als zwei separate Abfragen, eine zum Abrufen der Datenseite und eine zum Abrufen der Gesamtanzahl. Die Verwendung einer temporären Tabelle, um die Gesamtanzahl zu ermitteln, ist eine verlierende Strategie, wenn die Anzahl der Zeilen zunimmt. Zum Beispiel sind die Kosten für das Einfügen von 10.000.000.000 Zeilen in eine temporäre Tabelle, um sie zu zählen, offensichtlich zu hoch.

Obwohl ich Ihnen vollkommen zustimme, ist diese Abfrage komplex und ich muss in diesem Fall den Weg der Optimierung beschreiten. Phil Bennett

Verwandte Fragen