Mehrere Datensätze mit SQL-Befehl Update schneller aktualisieren

[ggscholz]

Hallo in die Runde,

in meinem Lagerprogramm werden regelmäßig größere Mengen Kartonware auf festgelegte Plätze eingelagert. Größere Mengen sind 100 Stück, vielleicht werden es auch mal 200 aber eher sind es regelmäßig 20 - 50 Stück. Ich weiß, diese Mengen sind für eine DB eher Kleinstmengen und damit schnell verarbeitet.
Die Notwendigkeit, sich mit der Verarbeitungsgeschwindigkeit zu beschäftigen liegt an einem höheren aufkommen an Arbeitsplätzen, die regelmäßig die DB nutzen und, jetzt neu, ein Außenlager, das per VPN die PC's dort ins lokale Netzt einbindet und/oder ein Remotedesktop als Arbeitsplatz. Das gibt dann doch immer mal wieder Fehler. Eine Möglichkeit sehe ich in der Verarbeitungsgeschwindigkeit des Update-Prozesses per SQL. Auf die Ausgestaltung des Netzwerks habe ich keinen Einfluss und DSL oder eine Standleitung bringt auch keine zuverlässige Geschwindigkeit bei der Verbindung.

Bisher lese ich die einzulagernden Produkte per Scanner in eine Liste ein, es werden ein paar Daten aus einer anderen DB ergänzt und für die weitere Verarbeitung in einer MEM Table auf dem PC vorgehalten. Über ein SELECT werden alle freien Plätze im Lager in der entfernten MariaDb gesucht und über weitere Kriterien aus der vorher erstellten Liste wird dann passend zum Produkt diese Abfrage gefiltert und mit einem UPDATE... der erste Datensatz als Lagerplatz für das Produkt bestimmt. Gibt es keine passenden Plätze mehr (kein Datensatz entspricht den Kriterien) bekomme ich das mit und kann darauf reagieren.

Ich habe jetzt mit einigen Varianten testweise mit 1000 Datensätzen herumexperimentiert: Die Update-Befehle in einer StringList gesammelt und dann an Query1.SQL.Text übergeben oder wie hier von Devart beschrieben, mit einem Array für die Parameter gearbeitet. Das Beschleunigt das Update mindestens um den Faktor 10 , ich bekomme aber keine Rückmeldung, wenn ein einzelner Update Befehl nicht richtig ausgeführt wird, bzw, es fehlen mir auch zu einem erfolgreichen Updatevogang jegliche Informationen, was an welchem Datensatz geändert wurde.

Diese Rückmeldung, ob, und wenn ja, wo jetzt das Produkt eingelagert werden soll benötige ich aber.

Hat jemand eine Idee, Beispiel oder Lösung, wie ich das Umsetzen könnte??

Beste Güße Gerd

[Jasocul]

Zitat von ggscholz:

ich bekomme aber keine Rückmeldung, wenn ein einzelner Update Befehl nicht richtig ausgeführt wird

Nutze Transaction und sorge im Fehlerfall für ein Rollback. Danach nimmst du als Fallback die langsame Methode. Dann solltest du herausbekommen, welches Update Probleme macht. Vielleicht kannst du beim Update auch ein Kennzeichen oder Timestamp mitführen. Anhand dessen kannst du mit den vorgesehenen Update-Datensätzen vergleichen, welchem das Kennzeichen fehlt. Reicht eventuell auch schon ein Vergleich, welche Datensätze keinen der vorgesehenen Lagerplätze bekommen hat?

Zitat von ggscholz:

es fehlen mir auch zu einem erfolgreichen Updatevogang jegliche Informationen, was an welchem Datensatz geändert wurde.

Dann musst du hinterher die Datensätze, die durch dein Update geändert wurden wieder lesen und mit dem alten Stand vergleichen.

Für mich läuft es im Grunde auf einen Vorher-Nachher-Vergleich hinaus. Es sei denn Devart hat da noch eine Option, die mir gerade nicht bekannt ist.

[TigerLilly]

Welchen Fehler bekommst du denn? Ein C/S Programm via VPN und ev noch WLAN kann so richtig langsam werden. Bei RDP solltest du das Problem aber nicht haben, oder?

Was ist das Problem eigentlich? Dauert das Update für die per VPN angebundenen zu lange?
Behindern die Updates sich gegenseitig und machen das langsam oder weil Deadlock unmöglich?

Du könntest die Updates in eine Sored Procedure übergeben, dann kannst du dir auch die Infos zurückgeben lassen, die du brauchst und die Daten müssen nicht via langsamen(?) VPN hin und her geschickt werden.

[hoika]

Hallo,
und ich komme mal mit dem berühmten "Prepare" um die Ecke.

[ggscholz]

Dank schon mal an dieser Stelle für die vielen Hinweise!

Zitat:

TigerLilly
Was ist das Problem eigentlich? Dauert das Update für die per VPN angebundenen zu lange?
Behindern die Updates sich gegenseitig und machen das langsam oder weil Deadlock unmöglich?

Ich bekomme von den Anwendern (vor allen Dingen aus dem neuen, externen Lager) gelegentlich den Hinweis, das nicht alle gescannten Kartons einen Lagerplatz bekommen haben. In der aktuellen Version wird, wenn kein Lagerplatz vorhanden ist, eine passender Eintrag auf einem Buchungsprotokoll vermerkt.

Eine konkrete Fehlermeldung habe ich bisher nicht bekommen. Wie gesagt, es gibt diesen Fehler zur Zeit nicht sehr oft. Aber ich erwarte, das es mehr Probleme geben wird, wenn an mehreren Rechnern enBlock Datensätze mit einem Update bearbeitet werden. Dem möchte ich mit einer Optimierung der Datenverarbeitung vorbeugen

Zitat:

Jasocul
Nutze Transaction und sorge im Fehlerfall für ein Rollback

Das ist ein Baustein, den ich in der neuen Version verwenden werde, auf jeden Fall.

Zitat:

Vielleicht kannst du beim Update auch ein Kennzeichen oder Timestamp mitführen.

Das ist schon umgesetzt, es wird eine Auftragsnummer erstellt und zu jedem Datensatz eingetragen, zusätzlich eine Detailnummer in der Reihenfolge der Buchung. Timestamp wäre auch möglich, ich brauche aber die Reihenfolge der Buchungen, wie sie vom Mitarbeiter eingelesen werden.

Zitat:

Für mich läuft es im Grunde auf einen Vorher-Nachher-Vergleich hinaus

Ja, das habe ich befürchtet. Es ist kein wirkliches Problem in der Programmierung, ich hatte nur gehofft, das es doch noch irgendwelche versteckten Rückmeldung aus der DB geben könnte, was da im einzelnen passiert ist.

Zitat:

TigerLilly
Du könntest die Updates in eine Stored Procedure übergeben, dann kannst du dir auch die Infos zurückgeben lassen, die du brauchst und die Daten müssen nicht via langsamen(?) VPN hin und her geschickt werden.

Hab ich für die einzelne Buchung mal umgesetzt, hat gut funktioniert. Wie wird denn dann so ein Block von Update-Befehlen übergeben, bzw, wie kommt der Block vom out-Daten zurück? Da ist mir noch kein Beispiel untergekommen.

Zitat:

hoika
Hallo,
und ich komme mal mit dem berühmten "Prepare" um die Ecke.

Da habe ich immer wieder mal gelesen, das das Prepare schon implementiert ist. Deswegen habe ich mich damit noch nicht wirklich beschäftigt. Aber der Einzeiler im Code geht ja immer

Grüße Gerd

[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.

[MyRealName]

Zitat von ggscholz:

in meinem Lagerprogramm werden regelmäßig größere Mengen Kartonware auf festgelegte Plätze eingelagert. Größere Mengen sind 100 Stück, vielleicht werden es auch mal 200 aber eher sind es regelmäßig 20 - 50 Stück. Ich weiß, diese Mengen sind für eine DB eher Kleinstmengen und damit schnell verarbeitet.
Die Notwendigkeit, sich mit der Verarbeitungsgeschwindigkeit zu beschäftigen liegt an einem höheren aufkommen an Arbeitsplätzen, die regelmäßig die DB nutzen und, jetzt neu, ein Außenlager, das per VPN die PC's dort ins lokale Netzt einbindet und/oder ein Remotedesktop als Arbeitsplatz. Das gibt dann doch immer mal wieder Fehler. Eine Möglichkeit sehe ich in der Verarbeitungsgeschwindigkeit des Update-Prozesses per SQL. Auf die Ausgestaltung des Netzwerks habe ich keinen Einfluss und DSL oder eine Standleitung bringt auch keine zuverlässige Geschwindigkeit bei der Verbindung.

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.

Zitat von ggscholz:

Bisher lese ich die einzulagernden Produkte per Scanner in eine Liste ein, es werden ein paar Daten aus einer anderen DB ergänzt und für die weitere Verarbeitung in einer MEM Table auf dem PC vorgehalten. Über ein SELECT werden alle freien Plätze im Lager in der entfernten MariaDb gesucht und über weitere Kriterien aus der vorher erstellten Liste wird dann passend zum Produkt diese Abfrage gefiltert und mit einem UPDATE... der erste Datensatz als Lagerplatz für das Produkt bestimmt. Gibt es keine passenden Plätze mehr (kein Datensatz entspricht den Kriterien) bekomme ich das mit und kann darauf reagieren.

Wie gesagt, lieber eine Tabelle, die die einzelnen Bewegungen enthält und dann mit SUM kannst rauskriegen, wieviel Du jeweils von einem Produkt an einer Stelle hast.

Zitat von ggscholz:

Ich habe jetzt mit einigen Varianten testweise mit 1000 Datensätzen herumexperimentiert: Die Update-Befehle in einer StringList gesammelt und dann an Query1.SQL.Text übergeben oder wie hier von Devart beschrieben, mit einem Array für die Parameter gearbeitet. Das Beschleunigt das Update mindestens um den Faktor 10 , ich bekomme aber keine Rückmeldung, wenn ein einzelner Update Befehl nicht richtig ausgeführt wird, bzw, es fehlen mir auch zu einem erfolgreichen Updatevogang jegliche Informationen, was an welchem Datensatz geändert wurde.

Diese Rückmeldung, ob, und wenn ja, wo jetzt das Produkt eingelagert werden soll benötige ich aber.

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

[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.