Firebird Insert

[lxo]

Hallo,

ich hab eine Prozedur in Firebird geschrieben um Datensätze zu kopieren.
In dem Fall wären es Anzeigeoptionen für Wirkstoffe.
Diese kann man mit der Prozedur von einem Werk in ein anderes kopieren.

Das geht schon relativ schnell ca. 20.000-25.000 Inserts pro Sekunde.

Meine Frage, könnte man das noch optimieren bzw. noch schneller machen.
Hat da jemand eine Idee?

Code:

  procedure KOPIERE_ANZEIGEOPTIONEN ( X_QUELLE_WIRKSTOFF type of column WIRK_ANZEIGEOPTIONEN.WIRKSTOFF,
                                            X_ZIEL_WIRKSTOFF type of column WIRK_ANZEIGEOPTIONEN.WIRKSTOFF,
                                            X_QUELLE_WERKNR type of column WIRK_ANZEIGEOPTIONEN.WERKNR,
                                            X_ZIEL_WERKNR type of column WIRK_ANZEIGEOPTIONEN.WERKNR)
  as
  begin
    delete from WIRK_ANZEIGEOPTIONEN WA
    where WA.WIRKSTOFF = :X_ZIEL_WIRKSTOFF and
          WA.WERKNR = :X_ZIEL_WERKNR;

    insert into WIRK_ANZEIGEOPTIONEN (WERKNR, WIRKSTOFF, ANZEIGE, DRUCK)
    select :X_ZIEL_WERKNR, :X_ZIEL_WIRKSTOFF, WA.ANZEIGE, WA.DRUCK
    from WIRK_TIERART WA
    where WA.WIRKSTOFF = :X_QUELLE_WIRKSTOFF and
          WA.WERKNR = :X_QUELLE_WERKNR;
  end

[hoika]

Hallo,
1. die Indizes von WIRK_ANZEIGEOPTIONEN temporär deaktivieren (Alter Index)
2. Das Select dahingehend prüfen, ob Indizes benutzt werden
3. Backup/Restore, um die DB optimal aufzubauen

Das 3. ist aber eigentlich nicht notwendig.

[IBExpert]

auf jeden fall drauf achten das da keine alten transaktionen auf der db offen sind, weil du damit sonst bei den deletes immer nur recordversionen erzeugst
und ganz wichtig auf der tabelle in der du schreibst auch keine unnötigen indizes haben, möglichst keine unnötig langen varchar deklarationen usw

mit ca 25000 pro sekunde ist das aber schon klagen auf hohem nieveau

Hardwarespeed mal über ibexpert benchmark messen ist sicherlich auch ein hinweis

[lxo]

Danke für die Rückmeldungen.
Extra Indizes hab ich für die Tabelle jetzt nicht, nur ein Primärschlüssel und zwei Fremdschlüssel.

Ich bin auch soweit zufrieden mit dem Ergebnis, dachte nur vielleicht gibt es da noch Tipps und Tricks da noch etwas herauszukitzeln.

Das Kopieren direkt mit Firebird im Gegensatz, vorher über Delphi schon um das ca. 400-fache schneller geworden.

[Neumann]

Anstatt den Datensatz in der Zieltabelle erst zu suchen, dann zu löschen und dann Insert zu machen könnte man ev. auch Update or Insert machen.
Ob das schneller ist kann ich aber nicht sagen.

[lxo]

Ich bräuchte nochmal eure Hilfe, an sich funktioniert das alles ganz gut mit der Kopier-Prozedur bei Datenbanken mit mehreren Millionen-Datensätzen ist das aber ganz schön langsam alles zu löschen und neu anzulegen für ein Werk.

Alle Wirkstoffe kopieren von ein Werk ins andere wäre aktuell so.

Code:

  procedure KOPIERE_ANZEIGEOPTIONEN ( X_QUELLE_WERKNR type of column WIRK_ANZEIGEOPTIONEN.WERKNR,
                                            X_ZIEL_WERKNR type of column WIRK_ANZEIGEOPTIONEN.WERKNR)
  as
  begin
    delete from WIRK_ANZEIGEOPTIONEN WA
    where WA.WERKNR = :X_ZIEL_WERKNR;

    insert into WIRK_ANZEIGEOPTIONEN (WERKNR, WIRKSTOFF, ANZEIGE, DRUCK)
    select :X_ZIEL_WERKNR, :X_ZIEL_WIRKSTOFF, WA.ANZEIGE, WA.DRUCK
    from WIRK_TIERART WA
    where WA.WIRKSTOFF = :X_QUELLE_WIRKSTOFF and
          WA.WERKNR = :X_QUELLE_WERKNR;
  end

Ich habe mir gedacht das es doch effizienter sein sollte wenn ich nur die Wirkstoffe lösche die es in der Quelle nicht gibt.
Da habe ich dann an "Update or Insert" oder "Merge Into" gedacht ich schaffe es damit jedenfalls alle nötigen Datensätze zu aktualisieren und ggf. fehlende hinzuzufügen.
Schaffe es aber nicht alle nicht mehr nötigen aus dem Ziel zu löschen.

Was ich genau erreichen möchte:
Alle Anzeigeoptionen von Werk 1 nach Werk 2 kopieren.
Alle bisherigen Einträge für Werk 2 sollen überschrieben werden.
Daher ...
- bei Datensätzen die zusammenpassen ein Update.
- bei Datensätzen die im Ziel nicht gefunden werden Insert
- bei Datensätzen die in der Quelle nicht gefunden werden delete.

[Delphi.Narium]

Gehe mal davon aus, dass WERKNR, WIRKSTOFF einen eindeutigen Wert darstellen.

Mit etwas in der Art könnte dann das Löschen etwas einfacher werden:

SQL-Code:

delete from Tabelle_Werk2 w2 
where not exists (
  select 1 from Tabelle_Werk1 w1 
  where w1.WERKNR = w2.WERKNR
  and w1.WIRKSTOFF = w2.WIRKSTOFF
)

Du brauchst aber immer zwei Statements, das für's Insert bzw. Update und das zum Löschen. Eine Möglichkeit, dies alles in einem Statement umzusetzen, ist mir nicht bekannt.

[lxo]

Zitat von Delphi.Narium:

Gehe mal davon aus, dass WERKNR, WIRKSTOFF einen eindeutigen Wert darstellen.

Mit etwas in der Art könnte dann das Löschen etwas einfacher werden:

SQL-Code:

delete from Tabelle_Werk2 w2 
where not exists (
  select 1 from Tabelle_Werk1 w1 
  where w1.WERKNR = w2.WERKNR
  and w1.WIRKSTOFF = w2.WIRKSTOFF
)

Du brauchst aber immer zwei Statements, das für's Insert bzw. Update und das zum Löschen. Eine Möglichkeit, dies alles in einem Statement umzusetzen, ist mir nicht bekannt.

Bei meiner Recherche bin ich darauf gestoßen, dass es z.B. bei MSSQL "merge when not matched by source"gibt.
Nur leider gibt es das bei Firebird nicht und soll wohl auch nicht implementiert werden. http://tracker.firebirdsql.org/browse/CORE-2515

Ich habe in einem Forum dafür trotzdem eine gute Lösung gefunden.
https://www.sql.ru/forum/1323218/mer...ched-by-source

Ich habe beim "Firbird tracker" ein Ticket zu dem Thema nochmal angelegt. Für alle die es vielleicht interessiert.
http://tracker.firebirdsql.org/browse/CORE-6448