Komplexe SQL-Abfrage optimieren

[omp]

Hallo. Wir haben ein Dokumentenarchiv mit einem einfachen Volltextindex. Die SQL-Abfrage sieht beispielsweise wie folgt aus:

Code:

SELECT * FROM document 
WHERE docid IN (SELECT id.docid FROM ftiwid id INNER JOIN ftiword w ON id.wordid = w.wordid WHERE (w.word LIKE {s gesuchteswort%}) )

Kommt ein neues Dokument in die Datenbank, muss dieses über die docid der Abfrage hinzugefügt werden:

Code:

SELECT * FROM document 
WHERE docid=200001 
OR docid IN (SELECT id.docid FROM ftiwid id INNER JOIN ftiword w ON id.wordid = w.wordid WHERE (w.word LIKE {s gesuchteswort%}) )

Tests mit Datenbank bis ca. 80.000 Datensätzen sind problemlos und schnell. In einem Archiv mit 200.000 Zeilen tritt jetzt aber ein echtes Problem auf:
Die erste Abfrage ist ok und in 2 Sekunden fertig.
Die zweite Abfrage mit "WHERE docid=200001 OR" benötigt aber über eine Stunde Zeit!

Hat jemand eine Idee, was dahintersteckt?
Besten Dank, Harald

[omnibrain]

Hast du dir schon den Query-Execution-Plan angeschaut?
https://learn.microsoft.com/en-us/sq...l-server-ver16

[Jasocul]

Zunächst wird die größere Datenmenge eine Rolle spielen. Aber das ist wohl offensichtlich :wink:
eine Prüfung auf "in" wird von der DB als "or" für alle gefundenen Werte umgewandelt. Bei vielen Elementen ist das keine besonders optimale Lösung. Falls MS-SQL das intern besser löst, solltest du das zusätzliche Dokument in das "in" integrieren. Also nach dem Select als weitere ID hinzufügen.

Bei potentiell vielen Treffern würde ich das umbauen und aus der IN-Prüfung eine EXISTS-PRüfung machen. Also etwa so:

Code:

SELECT * FROM document as d
where exists (SELECT id.docid FROM ftiwid id INNER JOIN ftiword w ON id.wordid = w.wordid WHERE (w.word LIKE {s gesuchteswort%}) and id.docid = d.docid)
union all -- um das or komplett rauszuschmeißen
SELECT * FROM document
WHERE docid=200001

Du kannst das ja mal testen :wink:

[Delphi.Narium]

Was wird hier eigentlich gemacht?

Zuerst brauchen wir ein bestimmtes Wort (bzw. eine Liste der IDs zu Wörtern, die dem Suchbegriff ähneln):

SQL-Code:

select wordid from ftiword where (w.word like {s gesuchteswort%})

Zu diesem Ergebnis möchten wir alle DocID:

SQL-Code:

select id.docid from ftiwid id where exists (
  select 1 from ftiword w where (w.word like {s gesuchteswort%}) and id.wordid = w.wordid
)

Zu diesem Ergebnis benötigen wir nun alle Dokumente:

SQL-Code:

select * from document a where exists 
(
  select 1 from ftiwid id where exists 
  (
    select 1 from ftiword w where (w.word like {s gesuchteswort%}) and id.wordid = w.wordid
  ) and id.docid = a.docid
)

Keine Ahnung, ob MSSQL mit so'ner Syntax zurecht kommt.

Warum wird im zweiten SQL nach einer konkreten ID gesucht und zusätzlich noch in einer Liste von IDs?

Hier würd' ich's dann mit 'nem Union versuchen:

SQL-Code:

select * from document where docid = 200001 
union
select * from document a where exists 
(
  select 1 from ftiwid id where exists 
  (
    select 1 from ftiword w where (w.word like {s gesuchteswort%}) and id.wordid = w.wordid
  ) and id.docid = a.docid
)

Wenn das nicht geht:

SQL-Code:

select * from document where docid = 200001 
union
SELECT * FROM document
WHERE docid IN (SELECT id.docid FROM ftiwid id INNER JOIN ftiword w ON id.wordid = w.wordid WHERE (w.word LIKE {s gesuchteswort%}) )

[TigerLilly]

Nicht raten. Messen.
Schau dir den Execution Plan an, dann weißt du, wo die Zeit liegen bleibt.
Gerne übersehen: Der Aufwand für das Locking bei Abfragen. Ergänze mal ein WITH (NOLOCK) nach den Tabellennamen (und sei dir der Konsequenzen bewusst).
Nutze den Optimzer, der gibt dir zielgerichtet Tipps zu deiner Query.
Ein Index (wenn er denn genutzt wird) kann böse defragmentiert sein - REBUILD.
Die Statistiken, die steuern, ob ein Index genutzt wird, können überholt sein - aktualisieren.

Erst dann kannst du versuchen, die Query umzubauen: CTEs, temporäre Tabellen etc.
ChatGPT ist auch ein guter Tipp, nicht alles funktioniert, aber du bekommst neue Ideen.

[Blup]

Die äußere Abfrage liefert nur die Dokumente zurück, deren ID bereits ermittelt wurde.
Ob mit "or", "in" oder "exists" dürfte kaum einen großen Unterschied darstellen.

Die eigentliche Bremse könnte die Verknüpfung aller Worte mit allen Dokumenten darstellen.
Neben dem verwendeten Index wirkt sich auch der Speicherverbrauch der Abfrage schnell auf die Geschwindigkeit aus.

Code:

SELECT     id.docid 
FROM       ftiwid  id 
INNER JOIN ftiword w  ON id.wordid = w.wordid 
WHERE      (w.word LIKE {s gesuchteswort%})

Ich würde es mit einem left join versuchen:

Code:

SELECT     distinct id.docid
FROM       ftiword w
left join  ftiwid  id ON id.wordid = w.wordid
WHERE      (w.word LIKE {s gesuchteswort%})

Mehrfache Rückgabe des selben Dokuments ist hier nicht erwünscht (distinct).
Natürlich könnte man die Anzahl der Dokumente hier sinnvoll z.B. auf die ersten 200 begrenzen.

[TigerLilly]

Ganz vergessen: Mit Version 2022 hast du ja Zugriff darauf:
https://learn.microsoft.com/en-us/sq...l-server-ver16

[TigerLilly]

Und? Fortschritt? Erkenntnisse?