[Gelöst] MariaDB - Mehrere Datensätze auf einmal einfügen

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.
Antworten
pluto
Lazarusforum e. V.
Beiträge: 7178
Registriert: So 19. Nov 2006, 12:06
OS, Lazarus, FPC: Linux Mint 19.3
CPU-Target: AMD
Wohnort: Oldenburg(Oldenburg)

[Gelöst] MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von pluto »

Hallo

Für mein Projekt habe ich eine Kopier Funktion erstellt. Klappt inzwischen auch soweit. Jedoch würde ich gerne, wie bisher im Projekt auf
"prepared statements" setzten.

Ich nutzte eine MariaDB(mysql57conn) als Datenbank.

Ich habe natürlich auch gesucht im Internet, jedoch scheinen das viele so zu machen.

Code: Alles auswählen

 
procedure TPLNoteManager2.AddNote2(Items: TObjectList; const aTargetDirectory: Integer);
var
  str:String;
  TempID,i:Integer;
  DirectoryItem:TPLNoteManager2DirectoryItem;
  NoteItem:TPLNoteManager2NoteItem;
begin
  str:='INSERT INTO NoteTable ('+
         'DirectoryID, ' +
         'Title, ' +
         'Text, ' +
         'CreateDateTime, ' +
         'LastReadDateTime, ' +
         'LastwriteDateTime, ' +
         'ReadCount, ' +
         'WriteCount, ' +
         'UserID, ' +
         'EncryptByUserPassword'+
        ') ' +
        'VALUES ';
 
  MariaDB.Query2.Prepare;
 
  for i:=0 to Items.Count-1 do begin
    NoteItem:=Items[i] as TPLNoteManager2NoteItem;
    str:=str+'('+
          IntToStr(NoteItem.DirectoryID)+','+
 
          QuotedStr(NoteItem.Title)+','+
          QuotedStr(NoteItem.Text)+','+
 
          QuotedStr(DateTimeToStr(NOW,DefaultSQLFormatSettings))+','+
          QuotedStr('0')+','+
          QuotedStr('0')+','+
 
//          QuotedStr(DateTimeToStr(NoteItem.LastReadDateTime,DefaultSQLFormatSettings))+','+
//          QuotedStr(DateTimeToStr(NoteItem.LastWriteDateTime,DefaultSQLFormatSettings))+','+
 
          IntToStr(NoteItem.ReadCount)+','+
          IntToStr(NoteItem.WriteCount)+','+
 
          IntToStr(NoteItem.UserID)+','+
          BoolToStr(NoteItem.EncryptByUserPassword)+''+
        ')';
    if i < Items.Count-1 then
      str:=str+',';
  end;
 
  writeln();
  writeln(str);
  writeln();
  MariaDB.Query.SQL.Text:=str;
  MariaDB.Query.ExecSQL;
  MariaDB.Query.Close;
  MariaDB.ATransaction.Commit;
 
  DirectoryItem:=FindDirectoryByDirectoryID(aTargetDirectory,DirectoryItemlist);
  if Assigned(DirectoryItem) then begin
    if not DirectoryItem.Used then begin
      DirectoryItem.Used:=UpdatetUsedDirectory(DirectoryItem.DirectoryID);
    end;
  end;
end; // TPLNoteManager2.AddNote2   
 


Die Frage ist, wie geht das am besten? Für einzelne Datensätze mache ich das so:

Code: Alles auswählen

 
var
  str:String;
  TempID:Integer;
  DirectoryItem:TPLNoteManager2DirectoryItem;
begin
  str:='INSERT INTO NoteTable (' +
         'NoteID, ' +
         'DirectoryID, ' +
         'Title, ' +
         'Text, ' +
         'CreateDateTime, ' +
         'LastReadDateTime, ' +
         'LastwriteDateTime, ' +
         'ReadCount, ' +
         'WriteCount, ' +
         'UserID, ' +
         'EncryptByUserPassword' +
       ') VALUES (' +
       ':NoteID, ' +
       ':DirectoryID, ' +
       ':Title, ' +
       ':Text, ' +
       ':CreateDateTime, ' +
       ':LastReadDateTime, ' +
       ':LastwriteDateTime, ' +
       ':ReadCount, ' +
       ':WriteCount, ' +
       ':UserID, ' +
       ':EncryptByUserPassword' +
       ');';
 
  MariaDB.Query.SQL.Text:=str;
 
  MariaDB.Query.ParamByName('NoteID').AsInteger:=aNote.NoteID;
  MariaDB.Query.ParamByName('DirectoryID').AsInteger:=aNote.DirectoryID;
  MariaDB.Query.ParamByName('Title').AsString:=aNote.Title;
  MariaDB.Query.ParamByName('Text').AsString:=aNote.Text;
  MariaDB.Query.ParamByName('CreateDateTime').AsDateTime:=aNote.CreateDateTime;
  MariaDB.Query.ParamByName('LastReadDateTime').AsDateTime:=aNote.LastReadDateTime;
  MariaDB.Query.ParamByName('LastwriteDateTime').AsDateTime:=aNote.LastWriteDateTime;
  MariaDB.Query.ParamByName('ReadCount').AsInteger:=aNote.ReadCount;
  MariaDB.Query.ParamByName('WriteCount').AsInteger:=aNote.WriteCount;
  MariaDB.Query.ParamByName('UserID').AsInteger:=aNote.UserID;
  MariaDB.Query.ParamByName('EncryptByUserPassword').AsBoolean:=aNote.EncryptByUserPassword;
  MariaDB.Query.ExecSQL;
 
  // Die NoteID gleich ändern. Das geht aber erst nach dem Hinzufügen. Da voher die ID nicht bekannt ist.
  aNote.NoteID:=MariaDB.AConnection.GetInsertID;
  MariaDB.ATransaction.Commit;
 
  DirectoryItem:=FindDirectoryByDirectoryID(aNote.DirectoryID,DirectoryItemlist);
  if Assigned(DirectoryItem) then begin
    if not DirectoryItem.Used then begin
      DirectoryItem.Used:=UpdatetUsedDirectory(DirectoryItem.DirectoryID);
    end;
  end;           
 

Das klappt ganz gut.
Zuletzt geändert von pluto am Fr 8. Mär 2019, 18:19, insgesamt 1-mal geändert.
MFG
Michael Springwald

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von af0815 »

Prepared Statements machen nur zusammen mit Parametern Sinn. Damit kann der Server das Statement vorkompilieren, optimieren und auch im Ausführungpuffer halten. Damit kann der Server sich die Analyse und Kompilierung des Statemnets sparen und braucht nur die Parameter austauschen. Man darf aber dann nicht das SQL-Statement ändern bzw. immer neu zuweisen.

Daher
a) Statement mit Parametern erzeugen
b) Parameter setzen
c) ExecSQL
dann wieder bei b) weitermachen.

Open bedingt sinnvoll, da oft automatische Statements für Datenänderung eingeschoben werden -> eher nur bei RO-Datensets
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Socke
Lazarusforum e. V.
Beiträge: 3158
Registriert: Di 22. Jul 2008, 19:27
OS, Lazarus, FPC: Lazarus: SVN; FPC: svn; Win 10/Linux/Raspbian/openSUSE
CPU-Target: 32bit x86 armhf
Wohnort: Köln
Kontaktdaten:

Re: MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von Socke »

af0815 hat geschrieben:Prepared Statements machen nur zusammen mit Parametern Sinn. Damit kann der Server das Statement vorkompilieren, optimieren und auch im Ausführungpuffer halten. Damit kann der Server sich die Analyse und Kompilierung des Statemnets sparen und braucht nur die Parameter austauschen. Man darf aber dann nicht das SQL-Statement ändern bzw. immer neu zuweisen.


Werden die Prepared Statements von SQLDB oder ZEOS überhaupt unterstützt? Als ich das letzte mal mit SQLDB gearbeitet hatte, wurden die Parameter noch in der Pascal-Anwendung ersetzt ...
MfG Socke
Ein Gedicht braucht keinen Reim//Ich pack’ hier trotzdem einen rein

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von af0815 »

Müsste man in den verschiedenen Treibern nachsehen. Generell ist das für mich guter Stil, und nicht alles per Stringkommandos zusammenquetschen. Parameter tragen auch zur Sicherheit gegen SQLinjektion bei, da sie nicht kompiliert werden. Deswegen auch guter Stil.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

pluto
Lazarusforum e. V.
Beiträge: 7178
Registriert: So 19. Nov 2006, 12:06
OS, Lazarus, FPC: Linux Mint 19.3
CPU-Target: AMD
Wohnort: Oldenburg(Oldenburg)

Re: MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von pluto »

Werden die Prepared Statements von SQLDB oder ZEOS überhaupt unterstützt? Als ich das letzte mal mit SQLDB gearbeitet hatte, wurden die Parameter noch in der Pascal-Anwendung ersetzt ...

Im Allgemeinen schon, soweit ich weiß...

Müsste man in den verschiedenen Treibern nachsehen. Generell ist das für mich guter Stil, und nicht alles per Stringkommandos zusammenquetschen. Parameter tragen auch zur Sicherheit gegen SQLinjektion bei, da sie nicht kompiliert werden. Deswegen auch guter Stil.

Das ist auch meine Ansicht, darum möchte ich auch weiterhin auf "Parameter" setzten, als alles in einem String zu tun...

Daher
a) Statement mit Parametern erzeugen
b) Parameter setzen
c) ExecSQL
dann wieder bei b) weitermachen.

Diese Lösung hatte ich mir auch schon gestern überlegt, werde ich mal ausprobieren.

Bei ExecSQL, wird keine DB Anfrage gestartet? wie wird das zu einer Anfrage zusammen gefasst?
Außerdem habe ich im Internet gelesen, es gibt eine Magische Grenze: von ca 500 Einträgen.

Ich habe mir auch schon überlegt, dass ganze in einer Server-procedure auszulagern, wäre bestimmt Sinnvoller?
Es geht hierbei um eine Kopier Funktion für Datensätze innerhalb einer Tabelle.
MFG
Michael Springwald

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von af0815 »

Wenn, dann sind diese Grenzen den Puffern und Transaktionen geschuldet. Am Server kann man viel mit einem Insert into und Subselects machen. Besonders für Operationen in derselben Tabelle oder zum Konsolidieren von Daten. Da geht verdammt viel. Was man mit self joins und Subselects machen kann ist gewaltig.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

pluto
Lazarusforum e. V.
Beiträge: 7178
Registriert: So 19. Nov 2006, 12:06
OS, Lazarus, FPC: Linux Mint 19.3
CPU-Target: AMD
Wohnort: Oldenburg(Oldenburg)

Re: MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von pluto »

Da geht verdammt viel. Was man mit self joins und Subselects machen kann ist gewaltig.

Das habe ich mir noch nicht angeschaut... Aber ich habe einiges im Internet gesehen... ist schon nicht schlecht.... was alles Möglich ist...

Z.B. habe ich im DelphiPraxies Forum gelesen in einigen Thread das man mit der Parameter Methode auch sehr sehr viel Zeit gegenüber einem Normalen Insert Into einsparen kann.
Da wurden aus Stunden auf einmal Sekunden....

Mir geht es erst mal um das Thema "Sicherheit".... Bei der Insert Into Methode ohne Parameter gab es vom DB Server eine Fehler Meldung: Es sei nicht Performant oder so ähnlich.
Darum habe ich die Aufrufe zusammen gefasst zu einer Anfrage...
MFG
Michael Springwald

pluto
Lazarusforum e. V.
Beiträge: 7178
Registriert: So 19. Nov 2006, 12:06
OS, Lazarus, FPC: Linux Mint 19.3
CPU-Target: AMD
Wohnort: Oldenburg(Oldenburg)

Re: MariaDB - Mehrere Datensätze auf einmal einfügen

Beitrag von pluto »

Die Methode klappt recht gut:

Code: Alles auswählen

 
procedure TPLNoteManager2.AddNote3(Items: TObjectList; const aTargetDirectory: Integer);
var
  str:String;
  i:Integer;
 
  DirectoryItem:TPLNoteManager2DirectoryItem;
  NoteItem:TPLNoteManager2NoteItem;
 
  TempDirectoryID, TempTitle, TempText:TParam;
  TempCreateDateTime, TempLastReadDateTime, TempLastwriteDateTime:TParam;
  TempReadCount, TempWriteCount, TempUserID:TParam;
  TempEncryptByUserPassword:TParam;
begin
  str:='INSERT INTO NoteTable (' +
         'DirectoryID, ' +
         'Title, ' +
         'Text, ' +
         'CreateDateTime, ' +
         'LastReadDateTime, ' +
         'LastwriteDateTime, ' +
         'ReadCount, ' +
         'WriteCount, ' +
         'UserID, ' +
         'EncryptByUserPassword' +
       ') VALUES (' +
       ':DirectoryID, ' +
       ':Title, ' +
       ':Text, ' +
       ':CreateDateTime, ' +
       ':LastReadDateTime, ' +
       ':LastwriteDateTime, ' +
       ':ReadCount, ' +
       ':WriteCount, ' +
       ':UserID, ' +
       ':EncryptByUserPassword' +
       ');';
 
  MariaDB.Query.SQL.Text:=str;
   // Die Idee habe ich aus dem http://www.delphipraxis.net/
  TempDirectoryID:=MariaDB.Query.ParamByName('DirectoryID');
  TempTitle:=MariaDB.Query.ParamByName('Title');
  TempText:=MariaDB.Query.ParamByName('Text');
 
  TempCreateDateTime:=MariaDB.Query.ParamByName('CreateDateTime');
  TempLastReadDateTime:=MariaDB.Query.ParamByName('LastReadDateTime');
  TempLastwriteDateTime:=MariaDB.Query.ParamByName('LastwriteDateTime');
 
  TempReadCount:=MariaDB.Query.ParamByName('ReadCount');
  TempWriteCount:=MariaDB.Query.ParamByName('WriteCount');
  TempUserID:=MariaDB.Query.ParamByName('UserID');
  TempEncryptByUserPassword:=MariaDB.Query.ParamByName('EncryptByUserPassword')
 
  for i:=0 to Items.Count-1 do begin
    NoteItem:=Items[i] as TPLNoteManager2NoteItem;
 
    TempDirectoryID.AsInteger:=NoteItem.DirectoryID;
    TempTitle.AsString:=NoteItem.Title;
    TempText.AsString:=NoteItem.Text;
    TempCreateDateTime.AsDateTime:=NoteItem.CreateDateTime;
    TempLastReadDateTime.AsDateTime:=NoteItem.LastReadDateTime;
    TempLastwriteDateTime.AsDateTime:=NoteItem.LastWriteDateTime;
    TempReadCount.AsInteger:=NoteItem.ReadCount;
    TempReadCount.AsInteger:=NoteItem.WriteCount;
    TempUserID.AsInteger:=NoteItem.UserID;
    TempEncryptByUserPassword.AsBoolean:=NoteItem.EncryptByUserPassword;
    MariaDB.Query.ExecSQL;
  end;
 
  DirectoryItem:=FindDirectoryByDirectoryID(aTargetDirectory,DirectoryItemlist);
  if Assigned(DirectoryItem) then begin
    if not DirectoryItem.Used then begin
      DirectoryItem.Used:=UpdatetUsedDirectory(DirectoryItem.DirectoryID);
    end;
  end;
end; // TPLNoteManager2.AddNote3
 


Damit hat sich das Problem gelöst. Danke für die Antworten.
MFG
Michael Springwald

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: [Gelöst] MariaDB - Mehrere Datensätze auf einmal einfüge

Beitrag von af0815 »

Eine Optimierung fällt mir bei dem Thema noch ein. Wie machst du das Transaktionsmanagment ?
wenn du das Manuell machst, dh. Starten vor der Schleife, beenden nach der Schleife, mit rollback im Fehlerfall, kann man noch optimieren.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

pluto
Lazarusforum e. V.
Beiträge: 7178
Registriert: So 19. Nov 2006, 12:06
OS, Lazarus, FPC: Linux Mint 19.3
CPU-Target: AMD
Wohnort: Oldenburg(Oldenburg)

Re: [Gelöst] MariaDB - Mehrere Datensätze auf einmal einfüge

Beitrag von pluto »

Eine Optimierung fällt mir bei dem Thema noch ein. Wie machst du das Transaktionsmanagment ?

noch gar nicht...

wenn du das Manuell machst, dh. Starten vor der Schleife, beenden nach der Schleife, mit rollback im Fehlerfall, kann man noch optimieren.

hast du eine Idee?
Du meinst, wenn das Einfügen Fehlschlägt und die Action rückgängig gemacht werden soll?

Edit: Zum Thema:
http://wiki.freepascal.org/SqlDBHowto/de
MFG
Michael Springwald

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: [Gelöst] MariaDB - Mehrere Datensätze auf einmal einfüge

Beitrag von af0815 »

Obs du es jetzt für das zurückrollen im Fehlerfall verwendest oder nicht ist dir überlassen. Allgemein ist es so, das inserts in einer Transaktion schneller sind, wenn sie zusammengefasst werden und nicht jede einen einzelne Transaktion öffnet/schliesst. Wenn du jetzt das Riski nimmst und zum Beispiel 50 Inserts zu einer Transaktion zusammenfassts, dann wird die Sache normalerweise schneller. Wenn dabei ein Fehler passiert hast du 50 Inserts nicht durchgeführt. Ist ein Abwägen an Serverbelastung, Geschwindigkeit und was wird benötigt.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

pluto
Lazarusforum e. V.
Beiträge: 7178
Registriert: So 19. Nov 2006, 12:06
OS, Lazarus, FPC: Linux Mint 19.3
CPU-Target: AMD
Wohnort: Oldenburg(Oldenburg)

Re: [Gelöst] MariaDB - Mehrere Datensätze auf einmal einfüge

Beitrag von pluto »

Obs du es jetzt für das zurückrollen im Fehlerfall verwendest oder nicht ist dir überlassen. Allgemein ist es so, das inserts in einer Transaktion schneller sind, wenn sie zusammengefasst werden und nicht jede einen einzelne Transaktion öffnet/schliesst

Ach so, ich dachte das das bereits bei der Parameter Methode Passiert. Ich werde das später noch mal prüfen, wenn ich größere Datenmengen habe... ich glaube für die Grundversion dürfte das nicht so entscheiden sein.

t. Wenn du jetzt das Riski nimmst und zum Beispiel 50 Inserts zu einer Transaktion zusammenfassts, dann wird die Sache normalerweise schneller. Wenn dabei ein Fehler passiert hast du 50 Inserts nicht durchgeführt. Ist ein Abwägen an Serverbelastung, Geschwindigkeit und was wird benötigt.

OK, werde ich bei einer Gelegenheit Testen, danke für die Info um sowas habe ich mir bisher noch keine Gedanken gemacht.
MFG
Michael Springwald

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6198
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: [Gelöst] MariaDB - Mehrere Datensätze auf einmal einfüge

Beitrag von af0815 »

versuch mal ein paar tausend Testdatensätze zu erstellen und dann die Versuche zu machen. Einer der Kardinalfehler beim Testen ist, wenn die Datenbanken fast leer sind. Da hat man noch keine Laufzeiten, wenn dir DB gefüllt ist, so schaut das schon ganz anders aus. Versuch zu überlegen wie viele Daten sich in 1 bis 2 Jahren ansammeln und versuch das zu simulieren, oft gibts dann 'Aha' Effekte.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

pluto
Lazarusforum e. V.
Beiträge: 7178
Registriert: So 19. Nov 2006, 12:06
OS, Lazarus, FPC: Linux Mint 19.3
CPU-Target: AMD
Wohnort: Oldenburg(Oldenburg)

Re: [Gelöst] MariaDB - Mehrere Datensätze auf einmal einfüge

Beitrag von pluto »

versuch mal ein paar tausend Testdatensätze zu erstellen und dann die Versuche zu machen. Einer der Kardinalfehler beim Testen ist, wenn die Datenbanken fast leer sind. Da hat man noch keine Laufzeiten, wenn dir DB gefüllt ist, so schaut das schon ganz anders aus.

Da hast du natürlich recht, aber um die Grund Funktionen zu entwickeln arbeite ich mit kleinen Datenmengen, jedoch habe ich mir auch einige Export und Import Methoden erstellt um die Datenbank Anwendung besser zu testen...

Ich denke, die DB wird auch kein Problem sein, sondern eher der TCP Server und TCP Client...

Versuch zu überlegen wie viele Daten sich in 1 bis 2 Jahren ansammeln und versuch das zu simulieren, oft gibts dann 'Aha' Effekte.

Gute Idee... wenn ich die Grund Funktionen soweit habe, kommt die Test Phase....
MFG
Michael Springwald

Antworten