SQL - Freitextfeld updaten anhand EAN

Shopware 5 Programmierung
1

Hallo, 

ich bin an einem Punkt wo die Api zu lange braucht und der Server abbricht. Deshalb weiche ich auf PHP-PDO aus.

Ein Lieferant schickt uns eine CSV mit EAN-Nummern und eine Spezialnummer. Nur die EAN ist im Shop vermerkt (s_articles_details). Die Spezialnummern sollten hinzugefügt werden.

Dazu habe ich 3 Queries hintereinander laufen:

  • Create Temporäre Tabelle

  • Lade CSV und setze die Werte in Temporäre Tabelle

  • Update Shoptabellen aus Temporäre Tabelle

    CREATE TEMPORARY TABLE tmp(
    ean varchar(255),
    spezialnummer varchar(255)
    );
    LOAD DATA INFILE ‘/root/server/path/example.csv’ INTO TABLE tmp
    FIELDS TERMINATED BY ‘;’
    LINES TERMINATED BY ‘\n’
    (@ean, @spezialnummer)
    SET
    ean = @ean,
    spezialnummer = @spezialnummer;
    UPDATE s_articles_attributes AS aat, s_articles_details AS sad
    JOIN tmp AS tmpx ON tmpx.ean = sad.ean
    SET aat.spezialnummer = tmpx.spezialnummer
    WHERE aat.articledetailsID = sad.id

Das habe ich einmal direkt im phpmyadmin getestet, aber er hängt irgendwo.

Hat jemand eine Idee warum oder wie man es noch besser machen könnte? Vorher habe ich die CSV in PHP zerteilt und 150.000 UPDATE-Queries gehabt, da hat er dann irgendwann abgebrochen.

Fehler werden mir keine angezeigt.

2

Naja zuerst mal schauen, ob er die Datei richtig einspielt und die Tabelle inkl. Inhalt richtig in der Datenbank angezeigt wird.

Daher die Tabelle vllt. erstmal nicht nur temporär erzeugen lassen, löschen kann man die Tabelle danach ja wieder jederzeit.

Wenn das funktioniert hat, dann liegt es eher am Update-Befehl, der würde (sofern einmal eine Artikelvariante in der Datei fehlt) aber auch gnadenlos Spezialwerte leeren wie es scheint, denn im WHERE fehlt ein “AND tmpx.spezialnummer is not null”.

Was spricht gegen den Aufbau:

UPDATE s_articles_attributes saa
LEFT JOIN s_articles_details sad ON saa.articledetailsID = sad.id
LEFT JOIN tmp ON tmp.ean = sad.ean
SET saa.spezialnummer = tmp.spezialnummer
WHERE tmp.spezialnummer is not null;

 

1 „Gefällt mir“
3

Hi @PStadtfeld‍ .

Ja es liegt tatsächlich am UPDATE. Der dauert einfach zu lange .

Für 117.000 Artikel sind es je nach SQL Konstellation mehrere Stunden. Ich probiere mal mit WHERE sad.id BETWEEN 1 AND 1000 herum. Und schau mal ob ich das so splitten kann.

EDIT:  Das hier ist etwas vielversprechender:

UPDATE s_articles_attributes AS saa
JOIN s_articles_details AS sad ON sad.id = saa.articledetailsID
JOIN tmp AS tmpx
SET saa.spezialnummer = tmpx.spezialnummer
WHERE tmpx.ean = sad.ean
AND sad.id BETWEEN 1 AND 1000

 

4

Hallo @brettvormkopp‍,

nicht geteste aber machen Sie das ean Spalte in Temp Table als Primary key und ein index zu ean Spalte beim s_articles_details hinzufügen dass es würde schnellen laufen (ich glaube )

probiere mal.

beim left Join ich glaube es werde langsmer sein.

VG,

image

Tel.: +49 755 - 183 990 00 | E-Mail: info@enbit.de | Web: http://enbit.de/

5

Hallo @ahmadsaad‍ Meinst du so?

Lösung 1:

CREATE TEMPORARY TABLE tmp(
 ean varchar(255) PRIMARY KEY,
 spezialnummer varchar(255)
 INDEX (ean)
 );

oder Lösung 2:

CREATE TEMPORARY TABLE tmp(
 ean varchar(255) PRIMARY KEY,
 spezialnummer varchar(255)
 );

mit

CREATE INDEX idx_ean ON s_articles_details(ean)

Hat das Auswirkungen im Sinne, dass man sich ggf an anderer Stelle die DB zerschiesst? Ich teste das auf Testdatenbank, ich will nur verhindern, dass man in 1-2 Monaten merkt, dass es so nicht geht. Kann man den INDEX wieder spurlos entfernen?

6

@brettvormkopp‍

Aus rein logischer Sicht dürfte der Index auf der tmp-Tabelle keine oder zumindest nur minimale Auswirkung haben.
Durch das setzen des Index wird der Speichervorgang etwas langwieriger, da die Datenbank den Wert zusätzlich zum Feld im Index für spätere Suchen und Abgleiche ablegt. Dafür ist Auslesen über die erstellten Index-Listen im Hintergrund dann zwar wirklich schneller, da es aber hier 1:1-Verknüpfungen sind, die zudem nur temporär angelegt und danach ohnehin wieder gelöscht werden, düfte sich daraus kein nennenswerter Vorteil ergeben.

Das sieht bei den festen Shopware-Tabellen allerdings anders aus, hier kann eine gute Indexierung in der Tat einiges an Geschwindigkeit herausholen.
Daher mal in Shopware ein Index auf die Spalte EAN setzen und prüfen ob auf saa.articledetailsID der Fremdschlüssel oder zumindest auch ein Index sitzt.

Eine Idee wäre noch die Tabelle grundsätzlich im Vorfeld einmalig anzulegen, dann im Durchlauf nur noch befüllen zu lassen und anstelle des Löschen der Tabelle einfach ein Truncate darauf auszuführen (also alle Daten raus aber die Tabelle besteht für den nächsten Durchlauf weiter). Aber auch hier fraglich ob sich der zeitliche Vorteil wirklich lohnt, wäre aber auf jeden Fall weniger anfällig, da man dadurch nur noch befüllen und leeren müsste.

Mir ist aber noch etwas bei deinem “vielversprechenden” Codeausschnitt aufgefallen, das dürfte erhebliche Probleme verursachen!

UPDATE s_articles_attributes AS saa
JOIN s_articles_details AS sad ON sad.id = saa.articledetailsID
JOIN tmp AS tmpx
SET saa.spezialnummer = tmpx.spezialnummer
WHERE tmpx.ean = sad.ean
AND sad.id BETWEEN 1 AND 1000

Hier fehlt beim Join von tmp das ON , somit wird jede Reihe aus den Artikeldetails mit jeder Reihe aus tmp zusammengepackt, wodurch das Ergebnis von 1:1 zu n:m wird, sich also gegenseitig aufmultipliziert!
Das kann bei großen Tabellen wie den Details durchaus zeitliches KO verursachen :slight_smile:

1 „Gefällt mir“
7

Hallo @PStadtfeld‍ …die lange Verarbeitungszeit hat mich schon gewundert aber er hat es tatsächlich durchgerattert.

Allerdings habe ich hier ein neues Problem, dass die s_artiles_attributes im Standard nicht für jeden Artikel vorhanden sind und ich kein gescheites INSERT IGNORE hinbekomme. (Eigentlich sind es mehrere Tabellen die ich hier updaten will und nicht nur die 2)

Wenn ich da auch keine Idee komme, dann muss ich eine SQL bauen welche zu jedem Artikel auch eine s_articles_attributes-Zeile anlegt. :-/

8

@brettvormkopp‍

Uhm… normalerweise sind die durchaus vorhanden… zumindest bei uns gehört zu jedem Artikel auch fest eine attributes-Zeile…
Aber okay, vielleicht legt er die auch nur an wenn etwas drin stehen soll… Zum Insert Ignore - du könntest vor dem Update folgendes versuchen:

INSERT IGNORE INTO s_articles_attributes (articleID, articledetailsID)
SELECT articleID, articledetailsID FROM s_articles_details;

Da articledetailsID unique sein müsste, dürfte er keine doppelten Zeilen anlegen.

Bitte zuvor in einer Testumgebung probieren und es kann natürlich der Fall sein, dass das Statement schief geht, wenn für einige Felder keine Standartwerte eingestellt sind.
In dem Fall müsstest du entweder die Datenbanktabelle anpassen und diese Standards hinterlegen oder die Felder zusätzlich ins Statement einbauen.

1 „Gefällt mir“
9

Ich wollte noch anmerken zur Performance bei ca 150.000 Zeilen-CSV

  • LEFT JOIN braucht es  30min
  • INNER JOIN braucht es nur 3min