SQL viel zu langsam

[MyRealName]

Hallo,
Ihr wisst, ich sucke ziemlich mit SQL, deswegen mal 'ne Frage...

Ich habe eine Detail-Tabelle für Konstruktionen. Im Normalfall gibt es 1-n Register für die Zutaten und 1 Register für das Endprodukt. Zutaten sind in einem Feld Transcode mit "S" gekennzeichnet, das Endprodukt mit "EI" (fragt mich nicht warum "EI", "S" dürfte "Salida" sein und "EI" könnte "Entrada Inventario" sein.. ich hab mir das nicht einfallen lassen.

Was ich jetzt schreiben muss und schon habe ist eine Funktion (Stored Procedure), welche die Kosten der Zutaten ausliest aus der Inventarliste, wenn es ein Einkauf/Zugang war, diese dann in der Tabelle ersetzt, wo ein Trigger die Kosten des Endprodukts neu berechnet. Das geht auch.

Nun das Problem : Ich habe ja oft auch den Fall, dass ein Endprodukt eine Zutat für ein anderes ist. Ich muss also zuerst die berechnen, die das nicht sind. dazu erstelle ich mir erstmal eine Liste derer, die nur den TransCode="S" haben, aber nicht "EI"
Nun habe ich 244.000 Register in der Tabelle (aber ein "SELECT DISTINCT" bringt mir nur 1800 rund mit TransCode="S")
Habe das über Nacht mal laufen lassen in der DB eines Kunden, der das halt viel nutzt.
Ergebnis : Execute time = 2h 5m 41s 516ms

Code:

SELECT DISTINCT ED1.Item, ED1.Location, ED1.ItemID
      FROM EnsambleD ED1
      WHERE ED1.TransCode='S ' AND
            NOT EXISTS (SELECT DISTINCT ED2.Itemid FROM EnsambleD ED2 WHERE ED2.Itemid=ED1.Itemid AND ED2.TransCode='EI')

Also der Code kam erst garned wieder, ich musste firebird abschiessen :)
Ich habe einen Index auf ItemID liegen, ist aber nicht unique, offensichtlich

Also dachte ich an etwas mehr traditionell und habe es so probiert :

Code:

SELECT DISTINCT ED1.Item, ED1.Location, ED1.ItemID,
                (select Count(ED2.ItemID) from ensambleD ED2 WHERE ED2.TransCode='EI' And ED2.ItemID=ED1.ItemID)
      FROM EnsambleD ED1
      WHERE ED1.TransCode='S ' AND

Das ging dann, hat aber 2 Stunden gedauert. Das ist natürlich nicht akzeptabel, weder für mich, noch den Kunden. Dessen Server ist bei weitem nicht so schnell wie mein PC ;)

Mache ich was falsch im Query ? Es zeigt mit 1.110.310.663 indexed reads an, nichts rot...
Sollte ich mir das lieber in Delphi bauen ?

[mkinzler]

Existieren Indices auf den Feldern für die Kombinationen?

[baumina]

Meine Empfehlung wären folgende Indizes:

1. EnsambleD.ItemID
2. EnsambleD.TransCode

[MyRealName]

Es existieren indices auf ItemID, Transcode, Item und Location (letztere beiden durch einen FK), die ersten hatte ich schon gemacht.

[jobo]

Was sagt der Ausführungsplan?

Ich würde einfach mal einen "normalen" Outerjoin probieren und auf der ehemaligen not exists Menge auf "Null" einschränken. Not exists verwende ich meist nur bei garantiert kleinen Suchmengen, erfahrungsgemäß ist das realtiv selten performant.
Allein die Formulierung der Abfrage bzw. das Verfahren lässt das m.E. erahnen, da es relativ "ungenau" ist. Diese losen Anforderungen durchzuprüfen ist halt aufwendig.

[MyRealName]

Technisch gesehen müsste EXISTS gerade wschnell sein, weil es eine Boolean-Abfrage ist. Count zum Beispiel muss durch alle Register rennen un dir die genaue Anzahl zu sagen, EXISTS nur bis zum ersten Vorkommen.

ich probiere es gerade wie Du sagtest mit einem LEFT JOIN

Code:

SELECT DISTINCT ED1.Item, ED1.Location, ED1.ItemID
FROM EnsambleD ED1
LEFT JOIN EnsambleD ED2 ON (ED1.Itemid = ED2.Itemid AND Ed2.Transcode='EI')
WHERE ED1.Transcode='S '

läuft schon einige Minuten, mal sehen, wann der fertig wird :D

[MyRealName]

Enttäuschend : gerade schnell ein kleines Programm geschrieben welches das einfach über Locate in einem TVirtualTable macht (also mit 2 VTs). Dauerte ungefähr 10 Sekunden um die Liste zu haben.

Kann Firebird nicht einfach 2 Listen machen und das abgleichen ? Kann doch nicht so schwer sein...

[hoika]

Hallo,

die Frage nach dem Ausführungsplan hast du leider noch nicht beantwortet ...


Heiko

[mkinzler]

Und nach den Indizes auch nicht

[MyRealName]

Den Ausführungsplan sieht man erst nach dem Ausführen.. der führt immer noch aus. :shock:
Die Indices haben leider nicht geholfen :( Hatte sie ja schon auf allen 4 Feldern als es 2 Stunden dauerte...

[Jumpy]

[OT]

Hatte mir das Freitag zufällig mal reingezogen, weil dazu eine Mail von Emba kam. Fand es eigentlich ganz interessant, da es (zwar bezogen auf Oracle) einige Hinweise gibt, wie man die Performance seiner Abfragen verbessern kann.

https://www.youtube.com/watch?v=RMc1...ature=youtu.be

[/OT]

[mkinzler]

Du benötigst einen Index auf Itemid und Transcode. Auf alle 4 und auf die einzelnen hilft ja nichts.

[MyRealName]

Zitat:

Zitat von mkinzler (Beitrag 1260279)

Du benötigst einen Index auf Itemid und Transcode. Auf alle 4 und auf die einzelnen hilft ja nichts.

Dieses Query läuft jetzt auch schon 10 Minuten ohne Ergebnis :(

Fertig nach 11 Minuten

Plan
PLAN SORT (JOIN (ED1 INDEX (ENSAMBLED_IDX_TRANSCODE), ED2 INDEX (ENSAMBLED_IDX1_ITEMID_TC)))

ENSAMBLED_IDX1_ITEMID_TC = Index auf ItemID und Transcode zusammen.

[Dejan Vu]

Dann könnte man noch so vorgehen:

Code:

select * from (
  SELECT DISTINCT 
   ED1.Item, 
   ED1.Location, 
   ED1.ItemID
  FROM 
   EnsambleD ED1 
  WHERE ED1.TransCode = 'S'
) x where not exists (
  SELECT DISTINCT ED2.Itemid 
    FROM EnsambleD ED2 
   WHERE ED2.Itemid=x.Itemid AND ED2.TransCode='EI') 
)

Nun sind vielleicht weniger Kandidaten zum prüfen auf not exists...

[MyRealName]

@DejaVu
Das query braucht 10m14s

Das originale von ganz am Anfang mit dem NOT EXISTS braucht 10m08s

Um Längen besser als die 2h von vorher. Danke, mkinzler und alle anderen...

[Dejan Vu]

Zitat:

Zitat von MyRealName (Beitrag 1260296)

Das query braucht 10m14s...

Trotzdem merkwürdig, das das so lange dauert...

[MyRealName]

Definitiv. Werd es wohl ncohmal neu schreiben, aber in Delphi... da brauch ich unter 10 Sekunden für diese Liste.

[himitsu]

Zitat:

SQL-Code:

SELECT DISTINCT ED1.Item, ED1.Location, ED1.ItemID
      FROM EnsambleD ED1
      WHERE ED1.TransCode='S ' AND
            NOT EXISTS (SELECT DISTINCT ED2.Itemid FROM EnsambleD ED2 WHERE ED2.Itemid=ED1.Itemid AND ED2.TransCode='EI')

Was mir hier in den Sinn kommt: Ist der Query-Planer eigentlich intelligent genug, um zu erkennen, daß die Ergebnisliste des SubSelects eigentlich garnicht nötig ist?

SQL-Code:

SELECT DISTINCT Item, Location, ItemID
      FROM EnsambleD 
      WHERE TransCode='S ' AND
            NOT EXISTS (SELECT True FROM EnsambleD AS ED2 WHERE ED2.Itemid=EnsambleD.Itemid AND ED2.TransCode='EI' LIMIT 1)

[Dejan Vu]

Du meinst, wegen dem 'DISTINCT'?

Laut einschlägigen Foren ist folgendes Äquivalent (wobei man FB nie wissen kann)
... where exists (select 1 from foobar)
--
... where exists (select * from foobar)

weil ja nicht die Ergebnismenge erzeugt wird, sondern beim ersten Treffer TRUE geliefert wird.

[himitsu]

Zitat:

Zitat von Dejan Vu (Beitrag 1260326)

Laut einschlägigen Foren ist folgendes Äquivalent (wobei man FB nie wissen kann)
... where exists (select 1 from foobar)
--
... where exists (select * from foobar)

Eigentlich vorallem deswegen.
Also Diesbezüglich soll der also intelligent genug sein.

Wenn erst die komplette Liste erzeugt und danach erst das EXISTS ausgewertet würde, dann wäre das ja unnütze Arbeit.

[Uwe Raabe]

Keine Ahnung wie performant das ist, aber eventuell kann man auch das mal versuchen:

SQL-Code:

SELECT DISTINCT 
  ED1.Item, ED1.Location, ED1.ItemID
FROM EnsambleD ED1
LEFT JOIN EnsambleD ED2 ON ED1.ItemId = ED2.ItemID
WHERE ED1.TransCode='S ' 
  AND ED2.TransCode='EI'
  AND ED2.ItemID IS NULL

[jobo]

Zitat:

Zitat von Uwe Raabe (Beitrag 1260365)

Keine Ahnung wie performant das ist, aber eventuell kann man auch das mal versuchen:

SQL-Code:

SELECT DISTINCT 
  ED1.Item, ED1.Location, ED1.ItemID
FROM EnsambleD ED1
LEFT JOIN EnsambleD ED2 ON ED1.ItemId = ED2.ItemID
WHERE ED1.TransCode='S ' 
  AND ED2.TransCode='EI'
  AND ED2.ItemID IS NULL

Das meinte ich mit meinem Vorschlag.

[jobo]

Zitat:

Zitat von MyRealName (Beitrag 1260249)

Technisch gesehen müsste EXISTS gerade wschnell sein, weil es eine Boolean-Abfrage ist. Count zum Beispiel muss durch alle Register rennen un dir die genaue Anzahl zu sagen, EXISTS nur bis zum ersten Vorkommen.

Ob das Boolean ist, dürfte m.E. keine Rolle spielen, besser als Count könnte es sein, wenn der Lauf wirklich beim 1 Treffer abgebrochen wird.
Der entscheidende Punkt ist, wie intelligent Firebird die Unterabfrage mit Referenz zu Hauptmenge erstellt. Die Ausführungsdauer sieht danach aus, das mindestens eine der Tabellen immer wieder abgefragt wird.

Das könnte mit meinem Vorschlag bzw konkret Uwes SQL evtl. besser gehen.

[MyRealName]

Zitat:

Zitat von Uwe Raabe (Beitrag 1260365)

Keine Ahnung wie performant das ist, aber eventuell kann man auch das mal versuchen:

SQL-Code:

SELECT DISTINCT 
  ED1.Item, ED1.Location, ED1.ItemID
FROM EnsambleD ED1
LEFT JOIN EnsambleD ED2 ON ED1.ItemId = ED2.ItemID
WHERE ED1.TransCode='S ' 
  AND ED2.TransCode='EI'
  AND ED2.ItemID IS NULL

Plan
PLAN SORT (JOIN (ED1 INDEX (ENSAMBLED_IDX_TRANSCODE), ED2 INDEX (ENSAMBLED_IDX1_ITEMID_TC)))

Execute time = 11m 3s 407ms

[jobo]

Ist der Index immernoch kombiniert?
Trenn die doch mal auf, jede Spalte einzeln.

[MyRealName]

Zitat:

Zitat von jobo (Beitrag 1260475)

Ist der Index immernoch kombiniert?
Trenn die doch mal auf, jede Spalte einzeln.

Hatte ich doch vorher, mkinzler empfohl mir, einen zu machen über die beiden Spalten. Dadurch ist es viel schneller geworden (von 2h auf 10m)