Mehrere Datensätze mit SQL-Befehl Update schneller aktualisieren

[TigerLilly]

ad Stored Procedure: Da geht fast alles. Du kannst zB mehrere SQL Statements als Block übergeben und die SP arbeitet die der Reihe nach ab. Oder du übergibst nur IDs, die abgearbeitet werden sollen. Wie auch immer. Und zurück kannst du dir eine Struktur geben lassen, aus der du dir deine Infos wieder rausklaubst. Oder du benutzt eine Tabelle zum Datenaustausch. Da kenne ich jetzt sowohl MaraiDB als auch deine Anwendung zu wenig.

Aber wenn ich einen Tipp geben darf: Schau, dass du rausbekommst, was genau das Problem ist. Ändere keinen Code aufgrund ungeprüfter Vermutungen.

[MyRealName]

Sollte man nicht erstmal eine transaktionale Tabelle machen, sowas wie :

Datum/Uhrzeit | Menge (+/-) | ProduktId | ....

Da trägt man ein, wenn was rein oder raus geht. und von dieser Tabelle erstellt man dann eine Zusammenfassung, errechnet durchschnittliche Einkaufspreise etc.
Und die Tabelle sollte auch immer wieder erzeugbar sein von den verschiedenen Quellen wie Wareneingang, Versand, Produktion etc. alles jeder, der was reintut oder rausnimmt.

[ggscholz]

Zitat:

MyRealName
Denk mal über einen kleinen Rest-Server nach, mit RTC (RealThinClient, open source) habe ich übers Internet vor Jahren schon tausende von SQLs pro Sekunde abgesetzt.

Danke für den Hinweis - das ist für mich völliges Neuland und damit eine neu Baustelle - das werde ich aktuell nicht in Betracht ziehen.

Zitat:

MyRealName
Da Du ja UniDAC benutzt, schau Dir doch mal UniScript an, die machen Fehlerbehandlung und lassen Dich viele Befehle auf einmal absetzen.

Werde ich mir ansehen, habe aber noch keinen Plan, was da für eine Idee hinter steckt.

Zitat von TigerLilly:

ad Stored Procedure: Da geht fast alles. Du kannst zB mehrere SQL Statements als Block übergeben und die SP arbeitet die der Reihe nach ab. Oder du übergibst nur IDs, die abgearbeitet werden sollen. Wie auch immer. Und zurück kannst du dir eine Struktur geben lassen, aus der du dir deine Infos wieder rausklaubst.

Mit Stored Procedure habe ich mich jetzt mal als erstes beschäftigt.

Ich habe mir eine SP zusammengebastelt, die das macht was ich will: Ein Update bei einem Datensatz, der meinen Suchkriterien entspricht. Und ich bekomme die passenden Infos z.B. den Lagerplatz zurück. Und wenn kein freier Platz gefunden wurde, fehlt der Lagerplatz in der Rückgabe.

Die SP ist in der Datenbank gespeichert.

Allerdings ist das Problem, das das Update für mehrere Datensätze (mein eigentliches Problem) auch wieder zu lange dauert. Da bin ich mit einem Update per Batch und einer erneuten Abfrage der bearbeiteten Datensätze schneller.

Ich habe für eine SP kein Bespiel gefunden, wie hier dann mehrere Datensätze mit einem ExecSQL an die Datenbank geschickt werden kann

Hier die Stored Procedure, die ich für jeden Datensatz einzeln aufrufe

Code:

CREATE DEFINER=`root`@`%` PROCEDURE `StoreInToPlaceUpdate`(
  IN pStoreId int, 
  IN pBuilding int, 
  IN pRoom int, 
  IN pSize int, 
  IN pItemNbr VARCHAR(50), 
  IN pCategoryNbr int, 
  IN pAssemblyStatus tinyint, 
  IN pStation int, 
  IN pComment VARCHAR(255), 
  IN pOrderNbr int, 
  IN pSequenceNbr int, 
  OUT oCount int, 
  OUT oNumber int, 
  OUT oPlace VARCHAR(255))
BEGIN
  SET @lagerid = '0';
  SET @placeid = '0';
  
SELECT
    nr,
    COALESCE(CONCAT(floor, corridor, '-', field, compartment, '-', place), 0) AS aPlace
    FROM lager
    WHERE StoreId = pStoreId
    AND Building= pBuilding
    AND Room = pRoom
    AND WgSize = pSize
    AND ((ItemNbr = '0')
    AND (Lock = 0))
    AND Deleted = 0
    ORDER BY wgsize ASC, floor ASC, corridor ASC, place ASC
    LIMIT 1 INTO @lagerid, @placeid;

  UPDATE lager
  SET ItemNbr= pItemNbr,
      WgNbr = pCategoryNbr,
      Montage = pAssemblyStatus,
      Station = pStation,
      LgTimeStamp = NOW(),
      Comment = pComment,
      OrderNbr = pOrderNbr,
      SequenceNbr = pSequenceNbr
  WHERE nr = (@lagerid);
  SELECT
    @lagerid,
    ROW_COUNT() AS rCount,
    @placeid INTO oNumber, oCount, oPlace;
END

Der Aufruf in Delphi

Delphi-Quellcode:

function StoreInToPlaceUpdate(pStoreId, pBuildingId, pRoomId: Integer; pSize,
      pItemNbr: string; pCategoryNbr, pAssemblyStatus, pStation: Integer; pComment:
      string; pOrderNbr, pSequenceNbr: integer; out oPlace: string): Integer;
var
   sp: TUniStoredProc;
begin
  Result:= 0;
  try
    sp:= TuniStoredProc.Create(nil);
    sp.Connection := fMainForm.coMariaDb;
    sp.StoredProcName := 'StoreInToPlaceUpdate';
    sp.PrepareSQL;

    sp.Params[0].AsInteger:= pStoreId;
    sp.Params[1].AsInteger:= pBuildingId;
    sp.Params[2].AsInteger:= pRoomId;
    sp.Params[3].AsString:= pSize;
    sp.Params[4].AsString:= pItemNbr;
    sp.Params[5].AsInteger:= pCategoryNbr;
    sp.Params[6].AsInteger:= pAssemblyStatus;
    sp.Params[7].AsInteger:= pStation;
    sp.Params[8].AsString:= pComment;
    sp.Params[9].AsInteger:=  pOrderNbr;
    sp.Params[10].AsInteger:= pSequenceNbr;
    
    sp.ExecSQL;
    
    oPlace:= sp.Params[13].AsString;
    Result :=sp.Params[12].AsInteger  
   finally
 
   end;
  FreeAndNil(sp);
end;

Denn Output habe ich in ein txt Datei geschrieben(Auszug):

Code:

1A-00-276 - 21734 - OrderNr:1180_65
1A-00-277 - 220398 - OrderNr:1180_66
1A-00-278 - 5279 - OrderNr:1180_67
0 - 21137 - OrderNr:1180_68
1A-00-279 - 21372 - OrderNr:1180_69
1A-00-280 - 21734 - OrderNr:1180_70
1A-00-281 - 21925 - OrderNr:1180_71

Einen Index habe ich auf die Felder WgSize,floor,corridor, place gesetzt, wirklich gebracht hat das aber für diese SP nicht wirklich was.

Zum Testen habe ich 1000 Updates abgeschickt, das braucht dann ca. 33000 ms, ein einfaches Update mit gleichem Ergebnis per Batch ist nach ca 200 ms fertig (alles unter gleichen Bedingungen getestet).

Wie kann ich ein Batch Update per Stored Procedure umsetzen? Geht das überhaupt?

Beste Grüße

Gerd

[TigerLilly]

ad Stored Procedure: Die SP hat wie du selber feststellst, nur Sinn, wenn mehrere Datensätze abgearbeitet werden. Wenn du jeden Satz einzeln abarbeitest, ist das nur Overhead. Ich würde die IDs ALLER Datensätze, die du bearbeiten möchtest, der SP auf dem Server übergeben.

Siehe hier:
https://stackoverflow.com/questions/...ored-procedure

Oder du stellst für das UPATE WHERE IN die IN Clausel in Delphi zusammen und übergibst die an die SP.

Aber nochmal hingewiesen: Schau, dass du rausbekommst, was genau das Problem ist (= was dauert denn so lange?). Ändere keinen Code aufgrund ungeprüfter Vermutungen.

Wenn das Aktualisieren selbst so lange dauert, hilft dir die SP auch nicht.

[hoika]

Hallo,
wozu dieser Index?
Wenn er nichts nützt, schadet er eher.

Bitte das hier mal sorgfältig durchlesen:
https://dzone.com/articles/improve-m...uery-profiling

Indizes sollen in erster Linie die Reduktion von Daten beschleunigen,
also alles was im "where" steht.
Dazu gehören Felder des Order By eher nicht dazu.

Bitte mal die betreffende Tabelle (Lager) incl. der aktuellen Indizes posten.

Mein "prepare" weiter vorn bezog sich auf Queries.
Bei SPs sollte das schon erledigt sein.
Und Prepare wird vor der Nutzung der Query gemacht,
wenn man sie mehrfach benutzen will.

[ggscholz]

Ich lese das gerade, da stimmt was nicht 🧨

Zitat von bernardtho:

Ich habe einen Index erstellt, der WgSize-, Boden-, Korridor- und Ortsdaten enthielt, aber bei Verwendung dieses SP keine Leistungsverbesserung festgestellt.

Ein einfaches Update mit dem gleichen Ergebnis per Batch ist in etwa 200 ms fertig, aber ich habe 33000 ms gebraucht, als ich tausend Änderungen zum Testen gesendet habe (alles unter den gleichen Bedingungen getestet).

Können Sie die Schritte erläutern, die ich ausführen muss, um eine gespeicherte Prozedur zum Ausführen einer Stapelaktualisierung zu verwenden? Wäre das machbar?

Wer schreibt da meinen Text ab? Boden- und Korridor-??? Ohne meine Datenbank zu haben, macht dieser Beitrag nicht viel Sinn.

[Uwe Raabe]

Nach der Signatur ist das Spam für ein Spiel...

[hoika]

Hallo,
Einen Index habe ich auf die Felder WgSize,floor,corridor, place gesetzt, wirklich gebracht hat das aber für diese SP nicht wirklich was.

Ein einziger Index für alle 4 Felder?

Gibt es einen Index für StoreId.
Hast Du mal einen Query-Analyzer über das Select laufen lassen?
Werden Indizes verwendet?

[dataspider]

Zitat von ggscholz:

Delphi-Quellcode:

function StoreInToPlaceUpdate(pStoreId, pBuildingId, pRoomId: Integer; pSize,
      pItemNbr: string; pCategoryNbr, pAssemblyStatus, pStation: Integer; pComment:
      string; pOrderNbr, pSequenceNbr: integer; out oPlace: string): Integer;
var
   sp: TUniStoredProc;
begin
  Result:= 0;
  try
    sp:= TuniStoredProc.Create(nil);
    sp.Connection := fMainForm.coMariaDb;
    sp.StoredProcName := 'StoreInToPlaceUpdate';
    sp.PrepareSQL;
    sp.Params[0].AsInteger:= pStoreId;
    ...
    sp.ExecSQL;
    oPlace:= sp.Params[13].AsString;
    Result :=sp.Params[12].AsInteger  
  finally
    FreeAndNil(sp);
  end;
end;

Du rufst StoreInToPlaceUpdate bei 1000 Datensätzen 1000 Mal auf?
Du erzeugst also jedes Mal erst TuniStoredProc, machst jedes Mal ein Prepare etc.

Wenn, dann Erzeugen, Prepare und Freigabe in einer äusseren Procedure, welche die StoreInToPlaceUpdate dann aufruft mit der UniStoredProc als ersten Parameter.
Und TuniStoredProc.Options.AutoPrepare auf False.

Frank