Unterabfrage mit NOT IN führt beim SQL Server 2008 R2 zu langen Laufzeiten

Question

Hallo,

beim Testen einer großen Anwendung, ob sich Probleme aus der Umstellung vom SQL Server 2000 auf den SQL Server 2008 R2 ergeben, bin ich auf folgendes gestoßen.

Es wird ein SELECT nach folgendem Schema ausgeführt:

 SELECT
 a.feld1,
 a.feld2,
 FROM tbl_ergebnis a,
 WHERE a.datum >= '01.01.2001'
 AND ( CAST(a.wert1 AS VARCHAR(20)) + CAST(a.wert2 as VARCHAR(20))
 NOT IN
(SELECT CAST(d.wert1 AS VARCHAR(20)) + CAST(d.wert2 AS VARCHAR(20))
 FROM tbl_bestand d  WHERE d.status = 2 OR d.ausserbetriebnahme IS NULL))

Es handelt sich um Tabllen mit mehreren tausend Einträgen.
Laufzeit beim SQL Server 2000: wenige Sekunden
Laufzeit beim SQL Server 2008 R2: mehrere Minuten!

Das Problem ist die NOT IN Klausel mit dem zusammengesetzten Wert.

Die Laufzeit beim SQL 2008 R2 ist nur dann akzeptabel, wenn die auszuschließenden Werte vorher in einer temporären Tabelle gespeichert werden, oder, als zweite Lösung, wenn die Tablle d um ein berechnetes Feld erweitert wird, das den Wert d.wert1 + d.wert2 enthält. In diesen beiden Fällen braucht auch der SQL 2008 R2 nur wenige Sekunden.

Da in der bestehenden Anwendung sehr oft solche Abfragen genutzt werden, wäre der Aufwand, die Anwendung nach solchen Konstellationen zu durchsuchen und entsprechende Änderungen durchzuführen sehr hoch (teuer).

Kennt jemand dieses Problem? Kennt jemand dafür vielleicht eine einfache Lösung? Oder hat jemand zufällig davon gehört, dass sich dieses Problem schon in der Pipeline von Microsoft befindet?

Vielen Dank für eine Antwort!

Gruß
Ralf

 

Answer 1

Hallo Ralf,

Die Laufzeit beim SQL 2008 R2 ist nur dann akzeptabel, wenn die auszuschließenden Werte vorher in einer temporären Tabelle gespeichert werden, oder, als zweite Lösung, wenn die Tablle d um ein berechnetes Feld erweitert wird, das den Wert d.wert1 + d.wert2 enthält. In diesen beiden Fällen braucht auch der SQL 2008 R2 nur wenige Sekunden.

ich hatte ein ähnliches Problem. Geholfen hat mir der Ausführungsplan bzw. die Hinweise, die das Management Studio 2008 R2 nun dort anzeigt. Es hat mir einen fehlenden Index (Verbesserung 99,xxx Prozent) gemeldet. Die Abfragen liefen in SQL 2008 (ohne R2) übrigens ebenfalls sehr performant.

Lass dir daher mal den Ausführungsplan anzeigen, ggfs. siehst Du dort auch, wo das Problem liegt.

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Hallo,

beim Testen einer großen Anwendung, ob sich Probleme aus der Umstellung vom SQL Server 2000 auf den SQL Server 2008 R2 ergeben, bin ich auf folgendes gestoßen.

Es wird ein SELECT nach folgendem Schema ausgeführt:

 SELECT
 a.feld1,
 a.feld2,
 FROM tbl_ergebnis a,
 WHERE a.datum >= '01.01.2001'
 AND ( CAST(a.wert1 AS VARCHAR(20)) + CAST(a.wert2 as VARCHAR(20))
 NOT IN
(SELECT CAST(d.wert1 AS VARCHAR(20)) + CAST(d.wert2 AS VARCHAR(20))
 FROM tbl_bestand d  WHERE d.status = 2 OR d.ausserbetriebnahme IS NULL))

Es handelt sich um Tabllen mit mehreren tausend Einträgen.
Laufzeit beim SQL Server 2000: wenige Sekunden
Laufzeit beim SQL Server 2008 R2: mehrere Minuten!

Das Problem ist die NOT IN Klausel mit dem zusammengesetzten Wert.

Die Laufzeit beim SQL 2008 R2 ist nur dann akzeptabel, wenn die auszuschließenden Werte vorher in einer temporären Tabelle gespeichert werden, oder, als zweite Lösung, wenn die Tablle d um ein berechnetes Feld erweitert wird, das den Wert d.wert1 + d.wert2 enthält. In diesen beiden Fällen braucht auch der SQL 2008 R2 nur wenige Sekunden.

Da in der bestehenden Anwendung sehr oft solche Abfragen genutzt werden, wäre der Aufwand, die Anwendung nach solchen Konstellationen zu durchsuchen und entsprechende Änderungen durchzuführen sehr hoch (teuer).

Kennt jemand dieses Problem? Kennt jemand dafür vielleicht eine einfache Lösung? Oder hat jemand zufällig davon gehört, dass sich dieses Problem schon in der Pipeline von Microsoft befindet?

Vielen Dank für eine Antwort!

Das Problem liegt in der Tat in der NOT IN Klausel. Da hat anscheinend jemand versucht, um die Limitation von NOT IN auf die Rückgabe einer einzigen Spalte herumzuarbeiten, anstelle einfach ein NOT EXISTS() zu verwenden, welches auch mit mehreren Kriterien zurechtkommt. :-)

Mittel- und langfristig solltet Ihr überlegen, ob es nicht Sinn macht, diese Logik zu überdenken. Als kurzfristigen "tactical workaround" solltest Du Stefan's Vorschlag folgen.

 

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 3

Hallo Frank,

Das Problem liegt in der Tat in der NOT IN Klausel. Da hat anscheinend jemand versucht, um die Limitation von NOT IN auf die Rückgabe einer einzigen Spalte herumzuarbeiten, anstelle einfach ein NOT EXISTS() zu verwenden, welches auch mit mehreren Kriterien zurechtkommt. :-)

das mit den mehreren Spalten hatte gar nicht gesehen. Bei mir war es so, dass eine Abfrage nach dem Schema:

SELECT ...
FROM   ...
WHERE  ... = ...
AND    ... NOT IN (
                   SELECT Spalte
                   FROM   AndereTabelle
                   WHERE  SpalteX = WertX
                   AND    SpalteY = WertY
                  )

unter SQL Server 2005 und 2008 ohne Probleme gearbeitet hat, unter 2008 R2 aber erhebliche Performanceprobleme mit sich brachte.

Ok, ich hab den Index "vergessen" aber warum es dann mit 2008 geht und mit R2 nicht, will sich mir nicht ganz erschließen.

(Die genaue Abfrage kann ich nicht mehr nennen, da müsste ich lange suchen^^)

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Hallo Frank,

Das Problem liegt in der Tat in der NOT IN Klausel. Da hat anscheinend jemand versucht, um die Limitation von NOT IN auf die Rückgabe einer einzigen Spalte herumzuarbeiten, anstelle einfach ein NOT EXISTS() zu verwenden, welches auch mit mehreren Kriterien zurechtkommt. :-)

das mit den mehreren Spalten hatte gar nicht gesehen. Bei mir war es so, dass eine Abfrage nach dem Schema:

SELECT ...
FROM   ...
WHERE  ... = ...
AND    ... NOT IN (
                   SELECT Spalte
                   FROM   AndereTabelle
                   WHERE  SpalteX = WertX
                   AND    SpalteY = WertY
                  )

unter SQL Server 2005 und 2008 ohne Probleme gearbeitet hat, unter 2008 R2 aber erhebliche Performanceprobleme mit sich brachte.

Ok, ich hab den Index "vergessen" aber warum es dann mit 2008 geht und mit R2 nicht, will sich mir nicht ganz erschließen.

(Die genaue Abfrage kann ich nicht mehr nennen, da müsste ich lange suchen^^)

Hm, keine Ahnung.

Wir planen jetzt gerade erst einmal die Migration auf 2005 64-bit. 2008 kommt vielleicht im nächsten Jahr. An R2 denkt im Moment noch niemand. :-)

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 5

Moin,

vielen Dank für Eure Anregungen!

Das für mich vorläufige Ergebnis lautet aber, dass in dem von mir beschriebenen Fall der R2 eine wesentlich schlechtere Laufzeit hat und dieses nicht ohne erheblichen Aufwand in der Anwendung zu beheben ist. Die Datenbank ist 1:1 auf den R2 portiert worden und auch die Indizes sind genau die selben.

Ich hab' aber noch mal den Vorschlag von Frank aufgegriffen und die Ausführungspläne beider Server verglichen. Bis auf einen Index, den der R2 vorgeschlagen hat, hat das keine weiteren Erkenntnisse gebracht.

Gewundert habe ich mich eigentlich über den vorgeschlagenen Index. Hab ihn trotzdem mal eingerichtet. Die Laufzeit hat sich dadurch, wie schon vermutet auch nicht wesentlich geändert.

Wäre toll gewesen, wenn vielleicht ein globaler Parameter einzustellen wäre, um das ganze schneller zu gestalten. Aber ich werd' noch mal experimentieren, ob es nicht doch eine Lösung gibt, die nicht so viel Aufwand nach sich zieht.

@Frank
Du hast geschrieben: Das Problem liegt in der Tat in der NOT IN Klausel. Da hat anscheinend jemand versucht, um die Limitation von NOT IN auf die Rückgabe einer einzigen Spalte herumzuarbeiten, anstelle einfach ein NOT EXISTS() zu verwenden, welches auch mit mehreren Kriterien zurechtkommt. :-)

Das sehe ich im Moment nicht ganz so, weil der SELECT für sich alleine

(SELECT CAST(d.wert1 AS VARCHAR(20)) + CAST(d.wert2 AS VARCHAR(20))
 FROM tbl_bestand d  WHERE d.status = 2 OR d.ausserbetriebnahme IS NULL)

in einer Sekunde erledigt ist; genauso schnell wie ein SELECT auf eine temporäre Tabelle mit nur einem Feld. Hier scheint das Zusammenspiel nicht zu funktionieren, so wie es unter SQL 2000 einwandfrei klappte. Ich halte das für einen BUG.

Gruß
Ralf

 

Answer 6

Sorry, vielleicht habe ich mich nicht eindeutig ausgedrückt. :-)

Ich meinte damit, dass die Logik der Abfrage meiner Meinung nach nicht wirklich optimal ist. Nichts gegen eine Verkettung, aber mehrere Spalten zu verketten, um damit einen Vergleich in einer NOT IN Klausel zu ermöglichen erscheint mir suboptimal. Wie soll SQL Server zum Beispiel bei so einer Abfrage evtl. vorhandene Indizes sinnvoll nutzen? Die Abfrage, die Du gepostet hast, mag ja für sich schnell sein, sie sollte aber auch nicht das eigentliche Problem sein. Aber ohne weitere Informationen ist das leider mehr ein Schuss ins Dunkle als alles andere.

Trotzdem ist nicht alles was wie ein Bug aussieht, auch ein solcher. Wenn Du jedoch dieser Meinung bist, kannst Dich ja mal auf der Connect Seite umsehen, ob dort bereits ein solches Problem bei R2 bekannt ist und falls nicht, ein Item dort erstellen.

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 7

Nein, nein, Du hast ja recht. Ich weiß ja auch, das die Abfrage(n) nicht optimal gestaltet sind. Aber wenn man so etwas vorfindet, dann kann ich jetzt dem 'Erfinder' von damals nicht unbedingt vorwerfen, dass er nicht so gut gearbeitet hat, denn die Abfrage läuft ja erstaunlich schnell - auf dem 2000er. Und Lösungen für eine Umstellung habe ich ja auch genug.

Meine Position ist aber die, dass ich davon ausgehe, dass eine Abfrage auf der neuen Datenbank mindestens vergleichbar schnell ablaufen sollte. Wäre dies nicht der Fall, würde generell etwas falsch laufen, wenn jedesmal alle/viele QUERIES auf Geschwindigkeitseinbußen durchforstet werden müssen. Das will niemand leisten, weil einfach zu teuer.

Ich hab' jetzt aber das Problem auf der Connect Seite eingestellt, nachdem ich nicht erkennen kann, dass ich etwas übersehen habe. Mir ist jetzt auch nicht ganz klar, welche 'weiteren Informationen' ich noch geben könnte; außer dass mir der etwas andere Ausführungsplan für die Unterabfrage noch aufgefallen ist. Ein Bild beider Pläne habe ich dem Item beigefügt.

Answer 8

Eine abschließende Antwort ist vom Microsoftteam erstellt worden.

Fazit:

Das Problem wurde als unlösbar geschlossen, weil es nicht nachvollziehbar ist, warum in diesem Fall vom 2008er ein schlechterer Ausführungsplan bevorzugt wird.

---

Gruß Ralf