Ausführungsplan ist komplett anders obwohl SQL gleich ist, bis auf einen WHERE-Wert

Question

Hi,

habe eine stored Prozedure die mit Daten aus mehrern Tabellen darstellen soll.
Diese ist so geschrieben, das der WHERE Befehl variabel gestaltet wird.

Beipiel 1: (Dauer 2 sec)

SELECT T_LWBK.BKSORT + CAST(T_LWBK.BKJAHR AS NVARCHAR(4)) + '-' + REPLACE(STR(T_LWBK.BKNUMM,4,0),' ','0') AS [PO Nummer],
           dbo.FN_DAT2STR(T_LWBK.BKANDA) AS [angelegt am],
           LIEF.LINAME + ' (' + CAST(BKLIEF AS NVARCHAR(5)) + ')' AS Lieferant, BKGRUN AS Grund, 
           REPLACE(STR(dbo.FN_FINSUM(BKSORT, BKJAHR, BKNUMM), 10,2),'.',',') + ' ' + dbo.T_WAEH.WKbez AS Gesamtpreis,
           T_LWBK.BKANUS AS [angelegt von], dbo.FN_DAT2STR(T_LWBK.BKBSDA) AS Bestelldatum, dbo.T_Status.Status AS [Status], T_LWBK.BKAABT
FROM dbo.T_LWBK LEFT JOIN LEARWACK.LEARWACK.RHDBD_21.LIEF LIEF ON T_LWBK.BKLIEF = LIEF.LILINR AND LIEF.LIFIRM='5' AND LIEF.LIWKNR='000'
                           LEFT JOIN dbo.T_WAEH ON dbo.T_LWBK.BKSORT = dbo.T_WAEH.WOrt and dbo.T_LWBK.BKWAEH = dbo.T_WAEH.WId
                           LEFT JOIN dbo.T_Status ON dbo.T_LWBK.BKSTAT = dbo.T_Status.idStat
WHERE LIEF.LIFIRM='5' AND T_LWBK.BKSTAT IN (20, 30)
ORDER BY T_LWBK.BKSORT DESC, T_LWBK.BKJAHR DESC, T_LWBK.BKNUMM DESC

Beipiel 2: (Dauer 17 sec)

SELECT T_LWBK.BKSORT + CAST(T_LWBK.BKJAHR AS NVARCHAR(4)) + '-' + REPLACE(STR(T_LWBK.BKNUMM,4,0),' ','0') AS [PO Nummer],
           dbo.FN_DAT2STR(T_LWBK.BKANDA) AS [angelegt am],
           LIEF.LINAME + ' (' + CAST(BKLIEF AS NVARCHAR(5)) + ')' AS Lieferant, BKGRUN AS Grund, 
           REPLACE(STR(dbo.FN_FINSUM(BKSORT, BKJAHR, BKNUMM), 10,2),'.',',') + ' ' + dbo.T_WAEH.WKbez AS Gesamtpreis,
           T_LWBK.BKANUS AS [angelegt von], dbo.FN_DAT2STR(T_LWBK.BKBSDA) AS Bestelldatum, dbo.T_Status.Status AS [Status], T_LWBK.BKAABT
FROM dbo.T_LWBK LEFT JOIN LEARWACK.LEARWACK.RHDBD_21.LIEF LIEF ON T_LWBK.BKLIEF = LIEF.LILINR AND LIEF.LIFIRM='5' AND LIEF.LIWKNR='000'
                            LEFT JOIN dbo.T_WAEH ON dbo.T_LWBK.BKSORT = dbo.T_WAEH.WOrt and dbo.T_LWBK.BKWAEH = dbo.T_WAEH.WId
                            LEFT JOIN dbo.T_Status ON dbo.T_LWBK.BKSTAT = dbo.T_Status.idStat
WHERE LIEF.LIFIRM='5' AND T_LWBK.BKSTAT IN (50)
ORDER BY T_LWBK.BKSORT DESC, T_LWBK.BKJAHR DESC, T_LWBK.BKNUMM DESC


Wie zu sehen ist, ist bis auf eine Zahl im WHERE Segment, der restliche SELECT absolute identisch.
Die Ausführungspläne sind aber völlig verschieden, und ich habe keine Ahnung wieso.
Die Schlüssel auf den Tabellen sind so gestaltet das Sie die Datensätze UNIQE darstellen, was aber kein Problem sein kann, da ja der erste SELECT schnell geht.
2 sec benötigt dieser auch nur weil ich über einen Linked Server von einer AS/400 DAten mit einbinde.

Wie ich die Ausführungspläne hier posten kann weis ich leider nicht, vielleicht kann man mir das mitteilen, dann kann ich die auch noch posten, falls das was bringt :-)

Hoffe das jemand sowas ähnliches schon hatte und ne Lösung gefunden hat, ich verzweifel seit 2 Tagen, da ich auch nichts lösendes im I-Net finde.
Habe die Prozeduren auch schon recompilen lassen (SP_RECOMPILE) bringt gar nichts, Ausführungsplan wird exact so erstellt wie davor auch.


Bis dann
Martin

 

 

 

---

thx

Answer 1

Hallo Martin,

Du könntest einen Screenshot von den Ausführungsplänen erstellen und die in Deinem SkyDrive ablegen.
Alternative kann man auch einen textuellen Ausführungsplan erstellen lassen, geht mit SET SHOWPLAN_TEXT ON; bei den vielen Joins werden die Pläne so oder so umfangreich.

Es ist übrigens nicht so ungewöhnlich, das bei unterschiedlichen Werten unterschiedliche Pläne verwendet werden. Indizes werden z.B. nur dann verwendet, wenn durch Filterung die Datenmenge auf min. unter 25% reduziert wird, sonst wird eher ein Full Table Scan ausgeführt.

Wenn es ungewöhnlich viele Datensätze mit Wert 50 gibt kann es sein, das ein Index nicht verwendet wird.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

hi Martin,

Wie zu sehen ist, ist bis auf eine Zahl im WHERE Segment, der restliche SELECT absolute identisch.
Die Ausführungspläne sind aber völlig verschieden, und ich habe keine Ahnung wieso.

Lass beide Abfragen vom Database Tuning Advisor analysieren:

http://msdn.microsoft.com/en-us/library/ms189303.aspx

Wie ich die Ausführungspläne hier posten kann weis ich leider nicht, vielleicht kann man mir das mitteilen, dann kann ich die auch noch posten, falls das was bringt :-)

In den Ausführungsplan einen rechts-click und den Plan als XML anzeigen oder speichern.

Wieviel Datensätze sind den betroffen:

SELECT  COUNT(*)
FROM    dbo.T_LWBK
        LEFT JOIN LEARWACK.LEARWACK.RHDBD_21.LIEF LIEF
          ON T_LWBK.BKLIEF = LIEF.LILINR
          AND LIEF.LIFIRM = '5'
          AND LIEF.LIWKNR = '000'
WHERE   LIEF.LIFIRM = '5'
        AND T_LWBK.BKSTAT IN ( 20, 30 ) ;

vs.

SELECT  COUNT(*)
FROM    dbo.T_LWBK
        LEFT JOIN LEARWACK.LEARWACK.RHDBD_21.LIEF LIEF
          ON T_LWBK.BKLIEF = LIEF.LILINR
          AND LIEF.LIFIRM = '5'
          AND LIEF.LIWKNR = '000'
WHERE   LIEF.LIFIRM = '5'
        AND T_LWBK.BKSTAT IN ( 50 ) ;

btw, warum ein LEFT JOIN auf LIEF, wenn du nachher auf LIEF.LIFIRM filterst und kein INNER JOIN?

Und

SELECT  COUNT(*)
FROM    dbo.T_LWBK
WHERE   T_LWBK.BKSTAT IN ( 20, 30 ) ;

vs.

SELECT  COUNT(*)
FROM    dbo.T_LWBK
WHERE   T_LWBK.BKSTAT IN ( 50 ) ;

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 3

SELECT  COUNT(*)
FROM    dbo.T_LWBK
         LEFT JOIN LEARWACK.LEARWACK.RHDBD_21.LIEF LIEF
           ON T_LWBK.BKLIEF = LIEF.LILINR
           AND LIEF.LIFIRM = '5'
           AND LIEF.LIWKNR = '000'
WHERE   LIEF.LIFIRM = '5'
         AND T_LWBK.BKSTAT IN ( 50 ) ;

btw, warum ein LEFT JOIN auf LIEF, wenn du nachher auf LIEF.LIFIRM filterst und kein INNER JOIN?

Noch besser - hab's jetzt erst im Nachhinein gesehen - das LIEF.LIFIRM = '5' ist sowieso unnötig, da schon Bestandteil der JOIN Bedingung.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 4

Was ist ein Skydrive, ich bin schon zu lange nicht mehr hier gewesen :-)
Werde das mal mit dem SHOWPLAN_TEXT versuchen.

Das mit deiner Erklärung wegen der Datenmenge kann ich nachvollziehen, in diesem Fall hat der lange SELECT aber nur ein 4tel von der Menge wie das Beispiel 1 :-)

Danke derweil

---

thx

Answer 5

Hi,

das mit der LIFIRM habe ich auch so gehabt. Habe ich das im WHERE nicht drin, bekomme ich alle Datensätze 4 mal (da 4 Firmen in der LIEF vorhanden sind).
Habe ich das im WHERE drin und im JOIN nicht habe ich das selbe PRoblem alle DS 4x.
Daher eben in beiden Bereichen eingetragen.

Ausführungsplan in XML kann ich nicht auswählen.
Kann nur als *.sqlplan sichern (SQL Server 2005) (ist aber wohl XML)
Ich trau mich kaum zu fragen, wie soll ich die hier posten? Einfügen macht kein Sinn, die sind Rieeeeeesig. (ca. 500 Zeilen pro Plan.)

Anzahl Datensätze:  194
SELECT  COUNT(*)
FROM    dbo.T_LWBK
        LEFT JOIN LEARWACK.LEARWACK.RHDBD_21.LIEF LIEF
          ON T_LWBK.BKLIEF = LIEF.LILINR
          AND LIEF.LIFIRM = '5'
          AND LIEF.LIWKNR = '000'
WHERE   LIEF.LIFIRM = '5'
        AND T_LWBK.BKSTAT IN ( 20, 30 ) ;

Anzahl Datensätze:  36
SELECT  COUNT(*)
FROM    dbo.T_LWBK
        LEFT JOIN LEARWACK.LEARWACK.RHDBD_21.LIEF LIEF
          ON T_LWBK.BKLIEF = LIEF.LILINR
          AND LIEF.LIFIRM = '5'
          AND LIEF.LIWKNR = '000'
WHERE   LIEF.LIFIRM = '5'
        AND T_LWBK.BKSTAT IN ( 50 ) ;

Bei den 2 anderen SELECT die selbe Menge natürlich, wäre ja schlimm wenn da andere Mengen wären :-)
Das ist der Ausschlaggebende Befehl, der Rest ist nur Vertextung und Summen.


Der Tuning Analyzer bringt keinen Verbesserungsvorschlag.

 

 

 

 

---

thx

Answer 6

hi Martin,

das mit der LIFIRM habe ich auch so gehabt. Habe ich das im WHERE nicht drin, bekomme ich alle Datensätze 4 mal (da 4 Firmen in der LIEF vorhanden sind).
Habe ich das im WHERE drin und im JOIN nicht habe ich das selbe PRoblem alle DS 4x.

Sicher? Ergibt in meinen Augen keinen Sinn.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 7

Was ist ein Skydrive, ich bin schon zu lange nicht mehr hier gewesen :-)

http://www.skydrive.com/

Einfach mit Deiner Live ID anmelden, wenn Du es nicht eh schon bist. Dort kannst Du dann z.B. in "Öffentlich" Dateien ablegen, auf die alle zugreifen können.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 8

Hi Olaf,

ok. Habe ich geschafft, die Pläne sind hochgeladen.

http://cid-a72cba174c94e40e.skydrive.live.com/redir.aspx?resid=A72CBA174C94E40E!122

Hoffe das mit dem Link funktioniert und Ihr könnt mir damit helfen :-)

 

Danke

---

thx

Answer 9

Sicher? Ergibt in meinen Augen keinen Sinn.

Hi Stefan,

ok, ich weis langsam nicht mehr was ich tue. Habs nochmal ausgeführt. Es macht keinen Unterschied wenn ich die Firma aus dem WHERE rausnehme, ich muss es im JOIN drinlassen.
Kann passieren :-)

Ausführungsgeschwindigkeit hat sich dadurch aber nicht geändert.

Bis dann
An alle die mich unterstützen ein schönes Wochenende, den anderen natürlich auch :-)

---

thx

Answer 10

Hallo Martin,

hat geklappt, die Pläne kann man runterladen und sich ansehen.

Einen Grund, das die zweite Abfrage (GetOVERVIEW-50.sqlplan) langsamer ist, scheint an der Remote Quell zu liegen.
Klick mal in beiden Plänen auf "Remote Query" => "Tatsächliche Anzahl von Zeilen"; bei Filter auf (20, 30) werden aus der Remote Quelle nur 2094 Datensätze abgerufen, bei (50) sind es 98.324; ein wenig mehr und wenn man mal davon ausgeht, das Remote Abfragen durch Übertragung etc. eh langsamer sind, kann die Zeitdifferenz schon mal daher kommen.

Stellt sich als nächstes die Frage, warum werden so eine Unterschiedliche Anzahl an DS aus der Remotequelle abgerufen, das ist mir noch nicht schlüssig, da muss ich noch etwas schauen, ob ich was sehen kann. 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 11

Klick mal in beiden Plänen auf "Remote Query" => "Tatsächliche Anzahl von Zeilen"; bei Filter auf (20, 30) werden aus der Remote Quelle nur 2094 Datensätze abgerufen, bei (50) sind es 98.324; ein wenig mehr und wenn man mal davon ausgeht, das Remote Abfragen durch Übertragung etc. eh langsamer sind, kann die Zeitdifferenz schon mal daher kommen.

Hi,

danke. Die Punkte habe ich (dachte ich) auch betrachtet, habe den Unterschied aber nicht wahrgenommen. Das kann tatsächlich dazu führen.
Komisch ist aber, wenn ich bei der Abfrage mit der 50 eine 60 noch mit angebe, dann dauert der auch nur 2 - 3 Sekunden. Ist ja echt nicht zum fassen ;-)

Werde mich nach dem WE darum kümmern.

Bis dann
Martin

---

thx

Answer 12

bei Filter auf (20, 30) werden aus der Remote Quelle nur 2094 Datensätze abgerufen, bei (50) sind es 98.324;
Stellt sich als nächstes die Frage, warum werden so eine Unterschiedliche Anzahl an DS aus der Remotequelle abgerufen

Hi Olaf,

mir ist ebenfalls Schleierhaft wieso die MEnge der DS so unterschiedlich sind, denn in der LIEF auf der Remote Maschiene sind exakt, halt dich fest, 2094 Datensätze drin. Wie der bei dem Filter von (50) darauf kommt das mal mit dem Faktor 46,955 mal zu nehmen habe ich echt keinen blassen Schimmer.

Das ist echt übel, ich muss das unbedingt beschleunigen, kann die externe Datenquelle aber nicht auf den SQL Server bringen, da ich nie weis wann sich etwas auf der Remote Maschiene ändert. Gibts da einen "Standard" Workflow wie sowas zu handhaben ist?

Ich brauch nen Strohhalm an dem ich mich wieder hochziehen kann :-)

Danke für deine mithilfe.

MArtin

 

---

thx

Answer 13

Hi SQLer,

noch ne Frage zu dem Ausführungsplan.

Habe gesehen das bei "Actual Rewinds" der
mit Filter 20: 0 stehen hat
mit Filter 50: 45 stehen hat

das bedeutet doch, das er den Befehl 45 mal wiederholt hat, aber aus was für Gründe?
Ist das irgendwo ersichtlich?

Danke nochmal

 

Martin

---

thx

Answer 14

Hallo Martin,

die Remote Query wird 46 mal ausgeführt; 46 * 2.094 = 96.324.

Im Plan-20 kann ein Merge Join zwichen Remote Query & T_LWBK ausgeführt werden, er nimmt also 2.094 Datensätze aus der Remote Query und merged sie mit 189 Datensätze aus T_LWBK.

Im Plan-50 kommen aus T_LWBK 46 Datensätze und hier macht er ein Nested Loop auf die Remote Query, deswegen wird die Remote Query 46mal abgefragt.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 15

Hallo Olaf,

hab es nun eingegrenzt.
Man glaubt es nicht, aber es liegt an der Menge der Datensätze die aus der T_LWBK kommen.
Unterschreitet die Anzahl eine bestimmte Menge (habe ich nicht rausbekommen) macht er einen Nested Loop, was dann, da Remote ewig dauert, eben zu einer langen Ausführungszeit führt.
Komme ich über die Menge macht er einen Sort und dann einen Merge Join was extrem schnell geht.

Nu wäre es cool, dem SQL-SELECT irgendwie mitzuteilen, dass er immer einen Sort mit Merge Join machen soll :-)

Danke

Martin

---

thx

Answer 16

hallo Martin,

Nu wäre es cool, dem SQL-SELECT irgendwie mitzuteilen, dass er immer einen Sort mit Merge Join machen soll :-)

Nichts einfacher als das:

http://msdn.microsoft.com/en-us/library/ms173815.aspx

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 17

Hallo an alle,

Danke an Stefan, nu gehts.
Und wieso hat das so lange gedauert *grins* *spass*

Wusste gar nicht das man den reinsetzten kann, ja immer wieder mal was neues, und das ist gut so :-)

Ich denke damit kann ich nun arbeiten.

 

Nochmals Danke an alle beteiligten.

Martin

---

thx