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

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 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 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 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