Laden...

Performance SQL-Abfrage: WHERE ID IN (...)

Letzter Beitrag vor 14 Jahren 7 Posts 1.103 Views
Performance SQL-Abfrage: WHERE ID IN (...)

Hallo.

Ich habe ein Performanceproblem: Immer wenn ich in einem Statement einen IN-Ausdruck habe, wird die Abfrage extram langsam.
Beispiel:

SELECT * FROM TABELLE WHERE TABELLE.ID IN (1,2,3,...,20,21,22)

Um so mehr Werte in der IN-Ausdrücken stecken, desto langsamer wird die Abfrage, selbst wenn nur sehr wenig Treffer rauskommen. Gilt ebenfalls für DELETE-Anweisungen, also in allen WHERE-Klauseln. Ich dachte schon es kommt durch ein großes Kreuzprodukt (sind oft mehrere Tabellen beteiligt) und habe den Teil schon bei INNER JOIN ... ON reingenommen - leider keine Wirkung.

Ist das normal? Kann man daran was ändern bzw. gibt es eine Alternative? Die IDs sind leider willkürlich, d.h. BETWEEN oder so klappt hier nicht.

Lars

Hi,

wir hatten ein ähnliches Problem, auch mit dem IN (haben allerdings fast ausschließlich LEFT JOINS).

Wir haben dann auf EXISTS mit Subselect umgestellt umgestellt.
IN erzeugt OR Bedingungen, die sind afaik langsam.

Gruß

DevHB

Hallo Lars

Wie DevHB schon richtig gesagt hat, erzeugt ein "col1 IN (1, 2)" intern ein "col1 = 1 OR col1 = 2", außerdem gibt's keine Execution Plan Wiederverwendung. Dadurch werden große IN-Statements immer sehr schnell zum Performanceproblem.

Ein besserer Weg ist eine Temp-Table zu erzeugen, die Keys dort einzufügen und diese dann zu joinen.

Der definitiv schnellste Weg ist ein SQL Server seitiger Split der Werte. Speziell bei IDs lässt sich das sehr gut umsetzen. Du verkettest alle Werte Clientseitig (z.B. über TAB) und schickst sie in einem String an den Server. Dort werden die Werte über SQL (oder SQLCLR) wieder aufgeteilt und z.B. über eine Table-Valued Function in eine Temp-Table gepackt welche man dann Joinen kann.

Zum Thema String Splitting mit SQL Server kannst du dir folgenden Artikel mal anschauen:
High Performance String Split Functions

Grüße
Flo

Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+

Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.

@DevHB:
Mit EXISTS sehe ich aber immernoch die Notwendigkeit eines IN-Statements oder? Hatte es mal probiert, aber entweder bin ich zu doof, oder mein Problem scheint komplizierter. ^^

@Florian Reischl:
Der Link ist klasse. Habe gleich mal die Cursor-Variante probiert, naja vielleicht 10-20% schneller. Ist etwas, aber noch nicht die Wcuht. Werde wohl die CLR-Funktion in unser Assembly integrieren, scheint die bessere Variante zu sein. Wenn ichs probiert habe geb ich hier bescheid wies klappt.

Danke für die Antworten.
Lars

huhu,

@Flo:

Hattest Du noch getestet wie folgendes in dem Zusammenhang skaliert - oder gleich weggelasen weils eh langsamer ist:


declare @tbl table (field nvarchar(50))

insert into @tbl select 'AA' union select 'AB' union select 'AC' [...]

select * from MyTable inner join @table as a on a.Field=MyTable.Field

SQL-Insert kann man beim 2008er vereinfachen. Ich weis, dass es bei nicht zu vielen IN Bedingungen recht schnell geht. Hab aber noch nie einen Vergleich angestellt.

😃

Xynratron

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

Hi Xynratron

Ja, mehrere UNION ALL Statements habe ich auch schon mal versucht, ist bei größeren Mengen von Datensätzen um Faktoren langsamer. Hatte das auch schon mal bei SSC angesprochen:
Why INSER INTO ... UNION ALL?
Da war's nur um Faktor 3 langsamer, in anderen Tests war ich bei Faktor 10. Ich verwende UNION ALL für mehrere INSERTs zum einen weil's kürzer ist (= gut für faule 😉 ) und weil's als komplette Aktion durchgeführt wird. Also entweder werden alle Datensätze eingefügt oder keiner. Bei einzelnen INSERT Statements ist das ja nicht der Fall (ohne entsprechende Fehlerbehandlung). Bei INSERTs in eine Temp-Table aus einem Programm würde ich aber auf jeden Fall die einzelnen INSERT-Statements vorziehen da sie parametrisiert und präpariert werden können. Schont den SQL Server Compiler - weil Cached Plan Reusage - und ist dem entsprechen nochmal schneller.

Zu der verkürzten Schreibweise bei SQL Server 2008 über den sog. Row-Constructor... Den habe ich mal getestet und danach einen Bug bei Microsoft aufgemacht 👅 :
INSERT Performance of Row Constructor is quiet bad
Bug wurde sogar bestätigt. Scheint sich um einen Fehler im SQL Server 2008 zu handeln...

Grüße
Flo

Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+

Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.

ahh, Danke 😃

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.