Mehrere Datensätze mit SQL-Befehl Update schneller aktualisieren

[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