Ausführung einer StoredProcedure sehr langsam

[Gor1]

Hallo,

ich habe in meiner Firebird-Datenbank eine StoredProcedure, die für die Ausführung z.B. in FlameRobin knapp zwei Sekunden benötigt. Wenn ich diese StoredProcedure aber aus Delphi heraus über DBExpress aufrufe, dann benötigt das ExecSQL 16 Sekunden (wenn ich Firebird Embedded benutze sogar 27 Sekunden).
Ich habe auch versucht, das nicht mit einer StoredProcedure sondern direkt als SQL-Befehl zu machen, aber mit dem gleichen Ergebnis.
Die StoredProcedure führt folgenden SQL-Befehl aus:
DELETE FROM TABELLE1 WHERE PARAM1 NOT IN (SELECT DISTINCT PARAM1 FROM TABELLE2)
Ich habe auch schon versucht, das DISTINCT durch ein GROUP BY zu ersetzen, aber das hat auch nichts gebracht.

Hat einer eine Idee, was ich da noch probieren könnte?

Schöne Grüße,
Georg

[Iwo Asnet]

Sind die 2 Sekunden in Flamerobin mit oder ohne Commit?

Dessenungeachtet ist eine der Schwächen von Firebird das Löschen von Datensätzen (DELETE).
Das ist leider langsam.

[Blup]

Ohne Kenntnis der Tabellenstruktur (insbesondere welchen Index es gibt) und ohne Kenntnis über die Daten ist es schwierig, Hinweise zur Optimierung zu geben.
Ich gehe davon aus, es existiert zumindest für Param1 in beiden Tabellen ein Index, aber Param1 ist in beiden Tabellen nicht eindeutig.

Code:

/* Liste aller Param1 in Tabelle1 durchgehen */
for select distinct param1
from       tabelle1
into       :param1
do begin
  /* ist dieser Param1 mindestens einmal in Tabelle2 enthalten */
  p = null;
  select param1 from tabelle2 where param1 = :param1 rows 1 into :p;
  if (p is null) then
  begin
    delete from tabelle1 where param1 = :param1;
  end
end

Falls Rows in deinem SQL-Dialekt nicht existiert, gibt es zumindest ein gleichwertiges Schlüsselwort.

[DeddyH]

Ginge es nicht auch so?

SQL-Code:

DELETE FROM 
  TABELLE1 
WHERE 
  NOT EXISTS(
    SELECT 
      * 
    FROM 
      TABELLE2
    WHERE
      TABELLE2.PARAM1 = TABELLE1.PARAM1
  )

[Gor1]

Zitat:

Zitat von Iwo Asnet (Beitrag 1120522)

Sind die 2 Sekunden in Flamerobin mit oder ohne Commit?

Dessenungeachtet ist eine der Schwächen von Firebird das Löschen von Datensätzen (DELETE).
Das ist leider langsam.

Die 2 Sekunden sind ohne Commit, allerdings braucht der Commit auch nur einen Bruchteil einer Sekunde.

Ich habe auch mal versucht, das DELETE durch ein SELECT PARAM1 zu ersetzen (so dass ich nur die zu löschenden Zeilen bekomme), auch diese SQL-Abfrage ist praktisch genauso langsam wie das ursprüngliche DELETE.

Deshalb habe ich die gesamte Abfrage in einzelne Teile zerlegt und den Vergleich ob der Parameter in der anderen Tabelle vorhanden ist in Delphi gemacht. Damit komme ich dann auf eine Ausführungszeit ähnlich wie beim Ausführen über Flamerobin, allerdings ist das natürlich keine sehr elegante Lösung.

Danke an alle für eure Vorschläge, ich werde sie mal ausprobieren.

[Gor1]

Zitat:

Zitat von DeddyH (Beitrag 1120532)

Ginge es nicht auch so?

SQL-Code:

DELETE FROM 
  TABELLE1 
WHERE 
  NOT EXISTS(
    SELECT 
      * 
    FROM 
      TABELLE2
    WHERE
      TABELLE2.PARAM1 = TABELLE1.PARAM1
  )

Danke für diesen Tip. Ich denke, dass das auch das gewünschte Ergebnis liefern würde. Diese Variante ist auch schon mal doppelt so schnell wie meine ursprüngliche, allerdings halt immer noch deutlich langsamer als mit Flamerobin.

[Gor1]

Zitat:

Zitat von Blup (Beitrag 1120531)

Ohne Kenntnis der Tabellenstruktur (insbesondere welchen Index es gibt) und ohne Kenntnis über die Daten ist es schwierig, Hinweise zur Optimierung zu geben.
Ich gehe davon aus, es existiert zumindest für Param1 in beiden Tabellen ein Index, aber Param1 ist in beiden Tabellen nicht eindeutig.

Code:

/* Liste aller Param1 in Tabelle1 durchgehen */
...

Falls Rows in deinem SQL-Dialekt nicht existiert, gibt es zumindest ein gleichwertiges Schlüsselwort.

Ich habe deinen Code mal in einer StoredProcedure ausprobiert, damit geht es deutlich schneller. Allerdings gibt es Fälle, in denen grosse Teile der Tabelle1 gelöscht werden und dabei ist diese Variante nicht so schnell (ich nehme an, weil für jede zu löschende Zeile ein DELETE aufgerufen wird).

[tsteinmaurer]

Und du bist dir sicher, dass du in allen Fällen dich zur selben Datenbank verbindest, d.h. für alle Fälle das Datengerüst identisch ist.

[Gor1]

Zitat:

Zitat von tsteinmaurer (Beitrag 1120627)

Und du bist dir sicher, dass du in allen Fällen dich zur selben Datenbank verbindest, d.h. für alle Fälle das Datengerüst identisch ist.

Ja, bin ich. Ich habe die Ausführungszeit für die verschiedenen Möglichkeiten auch mehrmals gemessen (in meinem Delphi-Programm). Da die Ausführung nur im Delphi-Programm so langsam ist (aber nicht in Flamerobin) vermute ich, dass das Problem irgendwo in Delphi bzw. den DBExpress-Treibern liegt und nicht in der Firebird-Datenbank. Momentan verwende ich Delphi 2009 mit dem dbxFirebird-Treiber.
Ich werde bei Gelegenheit auch mal andere Zugriffskomponenten ausprobieren oder auch DBExpress mit einer neueren Delphi-Version. Ich bin mal gespannt, ob sich da das gleiche Verhalten zeigt.

[FredlFesl]

Das kann doch aber nicht sein, das ein Treiber 14 Sekunden benötigt.

Nachdem ich schon ein paar dämliche Fragen gestellt habe, hier noch eine:

Du bist dir sicher, das das Ergebnis reproduzierbar ist? Ich frag deshalb, weil ja theoretisch erst in Delphi gemessen wurde und danach in Flamerobin, wo dann aber die DB-Engine schon ihren Cache gefüllt hat und so die Zugriffe viel schneller gehen...

Also ich hab sonst keine Erklärung für diesen Quark.

Ich hatte das mal mit MSSQL, aber da war es die Namensauflösung, die 30 Sek gedauert hat, weil irgendwas im Netz im Eimer war, irgend eine Einstellung. Vielleicht geht Flamerobin ja nicht über TCP, aber dbExpress...

[hoika]

Hallo,

warum sollte es an den Komponenten liegen ?
Vor allem bei so einem grossen Unterschied.

Wie sieht denn der Query-Plan aus ?

Bei Tests sollte auch folgendes beachtet werden ?

1. Start ist immer die gleiche DB (also vorher kopieren)
2. DB kopieren
3. Neustart des Rechners (+ Servers)
4. Test 1
6. DB kopieren
7. Neustart des Rechners (+ Servers)
8. Test 2


Heiko

[jobo]

Zitat:

Zitat von Gor1 (Beitrag 1120498)

DELETE FROM TABELLE1 WHERE PARAM1 NOT IN (SELECT DISTINCT PARAM1 FROM TABELLE2)

Was soll dabei rauskommen? Was ist Param1? Ein SP Parameter, z.b. 20 oder 42? Ein Feldname?
42 wäre dann gebunden:
DELETE FROM TABELLE1 WHERE 42 NOT IN (SELECT DISTINCT 42 FROM TABELLE2)

Wenn es ein Feldname wäre, statt eines Parameters, dann ist doch die Frage, wie das Mengengerüst aussieht.
Die Frage nach der Reproduzierbarkeit wurde ja schon gestellt.

Ich bin kein Firebird Experte, aber das Statement kommt mir sehr merkwürdig vor.

Wie siehts im Client mit Transaktionen aus? Wird hier vielleicht nicht nur commited, sondern noch die Transaktionskomponente benutzt?
Das wäre für mich ein Indikator für große Performance Einbrüche im Vergleich zu einem nackten SP Aufruf auf dem Server.

[Gor1]

Nochmal zur Klarstellung:
Param1 ist ein (Integer-)Feld sowohl in Tabelle1 als auch in Tabelle2. In Tabelle1 liegen Daten, die zu einem oder mehreren Datensätzen in Tabelle2 gehören und die Zuordnung erfolgt über Param1.
Es gibt jetzt eben Fälle, in denen Datensätze aus Tabelle2 gelöscht werden.
Danach sollen die Datensätze aus Tabelle1 herausgelöscht werden, die keinen zugeordneten Datensatz in Tabelle2 mehr haben (also verwaist sind) und das ist was der DELETE-Befehl machen soll.

Die Ergebnisse sind sowohl im Delphi als auch in Flamerobin reproduzierbar und auch wenn ich mein Delphi-Programm direkt ohne IDE starte, bekomme ich die gleichen Laufzeiten. Die Datenbank ist immer die gleiche und bei der Datenbank, die ich benutzt habe, gibt es keine verwaisten Einträge in Tabelle1, so dass sich die Datenbank auch bei mehrmaligem Aufruf des DELETE-Befehls nicht ändert.

Die Datenbank-Datei ist lokal auf meinem Rechner, der Firebird-Server ist auch lokal installiert, so dass das Netzwerk eigentlich keine Rolle spielen dürfte.

Ich verwende eine TSQLQuery und eine TSQLConnection und schicke das Kommando mit ExecSQL an den Server.

Ich bin noch ein Datenbank-Neuling, deshalb verstehe ich nicht, was mit Query-Plan und mit Transaktionskomponente gemeint ist.

[mkinzler]

Ich würde auf jeden Fall Fremschlüsselbeziehung erstellen und in ihr u.U. eine Löschregel hinterlegen, dann wird der abhängige Datensatz automatisch gelöscht.

[Gor1]

Zitat:

Zitat von mkinzler (Beitrag 1120908)

Ich würde auf jeden Fall Fremschlüsselbeziehung erstellen und in ihr u.U. eine Löschregel hinterlegen, dann wird der abhängige Datensatz automatisch gelöscht.

Wie legt man eine Fremschlüsselbeziehung an? Kannst du mir das erklären oder hast du einen Link dazu?

In diesem Fall ist das keine 1:1-Verknüpfung, d.h. es kann sowohl in Tabelle1 als auch in Tabelle2 mehrere Datensätze mit dem gleichen Param1 geben (und die Datensätze in Tabelle1 dürfen nur dann gelöscht werden, wenn es keinen Datensatz in Tabelle2 mit diesem Param1 gibt).
Es ist auch möglich, dass in Tabelle2 alle Datensätze mit einem bestimmten Param1 gelöscht wurden, aber dann später neue Datensätze mit diesem Param1 hinzugefügt werden. Deshalb soll die Tabelle1 nur dann bereinigt werden, wenn das von außen angestoßen wird.