Hilfe bei Optimierung in SQL

[Pro_RJ]

Huhu,
Ich habe die Aufgabe, das ich Datensätze von einer DB in eine ArchivDb zu kopieren.
Der Vorgang an sich ist kein Problem.mir geht es darum, wie kann ich mehr Geschwindigkeit rausholen?Ich mache Im moment ca 1000 Datensätze pro Sekunde. Nur das ist "einwenig" zu wenig, da muss mehr gehen.

Zum Datenbankaufbau:
Die Tabelle besteht aus ca 60 Feldern.
Einem PrimärIndex (Integer) 1 Feld
und einem UniqueIndex (Integer,Integer,Integer,Date,Time,Integer,Integer ) 7 Felder (Der Muss leider so lang sein
und einen FremdIndex (Integer) 1 Feld (ist zu Optimierung von Auswertungen)

Ich habe mir mal ein kleines Testpropgramm geschrieben was einfach mal 30K Datensätze erzeugt und einfügt.
Ich nutze zur Zeit die Standardkomps aus BDS 2006 Version: "InterbaseExpress 12,12"

Delphi-Quellcode:

    
for DS := 1 to 30000 do // Alle Datensätze Duchgehen
    Begin
      FeldListe := '';
      WertListe := '';
      INC(GesGearb);
      for Fld := 0 to q.Fields.Count -1 do     // Alle Felder der Tabelle Durcharbeiten
      Begin
        FeldName := q.Fields[Fld].FieldName;

        if FeldListe <> '' then FeldListe := FeldListe + ',';
        if WertListe <> '' then WertListe := WertListe + ',';
        if ((FeldName = 'ZAEHLER') or (FeldName = 'LO'))
        then Wert := IntToStr(DS)
        else
        Begin
          case q.Fields[Fld].DataType of
          ftSmallint, ftInteger : Wert := IntToStr(123);
          ftFloat,FTBCD         : Wert := '1.23';
          ftDate                : Wert := #39 + DateToStr(NOW) + #39;
          ftTime                : Wert := #39 + TimeToStr(NOW) + #39;
          ftDateTime            : Wert := #39 + DateTimeToStr(NOW) + #39;
          else                    Wert := #39 + 'asd' + #39;
          end;
        end;
        FeldListe := FeldListe + FeldName; 
        WertListe := WertListe + Wert;
      end;
      if sender = Button1 then
      Begin
        IBSQL1.Close;
        IBSQL1.SQL.text := 'Insert into tabelle ('+FeldListe+') values ('+WertListe+')';
        IBSQL1.ExecQuery;
        IBSQL1.Close;
      end;
      if CheckBox1.Checked then Application.ProcessMessages;
    end;

Für diese Schleife mit den 30K Datensätzen brauche ich ca 28-31 Secunden.
Nur die Datenbank in die Eingefügt wird ist absolut Leer. Sodas die Zeit in der Richtigen Datenbank deutlich langsamer sein wird.

Danke schonmal im Vorraus
mfg Jens

[Pro_RJ]

PS: Ich habe es gerade auchmal TIBScript getestet also alle 30K Insert-Statements auf einmal absetzte. DIeser VOrgang ist ca 20%-2525% langsamer :-(

[mkinzler]

Die Feldliste sollte für jeden DS gleich sein, es reicht deshalb sie einmalig zu bilden

[Pro_RJ]

Jup ist sie auch, nur ich muss ja eh alle Felder durcharbeiten,um die Feldwerte zu ermitteln.
PS: das Spart ca 0,05 Secunden

[mkinzler]

Aber nicht bei jedem Datensatz

[Pro_RJ]

ne Insgesamt

[mkinzler]

Du bildest diese bei 30 datensätze 30000mal, also 29999 unötige mal!!!

[Pro_RJ]

ich habs mal umgebaut:
if CheckBox2.Checked
dann FeldListe einmal aufbauen
else Immer neu Bilden

Delphi-Quellcode:

    
if CheckBox2.Checked then
    Begin
      for Fld := 0 to q.Fields.Count -1 do
      Begin
        if Fld > 0 then FeldListe := FeldListe + ',';
        FeldListe := FeldListe + q.Fields[Fld].FieldName;
      end;
    end;


    for DS := 1 to 30000 do
    Begin
      if not CheckBox2.Checked then FeldListe := '';
      WertListe := '';
      INC(GesGearb);
      for Fld := 0 to q.Fields.Count -1 do
      Begin
        FeldName := q.Fields[Fld].FieldName;
        if not CheckBox2.Checked then if FeldListe <> '' then FeldListe := FeldListe + ',';
        if WertListe <> '' then WertListe := WertListe + ',';
        if ((FeldName = 'ZAEHLER') or (FeldName = 'LO'))
        then Wert := IntToStr(DS)
        else
        Begin
          case q.Fields[Fld].DataType of
          ftSmallint, ftInteger : Wert := IntToStr(123);
          ftFloat,FTBCD         : Wert := '1.23';
          ftDate                : Wert := #39 + DateToStr(NOW) + #39;
          ftTime                : Wert := #39 + TimeToStr(NOW) + #39;
          ftDateTime            : Wert := #39 + DateTimeToStr(NOW) + #39;
          else                    Wert := #39 + 'asd' + #39;
          end;
        end;
        if not CheckBox2.Checked then FeldListe := FeldListe + FeldName;
        WertListe := WertListe + Wert;
      end;

Ergebniss:
Feldliste immer aufbauen : 00:30:953
FeldListe einmal aufbauen : 00:30:325
Durchschnitt bei ca 10 Durchläufen

[mkinzler]

Und wenn du die Bildung vor die Schleife ziehst?

[Pro_RJ]

Die Feldliste wird vor der Eigentlichen Schleiche gebildet also nu ein mal. oder wie meinst du das?

[mkinzler]

Delphi-Quellcode:

for Fld := 0 to q.Fields.Count -1 do     // Alle Felder der Tabelle Durcharbeiten
Begin
    FeldName := q.Fields[Fld].FieldName;

    if FeldListe <> '' then FeldListe := FeldListe + ','; 
...
for DS := 1 to 30000 do // Alle Datensätze Duchgehen

[Pro_RJ]

Sorry aber das verstehe ich gerade nicht.

Delphi-Quellcode:

    
    // Felder ermitteln
    //==============================

    if CheckBox2.Checked then
    Begin
      for Fld := 0 to q.Fields.Count -1 do
      Begin
        if Fld > 0 then FeldListe := FeldListe + ',';
        FeldListe := FeldListe + q.Fields[Fld].FieldName;
      end;
    end; // End zu if CheckBox2.Checked then
    // Alle Datensätze durcharbeiten
    //==============================
    for DS := 1 to 30000 do
    Begin
      if not CheckBox2.Checked then FeldListe := '';
      WertListe := '';
      INC(GesGearb);
      for Fld := 0 to q.Fields.Count -1 do
      Begin
        FeldName := q.Fields[Fld].FieldName;
        if not CheckBox2.Checked then if FeldListe <> '' then FeldListe := FeldListe + ',';
        if WertListe <> '' then WertListe := WertListe + ',';
        if ((FeldName = 'ZAEHLER') or (FeldName = 'LO'))
....
        then Wert := IntToStr(DS)

[slemke76]

Hi !

ich weiss nicht, ob du
a.) direkt SQL-Statements an IB Express absetzen kannst und
b.) ob das SQL Query kompatibel ist.

Ich würde es in MySQL folgendermassen machen:
insert into archivdb.archivtabelle select * from aktuelldb.tabelle [where ....]

Danach noch ein entsprechendes delete auf die aktuelldb.tabelle machen (wichtig nur, dass du ein eindeutiges Kriterium für die Übertragung hast, was bis einschließlich des deletes nicht verändert wird -> siehe auch Transaktionssicherheit / Lock Table).

Wenn das gehen sollte, wird damit die Schleife komplett gespart und das sollte richtig Schub bringen :-)

lg
Sebastian

[Pro_RJ]

Jup aber das geht erst in FireBird 2.5 soweit ich weis

[slemke76]

Hi !

wäre definitv ein Grund zum Updaten - aber wahrscheinlich gibt es Gründe dagegen !?

lg

[mkinzler]

Nein geht auch schon vorher

[sx2008]

Delphi-Quellcode:

if sender = Button1 then  // Wozu? Weg mit der If-Abfrage
Begin
  IBSQL1.Close;  // unnötig
  IBSQL1.SQL.text := 'Insert into tabelle ('+FeldListe+') values ('+WertListe+')';
  IBSQL1.ExecQuery;
  IBSQL1.Close;  // unnötig
end;

[Pro_RJ]

Ohh ok das wuste ich nicht. Ab welcher FB-Version geht das schon?

[Pro_RJ]

Zitat:

Zitat von sx2008

Delphi-Quellcode:

if sender = Button1 then  // Wozu? Weg mit der If-Abfrage
Begin
  IBSQL1.Close;  // unnötig
  IBSQL1.SQL.text := 'Insert into tabelle ('+FeldListe+') values ('+WertListe+')';
  IBSQL1.ExecQuery;
  IBSQL1.Close;  // unnötig
end;

Das
"if sender = Button1 then" ist nur da ich verschiedene Versionen teste also TIBSQL,TIBScript usw.

Ich habe es mal in

Delphi-Quellcode:

//      if sender = Button1 then
      Begin
//        IBSQL1.Close;
        IBSQL1.SQL.text := 'Insert into PCBCasioZBPos ('+FeldListe+') values ('+WertListe+')';
        IBSQL1.ExecQuery;
//        IBSQL1.Close;
      end;

Geändert : macht ca 0,02Sekunden aus

[slemke76]

Hi !

Alternativ könnte man noch prüfen, ob die DB

a.) Multiple Inserts unterstützt (insert into tabelle (a,b,c) values (1,2,3), (4,5,6) oder
b.) Die Keys temporär deaktiveren kann (alter table tabelle disable/enable keys)

Ich bin ein wenig "mysql-lastig" :mrgreen:

lg

[mkinzler]

Auch ein prepared query, wäre ein Blick wert.

[Pro_RJ]

Ich liste mal die Einstellung der Beteiligten Combs auf:
IBDatabase1:
IdleTimer : 0
traceFlags : Alles auf flase

IBTransaction1:
AutoStopAction : SaNone
DefaultAction : TACommit
IdleTimer : 0

IBSQL1:
GoToFirstRec. False
ParamCheck : True

Zitat:

Zitat von mkinzler

Auch ein prepared query, wäre ein Blick wert.

Das ist egal da in IBSQL:

Delphi-Quellcode:

procedure TIBSQL.ExecQuery;
var
  fetch_res: ISC_STATUS;
begin
  CheckClosed;
  if not Prepared then
    Prepare;

PS: die IBQuery wird nur zum Ermitteln der Datensätze benötigt.
Die Statements werden über TIBSQL abgesetzt

[mkinzler]

In diesem fall würde ich auf autocommit verzichten und manuell alle x-DS comitten

[Pro_RJ]

AHst du da einen Ungefähren erfahrungswert alle wieviel mal Commit machen sollte.
Aber wenn ich Commit mache scholieße ich ja auch die Query wo meine Datensätze drin sind.Das heist ich muss die Daten ja neu ziehen.
Oder Kann ich da auch ein CommitRetaining machen?
Wobei Ich das Ganze schon gerne in einer ganzen Transaction hätte, da es später in Threads ablaufen soll.

[mkinzler]

Autocommit ruft nach jedem Insert ein CommitRetaining auf, was sich sehr negativ auf die Performance auswirkt. Man könnte sicherlich bis zu 1000 Inserts in einer Transkation ausführen.

[Pro_RJ]

Morgen,
WIe kann ich das AutoCommit ausstellen?

[nahpets]

Hallo,

habe von Interbase, Firebird recht wenig bis keine Ahnung aber bei anderen Datenbanken die Erfahrung gemacht:
Fügt man viele Daten ein und hat dabei 'nen Index zu pflegen (oder gar mehrere), so kann es schneller sein zuerst

• Index löschen

• Dateneinfügen

• Index neu aufbauen

Es ist zumindest einen Versuch wert, wir haben dadurch bei großen Datenmengen schon Tage eingespart.

[Pro_RJ]

Huhu,
Die Idee mit dem Index Deaktivieren ist an sich nicht so verkehrt. das Problem is, das es nur bei Kleinen Tabellen schnell geht.
Nur bei dieser Datenbank ist das problem, das im Moment ca 105 Mio Datensätze in der Tabelle sind, die Einmalig Kopiert werden sollen. der Tägliche Datenabgleich beträgt dann Pro Tag ca 100K Datensätze. Und hier Dauert das Neuaufbauen von Einem Index Locker mal 3 Stunden. :-(

Gibts eventuell andere Komponenten die das Einfügen "schneller" können? Oder gibts eventuell eine ganz andere möglichkeit die Daten zu Kopieren. Ich denke da an die Art und weise, wie es zum Beispiel DataPumb macht oder wie es beim Backup/Restore gemacht wird?

[Blup]

Jede Änderung des SQL-Textes erfordert ein neues Prepare, dehalb beim Insert sinnvoller mit einem festen SQL-Text arbeiten und nur die Parametern ändern.

Alternativ gibt es ab Firebird 2.0 die Möglichkeit mit EXECUTE BLOCK viele Inserts mit einem Komando abzusetzen.
Mit der IBEBlock Scriptsprache (www.ibexpert.com) kann man sogar der gesamte Datentransfer zwischen unterschiedlichen Datenbanken auf dem Server abwickeln.

[nahpets]

Hallo,

könnte hier nur eine Erfahrung mit MS-SQL-Server 2000 beisteuern.

Musste mal eine große Menge von Dateien parsen und die daraus resultierenden Ergebnisse per Insert in drei Tabellen verteilen. Das war grottenlangsam.

Bin dann hergegangen und habe die im Programm erstellen Insert-Statements in einem Memo gesammelt und immer dann, wenn das Memo 20 Zeilen enthielt, den Inhalt des Memos per TQuery.ExecSQL an die Datenbank geschickt. Dadurch wurde der "Spaß" deutlich schneller. Anschließend bin ich hergegangen und habe die Menge der Zeilen im Memo konfigurierbar gemacht, so dass ich über einen Button steuernkonnte, nach wievielen Zeilen im Memo ein Insert angestoßen werden sollte. Bei der Datenbank und dem Server waren es etwa 60 Inserts, die zur besten Geschwindigkeit führten.
Jeder Aufruf von TQuery.ExecSQL wurde in einer eigenen Transaktionsklammer ausgeführt. Da auch hier ab und an Probleme auftraten mit Timeout... habe ich um die Transaktionsklammer noch eine Schleife gelegt, die 10 Versuche startete, bevor sie mit 'ner Exception den Job beendete.

Das ist jetzt nicht gerade eine besonders "professionelle" Lösung, aber eventuell könnte Dir so ein "Hilfsmittel" ja auch behilflich sein.

[alzaimar]

Für MSSQL gibt es das sehr schnelle BCP.EXE (=Bulk Copy Program), mit dem Textdateien sehr (und ich meine: sehr) schnell in MSSQL importiert werden können (Tausende von Records pro Sekunde).

Gibt es keine guten Datenpumpen für FB?

Als Alternative könnte man mal probieren, wie FB auf soetwas reagiert:

SQL-Code:

insert into MyTable (Field1, Field2, Field3)
select 1,2,3
union
select 4,5,6
union
select 7,8,9
...
...
...

Das dann noch in Blöcken à 10-100 Befehlen gebündelt in einer Transaktion und dann könnte das schon schneller werden.