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...