SQL-Performanceeinbruch bei SELECT

[alzaimar]

Hi Leute,

Ich habe hier eine DB mit ca. 50 Tabellen. Zentrale Tabelle sind Aufträge (ca. 300.000 Stück). An jedem Auftrag hängen noch diverse Kindtabellen, alles schön nach 3NF.

Nun wollen wir einfach mal eine Auftragsübersicht erstellen: Also Haupttabelle + diverse Joins und left joins.

Ergebnis: Für 3000 Zeilen braucht der Server 30-40 Sekunden :shock:

Ich habe schon alles durchprobiert, den Query Analyzer angeworfen, den Execution Plan analysiert, den Index-Tuning-Wizzard angeworfen: Alles Fehlanzeige, will sagen: Überall schöne [Clustered] Index Scans oder Seeks, kein Table Scan etc.

Es kann doch nicht sein, das so ein etwas komplexeres SELECT einen MSSQL in die Knie zwingt?

so in etwa sieht die View aus:

SQL-Code:

select [150 Felder]
from [order] o
   left join OrderProperty op on o.orID = op.orID
   join customer c on o.cuID = c.cuID
   join CustomerAddress ca on ca.cuID = c.cuID and ca.catype = 0
        join Address a on a.adID = ca.adID
   left join Carrier cr on cr.crID = o.crID
   left join CarrierProperties cp on cp.crID = o.orPartnercrID
   left join OrderPrices op3 on op3.orID = o.orID  and op3.pmID in (10,13,14)
   left join OrderPricemodelparameter op50 on op50.orID = o.orID and op50.ppID = 50
   left join OrderPricemodelparameter op79 on op79.orID = o.orID and op79.ppID = 79
   left join OrderPricemodelparameter op36 on op36.orID = o.orID and op36.ppID = 36
   left join OrderPricemodelparameter op53 on op53.orID = o.orID and op53.ppID = 53
   left join OrderPricemodelparameter op38 on op38.orID = o.orID and op38.ppID = 38
   left join OrderPricemodelparameter op39 on op39.orID = o.orID and op39.ppID = 39
   left join OrderPricemodelparameter op40 on op40.orID = o.orID and op40.ppID = 40
   Left join (OrderAddress oa1 join Address ao1 on ao1.adID = oa1.adID) on oa1.orID = o.orID and oa4.oaType = 0  
   Left join (OrderAddress oa2 join Address ao2 on ao2.adID = oa2.adID) on oa2.orID = o.orID and oa4.oaType = 1  
   Left join (OrderAddress oa3 join Address ao3 on ao3.adID = oa3.adID) on oa3.orID = o.orID and oa3.oaType = 2 
   Left join (OrderAddress oa4 join Address ao4 on ao4.adID = oa4.adID) on oa4.orID = o.orID and oa4.oaType = 3  
   Left join (OrderAddress oa5 join Address ao5 on ao5.adID = oa5.adID) on oa5.orID = o.orID and oa5.oaType = 4  
   Left join (OrderAddress oa6 join Address ao6 on ao6.adID = oa6.adID) on oa6.orID = o.orID and oa6.oaType = 5  
   Left join (OrderAddress oa7 join Address ao7 on ao7.adID = oa7.adID) on oa7.orID = o.orID and oa7.oaType = 6
   join inlinevariables on icID = 3

Die Tabelle [order] hat ca. 300.000 Einträge, OrderPricemodelparameter hat ca. 3.2 Mio, die OrderAddress sind ca. 800.000 so in dem Dreh.

Meine Frage lautet nun: Sind die 30-40 Sekunden (für ca. 3000 Datensätze) 'normal'? Das SELECT-Kriterium läuft über einen Index der Tabelle 'Order'.

[Bernhard Geyer]

Das dumme am MS SQL-Server ist das er bis zur Version 2005 kein Multi-Version-Konzept unterstützte und bei ungünstiger Konstellation erst die Abfrage komplett abarbeiten mußte bevor irgendein Ergebnis geliefert wurde.

Ebenfalls kann bei dieser DB-Größe Speicher ein Problem sein. Falls die Indize nicht komplett im RAM gehalten werden können kann es Performanceeinbrüche geben. bei der größe könnte ich mir schon vorstellen das ein paar GB RAM sinnvoll sind.

[Jelly]

Wieviele Datensätze liefert dir denn die ganze Abfrage. Das kann schon sein dass das etwas dauert.

Kleine Bemerkung am Rande: Man sollte left joins nie vor inner joins schreiben. Da können falsche Ergebnisse rauskommen.

Kannst du das hier:

SQL-Code:

left join OrderPricemodelparameter op50 on op50.orID = o.orID and op50.ppID = 50 
   left join OrderPricemodelparameter op79 on op79.orID = o.orID and op79.ppID = 79 
   left join OrderPricemodelparameter op36 on op36.orID = o.orID and op36.ppID = 36 
   left join OrderPricemodelparameter op53 on op53.orID = o.orID and op53.ppID = 53 
   left join OrderPricemodelparameter op38 on op38.orID = o.orID and op38.ppID = 38 
   left join OrderPricemodelparameter op39 on op39.orID = o.orID and op39.ppID = 39 
   left join OrderPricemodelparameter op40 on op40.orID = o.orID and op40.ppID = 40

nicht irgendwie zusammenfassen in einen Join, und das 2. Filterkriterium in der Where Clause unterbringen?

[hoika]

Hallo,

so viele left joins ...
Das sind doch left outer joins ?

Kann schon sein, dass der ms sql damit ein Problem hat
(Firebird auf jeden Fall).

Das Zwischenergebnis der Abfrage kann schon etwas gross sein
(im Speicher).

Kannst du die left joins nicht durch joins ersetzen ?

Dazu musst du natürlich in der DB / Logik etwas ändern.

z.B. OrderPricemodelparameter op50
Left join nimmst du ja, weil vielleicht was drinstehen kann,
aber nicht muss, ein inner join würde ja dann ja den kompletten Auftrag verschwinden lassen.

Wenn es jetzt aber genau einen Eintrag in der op50 (jaja, es ist immer die gleiche Tabelle)
für jeden Auftrag gibt, der entweder ein NULL (nicht da) oder einen richtigen Wert
enthält, kannst du einen inner join benutzen.


Eine andere Möglichkeit wäre eine Aufsplittung der Query (je left join eine)
und ein manuelles Zusammenbauen per Code.
Die Anzeige könnte in einem StringGrid erfolgen.


Heiko

[bttb930]

Man kann bei MS SQL Server in der Abfrage mitteilen, welcher Index benutzt werden soll. Das kann dramatische Auswirkungen auf die Performance haben. Sieht dann etwa so aus:

Tabelle1 T1
JOIN Tabelle2 T2 (INDEX(Tab1Tab2Index)) ON T1.bla = T2.blub

Das ist eine Lösung. Eine andere wäre, mit temporären Tabellen zu arbeiten.

[Gecko]

Ich würds mal mit MySQL versuchen. MSSQL 2000 ist denke nichtmehr so ganz der neuste Stand...

[bttb930]

Zitat:

Zitat von Gecko

Ich würds mal mit MySQL versuchen. MSSQL 2000 ist denke nichtmehr so ganz der neuste Stand...

Das hier immer wieder solche "Expertenmeinungen" zu hören sind...

[mkinzler]

Zitat:

Zitat von Gecko

Ich würds mal mit MySQL versuchen. MSSQL 2000 ist denke nichtmehr so ganz der neuste Stand...

Es gibt zwar ne neuere Version, aber MySQL steckt diese locker in die Tasche.

[Jelly]

Bevor das hier wieder in einer Diskussion ausartet, welche DB denn nun die beste ist, bitte ich doch dringlichst beim Thema zu bleiben.

[omata]

Hallo alzaimar,

hier mal mein Vorschlag...

SQL-Code:

SELECT [150 Felder]
FROM ([order] JOIN inlinevariables ON icID = 3) o
INNER JOIN customer c 
  ON o.cuID = c.cuID
INNER JOIN CustomerAddress ca 
  ON     c.cuID = ca.cuID 
     AND ca.catype = 0
INNER JOIN Address a 
  ON ca.adID = a.adID
LEFT JOIN OrderProperty op 
  ON o.orID = op.orID
LEFT JOIN Carrier cr 
  ON o.crID = cr.crID
LEFT JOIN CarrierProperties cp 
  ON o.orPartnercrID = cp.crID
LEFT JOIN OrderPrices op3 
  ON     o.orID = op3.orID
     AND op3.pmID IN (10, 13, 14)
LEFT JOIN OrderPricemodelparameter op50 
  ON     o.orID = op50.orID
     AND op50.ppID = 50
LEFT JOIN OrderPricemodelparameter op79 
  ON     o.orID = op79.orID
     AND op79.ppID = 79
LEFT JOIN OrderPricemodelparameter op36 
  ON     o.orID = op36.orID
     and op36.ppID = 36
LEFT JOIN OrderPricemodelparameter op53 
  ON     o.orID = op53.orID 
     AND op53.ppID = 53
LEFT JOIN OrderPricemodelparameter op38 
  ON     o.orID = op38.orID 
     AND op38.ppID = 38
LEFT JOIN OrderPricemodelparameter op39 
  ON     o.orID = op39.orID 
     AND op39.ppID = 39
LEFT JOIN OrderPricemodelparameter op40 
  ON     o.orID = op40.orID 
     and op40.ppID = 40
LEFT JOIN (SELECT *
           FROM OrderAddress oa1 
           INNER JOIN Address ao1 
             ON oa1.adID = ao1.adID
           WHERE oa1.oaType = 0) oa1
  ON o.orID = oa1.orID 
LEFT JOIN (SELECT *
           FROM OrderAddress oa2 
           INNER JOIN Address ao2 
             ON oa2.adID = ao2.adID
           WHERE oa2.oaType = 1) oa2
  ON o.orID = oa2.orID
LEFT JOIN (SELECT *
           FROM OrderAddress oa3 
           INNER JOIN Address ao3 
             ON oa3.adID = ao3.adID
           WHERE oa3.oaType = 2) oa2
  ON o.orID = oa3.orID 
LEFT JOIN (SELECT *
           FROM OrderAddress oa4 
           INNER JOIN Address ao4 
             ON oa4.adID = ao4.adID
           WHERE oa4.oaType = 3) oa4
  ON o.orID = oa4.orID
LEFT JOIN (SELECT *
           FROM OrderAddress oa5 
           INNER JOIN Address ao5 
             ON oa5.adID = ao5.adID
           WHERE oa5.oaType = 4) oa5
  ON o.orID = oa5.orID
LEFT JOIN (SELECT *
           FROM OrderAddress oa6 
           INNER JOIN Address ao6 
             ON oa6.adID = ao6.adID
           WHERE oa6.oaType = 5) oa6
  ON o.orID = oa6.orID
LEFT JOIN (SELECT *
           FROM OrderAddress oa7 
           INNER JOIN Address ao7 
             ON oa7.adID = ao7.adID
           WHERE oa7.oaType = 6) oa7
  ON o.orID = oa7.orID

Zitat:

Zitat von Gecko

Ich würds mal mit MySQL versuchen. MSSQL 2000 ist denke nichtmehr so ganz der neuste Stand...

Das ist der Brüller! Ich bin fast vom Stuhl geruscht vor lachen. Danke für diesen Witz.

@Jelly: Sorry, den konnte ich mir jetzt nicht verkneifen.

Gruss
Thorsten

[alzaimar]

Hallo Leute,

Vielen Dank für die vielen Ideen. Ich habe aus der View nun eine Funktion gemacht, die die Ergebnistabelle sukkessive füllt. Zunächst werden die Daten der [order]-Tabelle sowie der direkt mit ihr verbundenen Detailtabellen in das Resultat geschrieben. Das dauert ca. 1 Sekunde. Lustigeweise sind die anschließenden Aktionen (also z.B. die 7 Left Joins mit den OrderPriceParameter) in 200ms durch, ebenso das Auffüllen der 7 Adressen (OrderAddress join Address). Insgesamt braucht die Funktion somit ca. 3 Sekunden.

Der Speicherbedarf des Servers liegt bei ca. 600MB, sodaß die 1GB RAM ruhig aufgestockt werden könnten.

@omata: Dein Vorschlag bringt auch nichts.

Zu MySQL: Erstmal haben wir eine DB im Einsatz, da kann man ja wohl schlecht das DBMS mal eben austauschen. Und soweit

Kann es sein, das MSSQL hier wirklich an seine Grenzen stößt?

[omata]

Zitat:

Zitat von alzaimar

@omata: Dein Vorschlag bringt auch nichts.

Schade, einen Versuch war es wert.

Zitat:

Zitat von alzaimar

Kann es sein, das MSSQL hier wirklich an seine Grenzen stößt?

Die Frage sollte wohl eher lauten: Benutze ich wirklich die Richtige Technik für das was ich machen möchte?
Für statistische Auswertungen gibt es die OLAP-Technik. Diese ist dafür da der Geschäftsleitung Daten für Ihre Statistiken zu liefern. Diese Analyse-Services sind für große Datenmengen ausgelegt und sorgen für eine schnelle Antwortzeit. Allerdings werden die Daten in solch einem Data-Warehouse mehrdimensional abgelegt und deshalb muss das dann auch eine sehr gute Hardware sein (grosse Platte + schnelle CPU). Man kann auch nicht mal so eben ein ganzes Data-Warehouse hochziehen. Trotzdem solltest du dir die Fragen stellen ob du eventuell in der Zukunft noch mehr und noch komplere Fragestellungen beantworten musst. Dann könnte vielleicht die OLAP-Technik bei euch sinn machen.

Schau dir doch einfach mal die SQL Server Analyse Services an. Sind auf der CD von SQL-Server mit drauf. Einfach installieren und mal probieren (vergiss aber nicht das Service Pack 4 einzuspielen, sonst geht nichts)

Gruss
Thorsten

[mkinzler]

Bei OLAP-Datenbanken hat man meistens keine Datensatz-orientierte sondern eine Spalten-orientierente Speicherung, deshalb ist damit eine derartige Fragestellung besser zu beantworten

[Elvis]

Zitat:

Zitat von alzaimar

Kann es sein, das MSSQL hier wirklich an seine Grenzen stößt?

Naja, meine persönliche Meinung von dieser alten windows-only Sybase-version mit dem MS-Stempel mal außen vorgelassen:
Nur weil etwas geht, muss man es nicht immer unbedingt auch machen.
Dein SQL ließe sich wunderbar in 2 Statements aufteilen: dem Hauptteil ohne OrderPricemodelparameter und einem normalen Select, dass alle verknüpften DS aus OrderPricemodelparameter liefert.
Das 2. Statement wird "prepared" so dass du ein vorkompiliertes Handle darauf bekommst.
Dann Tun weitere Calls auch nicht so weh.
Wenn SQLs zu komplex werden, fangen auch die "großen" DBMS an Mist zu bauen, besonders wenn Sortierungen im Spiel sind...

[alzaimar]

Es geht hier doch nicht um Datenmassen, die aggregiert werden müssen, um der Geschäftsleitung die Statistiken der letzten Jahrhunderte so zu präsentieren, das selbst sie es verstehen. Es handelt sich um eine normale Tabelle, bei der einfach nur ein paar Daten zusammengesucht werden.

Was mich immer wieder entäuscht, ist die Tatsache, das ein eigentlich sauberes SQL-Statement aus einem Ferrari einen Eselskarren macht. Die Engine klappert nur ein paar Indexe ab, und müsste die Abfrage eigentlich in wesentlich kürzerer Zeit abarbeiten: Die View an sich ist ja sehr groß, aber die WHERE-Klausel limitiert die zu verknüpfenden Datensätze (aus der [Order]-Tabelle) schon auf das Endresultat. Da hier ein Index ansetzt (sieht man im Execution Plan), müsste die Engine nur die paar 1000 Records abklappern und über die Verknüpfungen die restlichen Daten zusammensuchen. Laut Execution Plan macht sie das auch. Nur eben in 30-40 Sekunden.

Prinzipiell sieht die vom Client generierte Abfrage so aus:

SQL-Code:

Select [bla] from View_Orders where [Einschränkungen für die Order-Tabelle] and [Einschränkungen der verknüpften Felder]

Bei den 'Einschränkungen für die Order-Tabelle' handelt es sich um ein Zeitfenster sowie noch ein paar Dinge ('Alle vermittelten Aufträge von Gestern'), die dann bestimmten Filterkriterien (z.B. Kundenname enthält 'umpitz') entsprechen. Mit diesem Wissen kann ich dann die erwähnte Funktion schreiben, die mir dann die fertige Tabelle zusammenbastelt. Zunächst erstellt sie eine Tabelle, die alle Records mit den 'Einschränkungen für die Order-Tabelle' enhält. Dann wird sie befüllt und abschließend werden die Daten gefiltert.
Befriedigend ist das aber nicht, weil nicht allgemeingültig.

Es ist wirklich bedauerlich, wie schnell man in die Trickkiste greifen muss.

@omata: Mit OLAP habe ich mich noch nie beschäftigt, werde ich mich aber mal schlau machen.

Also nochmals Dank an alle Mitdenkenden: Ich probiere heute noch mal ein paar Tricks aus.

:dp:

[hoika]

Hallo,

wie ein left join intern abgearbeitet wird,
weisst du aber ?
Da kann man nicht einfach "ein paar Indizes" abklappern.
Packe doch mal in einer Test-DB für jedes deiner left joins
einen Eintrag in die Tabelle
und ändere dann in einen inner join.


Heiko

[alzaimar]

Hi hoika,

Leider kann man nur an 1-2 Stellen das LEFT JOIN durch ein INNER JOIN ersetzen.

Wenn ich aber testweise ein SELECT einmal mit LEFT JOIN und einmal mit JOIN durchlaufen lasse, dann ist sowohl der Query plan als auch die verwendete Zeit identisch. Deshalb behaupte ich weiterhin, das ein LEFT JOIN auch nur ein paar Indexe abklappert. Ehrlich gesagt war ich auch der Überzeugung, das ein LEFT JOIN länger dauert. Bis ich das mal durchgetestet habe und keine Verbesserung beobachten konnte.

Ich versuche gerade, dem Query-Optimizer und der Engine die Sache zu vereinfachen, indem ich meine Funktion noch weiter verbessere: Derzeit werden alle Aufträge innerhalb des Zeitraums generiert, zum Client geschaufelt und dort (mit einem TcxGrid von DevExpress) gefiltert. Das geht viel schneller, als dieses blöde 'SELECT * From View_Orders', ist aber trotzdem nicht perfekt. Und vor Allen Dingen ist es doch krank, erstmal 4MB Daten zu verschicken, nur um im Client eine Zeile darzustellen.

[hoika]

Hallo,

also ich weiss zumindestens unter Firebird,
das left outer joins lahm sind.
Im Netz steht dazu, dass left outer joins eines der
kostenintensivsten Statements sind.

Als eine Lösung war eben angegeben, (inner) joins drauszumachen,
indem Dummy-Einträge in den gejointen Tabellen eingetragen werden.
Wenn einem Auftrag eine Rechnung zugeordnet werden kann,
wird beim Auftragsanlegen eine Dummyrechnung (alle Felder NULL),
angelegt, die kann dann einfach gejoint werden.
Beim Anlegen der 1. Rechnung muss natürlich diese Dummy-Rechnung verschwinden.

Ich habe in unserem Programm auch so ne "Auftragsübersicht".
Dort habe ich mich um das Dummy gedrückt ;)
und habe die eine Query auseinandergenommen und pro Left Join
eine eigene Query gemacht.
Der Code bastelt daraus dann wieder ein Grid (über eigene Klasen/Listen).

Performance um 1000% hochgegangen.

Das Problem war, mit ein paar Daten merkst du keine Unterschied.

"Fully populate your database", heisst es ja so schön.


Eine andere Lösung wäre eine Stored Procedure
mit mehreren for/select


Heiko

[alzaimar]

Heiko, danke. Ich versuch's einfach mal.