Join oder IN - die schnellste Methode

[Tobias Frost]

Hi, ich habe eine prinzipielle Technik-Frage:

Angenommen ich habe eine Artikel-Tabelle mit 3000 Artikeln, Key auf ART_NR, und habe nun eine Liste von 1000 Artikel-Nummern zu denen ich Details aus der Artikel-Tabelle abfragen möchte. Das SELECT dazu ist letzlich einfach:

SELECT * FROM ARTIKEL WHERE ART_NR IN (A1, A2, A3 usw.)

Diese Abfrage konstruiere ich in DELPHI, der '()'-Teil ist ein String erstellt aus der Liste. Meine Erfahrung mit Interbase hat mir gezeigt, daß dies Abfrage relativ langsam ist - ein StoredProcedure die aus dem String eine Tabelle macht und dann per JOIN mit der Artikel-Tabelle verbunden wird war bei mir immer schneller auch wenn der Aufwand größer war. Nun kann ich in INTERBASE eine SP in einen JOIN aufnehmen, in MS-SQL ist mir das noch nicht gelungen. Ich kann zwar in der SP eine temporäre, indizierte Tabelle erstellen, diese dann als SELECT von der SP zurüchgeben lassen, jedoch die Ergebnismenge anscheinend nicht in einen JOIN aufnehmen !

Vielleicht ist der ganze Aufwand nicht nötig und der MS SQL-Server macht aus einem 'IN ( )' sowieso eine temporäre Tabelle die er dann mittels JOIN intern verknüpft. Leider habe ich dazu noch nichts gefunden.

Hat jemand eine IDEE wie sich dieser Fall am elegantesten und effektivsten Lösen läßt ?

Tobias

[Ralf Jansen]

Hallo,

ich vermute der SQL Server macht aus dem IN eine Simple OR Verknüpfung. Sehr Effizient wäre
es wohl aus der Artikelliste einen UNION zu basteln(macht Oracle z.B. in bestimmten Fällen).

ALSO
<PRE>
SELECT * FROM ARTIKEL WHERE ART_NR = 'A1'
UNION
SELECT * FROM ARTIKEL WHERE ART_NR = 'A2'
</PRE> u.s.w.

Bei einer großen Artikelliste aber eher nicht praktikabel.

In einen Join kann man nur Stored Functions aufnehmen. Das sähe z.B. dann so aus

<PRE>

CREATE FUNCTION fn_Test(@cList varchar(255))
RETURNS @retTable TABLE(cValue varchar(15))
AS
BEGIN
Declare @aList varchar(255),
@aValue varchar(15),
@aStartChar int,
@aEndChar int;

Set @aList = @cList + ',';

WHILE (CHARINDEX(',', @aList) <> 0)
BEGIN
Set @aStartChar = 2;
Set @aEndChar = CHARINDEX(',', @aList) - 1;
Set @aValue = SUBSTRING(@aList, @aStartChar, @aEndChar - @aStartChar);
Set @aStartChar = @aEndChar + 2;
Set @aList = SUBSTRING(@aList, @aStartChar, LEN(@aList) - @aStartChar + 1);
Insert into @rettable(cValue) values(@aValue);
END
RETURN
END
</PRE>

und dann

<PRE>
select * from fn_Test('''A1'',''A2'',''A3''')
</PRE>

Grüße
Ral

[Tobias Frost]

Hi Ralf,

das ist eine sehr interessante, mir bisher unbekannte Variante. Mit StoredFunctions habe ich nur als UDF's unter INTERBASE gearbeitet. Die SF's unter MS-SQLServer scheinen interssante Aspekte zu bergen. Werde mir dein Code-Beispiel vertieft zu Gemüte führen.

Gruß, Tobia

[Ralf Jansen]

Hallo Tobias,

ich habe gerade selbst mal mit der stored function rumgespielt. War eigentlich nur ein kleines Gedankenexperimemt wie so etwas aussehen könnnte und ich habe nicht wirklich mit einem Performancegewinn gerechnet.
Ich konnte damit aber in meinem aktuellen
Projekt bestimmte Abfragen dadurch um einen Faktor 2-3 beschleunigen. Nur bei Abfragen
in der sich die IN-Klausel auf eine Spalte
mit Clustered Index bezieht war kein Unterschied feststellbar auch wenn laut Ausführungsplan das SQL anders aufgelöst wird.

Danke das du mich auf diese Idee gebracht hast.
Dafür gibt's hier meinen überarbeiteten Code.

<PRE>
CREATE FUNCTION fn_StringListToRecordset(@cList text)
RETURNS @retTable TABLE(Value varchar(50))
AS
BEGIN
DECLARE @aValue varchar(50),
@aStartChar int,
@aEndChar int;

IF DATALENGTH ( @cList ) > 2
BEGIN
SET @aStartChar = 1;
WHILE (CHARINDEX(',', @cList,@aStartChar) <> 0)
BEGIN
SET @aEndChar = CHARINDEX(',', @cList,@aStartChar);
SET @aValue = SUBSTRING(@cList, @aStartChar, @aEndChar - @aStartChar);
SET @aStartChar = @aEndChar + 1;
INSERT INTO @rettable(Value) VALUES(RTRIM(LTRIM(@aValue)));
END

SET @aValue = SUBSTRING(@cList, @aStartChar, DATALENGTH(@cList) - @aStartChar + 1);
INSERT INTO @rettable(Value) VALUES(RTRIM(LTRIM(@aValue)));
END
RETURN
END
</PRE>

Grüße Ral

[Tobias Frost]

Hi Ralf !

Freut mich, daß ich mal etwas produktives beisteuern konnte. Vielleicht macht es noch Sinn, @retTable zu indizieren ?!

RETURNS @retTable TABLE(Value varchar(50) NOT NULL PRIMARY KEY)

Gruß, Tobia