Wie kann ich automatisiert über viele Tabellen suchen?

[Klaus1]

Hallo,

wir haben eine Webanwendung, die von einer externen Firma erstellt wurde und nun aber nicht mehr supportet wird, sodass ich nun prüfen muss, ob ich die Daten selber gepflegt bekomme.

Die Daten sind meinem Verständnis nach sehr umständlich aufgebaut. Verwendet wird MySQL. Die Tabelle 'catalog' hat nur 2 Felder: 'hash' und 'name'. Der im Feld 'hash' angegebene Name referenziert auf Tabellen, die alle diesen hash beinhalten. Bspw. t_2e6f3a_articlenumber.

Diese Tabelle beinhaltet die Felder 'id' und 'value'. In 'value' steht die Artikelnummer. Möchte ich bspw. für diese Artikelnummer den Preis ermitteln, dann muss das Feld 'id' mit 'id' aus der Tabelle t_hash_attr_price verknüpfen. Der Preis steht dort wiederrum im Feld 'value'.

Wenn ich jetzt im konkreten Beispiel den Preis einer bestimmten Artikelnummer aktualisieren möchte, wie stelle ich das am Besten an? Die Artikelnummer kann innerhalb von t_hash_artikelnummer und aller t_hash_tabellen beliebig oft vorkommen.

Wie sieht es aus, wenn ich die Preise aller verwendeten Artikelnummern aktualisieren möchte ohne den Preis für eine Artikelnummer 100 Mal abzurufen?

Gibt es eine Möglichkeit eine Liste aller verwendeten Artikelnummern über alle Tabellen zu bekommen und dann mit einem Kommando den Preis überall zu aktualisieren?

LG Klaus

[Der Martin]

Hallo Klaus,

wir haben eine Webanwendung, die von einer externen Firma erstellt wurde und nun aber nicht mehr supportet wird, sodass ich nun prüfen muss, ob ich die Daten selber gepflegt bekomme.

hmm, das ist blöd. Musst du denn die Tabellenstrukturen (und Zusammenhänge) jetzt selbst re-engineeren oder hast du von der Softwarefirma wenigstens eine Dokumentation darüber?

Die Daten sind meinem Verständnis nach sehr umständlich aufgebaut.

Das kann sein; es kann aber ebensogut sein, dass die Tabellen "sauber" normalisiert sind und deshalb für Außenstehende kompliziert oder umständlich aussehen. Ich werde aus deiner Beschreibung der Struktur aber auch nicht schlau. Kannst du mal eine grobe Skizze machen oder einen kleinen Auszug aus den relevanten Tabellen zeigen, der das Prinzip deutlich macht?

Die Artikelnummer kann innerhalb von t_hash_artikelnummer und aller t_hash_tabellen beliebig oft vorkommen.

Was ist der Sinn dahinter, dass eine Artikelnummer in einem Katalog mehrmals vorkommt? Die soll einen bestimmten Artikel doch eindeutig identifizieren. Oder ist es hier gar nicht mehr der Katalog, sondern Arbeitsdaten wie Bestellungen oder Rechnungen?

Wie sieht es aus, wenn ich die Preise aller verwendeten Artikelnummern aktualisieren möchte ohne den Preis für eine Artikelnummer 100 Mal abzurufen?

Gibt es eine Möglichkeit eine Liste aller verwendeten Artikelnummern über alle Tabellen zu bekommen und dann mit einem Kommando den Preis überall zu aktualisieren?

Bestimmt gibt es so eine Möglichkeit. Aber ich sagte ja schon, dass mir die Struktur noch nicht klar geworden ist. Daher kann ich dir an der Stelle noch nicht weiterhelfen, sondern höchstens mit einem unqualifizierten "Hä?" kontern.

Immer eine Handbreit Wasser unterm Kiel
 Martin

[Klaus1]

Hallo,

es handelt sich dabei um eine Art technische Dokumentation, es sind also keine Bewegungsdaten, sondern Informationen darüber, welches Produkt als welchen Einzelteilen besteht und wieviel ein Austausch eines Einzelteils maximal kosten wird (Listenpreis). Es gibt keinen Warenkorb dahinter, also keine Buchungen. Bis auf den Preis (und sowas wie Datum X ersetzt Bauteil B das Bauteil A) ändert sich eigentlich nie etwas.

Nehmen wir als Beispiel Schränke. Die Firma bietet 300 verschiedene Schränke an, alle Schränke haben Einzelteile, die bei allen identisch sind (z.B. Tür-Befestigungen), andere Teile sind nicht überall vertreten (z.B. Spiegel-Tür oder Innenbeleuchtung). Wieder andere sind Artikel sind nur bei einem einzigen Schrank zu finden (z.B. spezielle Designelemente).

Zum 01.Januar ändern sich seit langem die Preise und die sollen/müssen einfach per Massenänderung aktualisiert werden. Die Software lässt leider keine Massenänderung zu, d.h. es müsste aktuell jedes Produkt einzeln aus unserem ERP-System exportiert und in der Webanwendung importiert werden. Durch Produktbilder, Explosionszeichnungen, etc. dauert der Export/Import eines Produkts je nach Größe und Umfang ca. 3 Minuten. Das ist bei einer hohen 3-stelligen Anzahl ein großer Aufwand.

Der Begriff "Artikelnummer" ist in der Tabelle auch ungünstig, "Materialnummer" wäre besser gewesen.

Ich halte die Struktur für "umständlich", da jedes weitere Produkt bedeutet, dass in der Datenbank ca. 20 weitere Tabellen erstellt werden, Und dass (zumindest) ich die Suche daher nicht über übliche JOINs laufen lassen kann.

LG Klaus

[Rolf B]

Hallo Klaus1,

Webanwendung, die von einer externen Firma erstellt wurde und nun aber nicht mehr supportet wird

Es lebe das externe Know How. Es ist ja sooooo viel billiger, als eigene Kräfte zu hegen und zu pflegen. Bis der Externe den Sack zubindet und weiterzieht. Aber in dem Moment ist der Manager, der alles auf Extern gesetzt hat, vermutlich auch schon weitergezogen und stiftet anderswo Unheil. Und die armen kleinen Indianer müssen es ausbaden.

Ich halte die Struktur für "umständlich", da jedes weitere Produkt bedeutet, dass in der Datenbank ca. 20 weitere Tabellen erstellt werden

Ja, und die Externen sind ja auch dermaßen kompetent und klug - das schaffen die Internen nie. Und so erhält man dann DB-Layouts, die man vorzugsweise mit FORMAT E: repariert. Das, was Du da hast, ist nicht umständlich. Das ist sträflich dumme Fehlverwendung von Datenbanken. So gewaltig können die Tabellen gar nicht sein, dass man sie aus Performancegründen auf Produkte teilen muss.

Ein Update über mehrere Tabellen hinweg ist nicht möglich, ein Update geht immer nur auf eine Tabelle.

Das kannst Du scripten - wenn Du das nötige Know How hast, natürlich. Irgendwo wirst Du ja sicherlich eine "übersichtliche" Quelle für die neuen Preise haben, und dann musst Du pro Produkt die richtige Table finden und darin die Werte aktualisieren.

Frage ist nur, ob die Werte irgendwie historisiert sind. Wenn ein Angebot vom 01.08.2021 gespeichert ist und für 47,11€ rausgegangen ist, soll es ja, wenn man es am 01.02.2022 aufruft, immer noch 47,11€ anzeigen, und nicht auf einmal 52,10€. Aber ob das relevant ist, kannst nur Du wissen.

Rolf

[Klaus1]

Hallo Rolf,

Du sprichst mir aus der Seele. Wie oft ich schon externen Dienstleistern, den sogenannten Profis, Dinge in Kindergartensprache erklären musste....

"umständlich" war hier der Euphenismus für katastrophal. Aber ich bin ja allenfalls der Einäugige unter den Blinden.

Es gibt neben price auch Tabellen wie future_price, start_future und end_future, aber die wurden von uns bisher nicht genutzt. Und jedes tolle Attribut hat seine eigene Tabelle, spitzenmäßig gelöst oder? Eine Historisierung gibts auch nicht. Ein Warenkorb ist nicht angeschlossen, die Ersatzteilbestellung läuft über ein anderes Portal (was ja total praktisch und anwenderfreundlich ist </ironie-off>)

Momentan laufe ich durch alle Produkte ( select hash, name from catalog ), laufe dann durch alle Materialien (die einen aktuell auch einen Preis haben) ( select a.value as matnr, b.value as preis from $tab_artikel a left join $tab_preise b on a.item_id=b.item_id ), hole mir hierfür den aktuellen Preis aus unserem ERP-System und aktualisiere den Preis in der Tabelle $tab_preise.

Um beim Schrank-Beispiel zu bleiben, 300 Schränke, jeweils 2 Türen, 4 Halter je Tür, macht das leider somit 2400 Mal dieselbe Abfrage ans ERP-System. Performance-Probleme wird es nicht geben, aber da sträuben sich dennoch die Nackenhaare.

LG Klaus

[Rolf B]

Hallo Klaus1,

300 Schränke, jeweils 2 Türen, 4 Halter je Tür, macht das leider somit 2400 Mal dieselbe Abfrage

Nee, dann solltest Du schon in zwei Schritten vorgehen. Wieviele verschiedene Artikelnummern gibt es denn? 1000? 10000? Egal, wenn Du ein assoziatives Array (a.k.a. HashMap) aufbaust, das den Artikelnummern Preise zuordnet, sind das je nach Umfang der zu cachenden Daten nicht mehr als 100 Bytes pro Eintrag, d.h. bei 100'000 Artikelnummern gerade mal 10 MB. Das hiermit aufgebaute PHP Array

$artikelCache = [];
$entries = 100000;
for ($i=0; $i<$entries; $i++) {
    $artNr = random_int(10000,99999);
    $preis = random_int(10000,99999) / 100;
    $artikelCache[$artNr] = $preis;
}

belegt weniger, gerade mal 4,5 MB (mit memory_get_usage() vorher und nachher gemessen).

Wieviele echt unterschiedliche Teile hast Du also? Es könnte durchaus lohnen, in zwei Stufen zu arbeiten.

(1) Alle Teile-IDs einsammeln (2) Die Preise (und ggf. andere Daten) pro Teile-ID abrufen und speichern (3) Tabellen der Webanwendung die Preise updaten

Rolf

[Klaus1]

Hallo Rolf,

ich hab es jetzt erstmal Quick&Dirty gelöst. Pro Produkt gibt es ca. 1000 Materialien. Der Durchlauf eines Produkts mit der Ermittlung aller Preise dauert ca. 8 Sekunden. Für alle Produkte wird die Laufzeit damit ca. 43 Minuten betragen. Noch vertretbar für 1 Mal pro Jahr aufrufen. Mit dem Aufbau einer einfachen Hashmap (Materialnummer,Preis) werde ich sicherlich die Laufzeit verringern, da der Preis nicht jedesmal gegen das ERP-System abgefragt werden muss, aber das kann bis nächstes Jahr warten. Dann gibts vielleicht auch Features wie Zukunftspreis, Preis gültig ab/bis und vielleicht partielle Aktualisierung (einzelnes Produkt, einzelnes Material).

LG Klaus

[Der Martin]

Hi,

es handelt sich dabei um eine Art technische Dokumentation, es sind also keine Bewegungsdaten, sondern Informationen darüber, welches Produkt als welchen Einzelteilen besteht und wieviel ein Austausch eines Einzelteils maximal kosten wird (Listenpreis).

also eher eine Art Materialstückliste mit Einzelteilpreisen.

Nehmen wir als Beispiel Schränke. Die Firma bietet 300 verschiedene Schränke an, alle Schränke haben Einzelteile, die bei allen identisch sind (z.B. Tür-Befestigungen), andere Teile sind nicht überall vertreten (z.B. Spiegel-Tür oder Innenbeleuchtung). Wieder andere sind Artikel sind nur bei einem einzigen Schrank zu finden (z.B. spezielle Designelemente).

So langsam formt sich ein Bild.

Der Begriff "Artikelnummer" ist in der Tabelle auch ungünstig, "Materialnummer" wäre besser gewesen.

Die beiden Begriffe werden bei meinem Arbeitgeber synonym verwendet.

Ich halte die Struktur für "umständlich", da jedes weitere Produkt bedeutet, dass in der Datenbank ca. 20 weitere Tabellen erstellt werden

HALT! Jetzt muss ich mich Rolf anschließen. Ein Datenmodell, bei dem nur wegen einer Erweiterung des Datenbestands neue Tabellen angelegt werden, ist ein Rohrkrepierer. Da sollten nur neue Datensätze in vorhandene Tabellen eingefügt oder angehängt werden.

Für mich stellt sich das System sowieso schon als nur eine Tabelle dar. Die hätte dann im Minimalentwurf etwa folgende Spalten:

Bedeutung	Datentyp	Bemerkung[1]
ID	Zahl	unique, autoincrement
Materialnummer	String	kann also auch alphanumerisch sein
Klartextbezeichnung	String
Bestandteil von	Zahl	ID des übergeordneten Materials
Gültig ab	Datum
Preis	Zahl

Über die Spalte Bestandteil von können listen- und baumartige Materialhierarchien realisiert werden, und die Datumsspalte Gültig ab erlaubt es, dass jederzeit Materialeigenschaften (z.B. der Preis) geändert werden können, indem einfach ein neuer Datensatz für dieselbe Materialnummer, aber mit einem jüngeren Gültig ab-Datum angehängt wird. Der vorherige bleibt für historische Betrachtungen verfügbar, aber bei einer regulären aktuellen Abfrage darf von jeder Materialnummer nur der Eintrag mit dem jüngsten Datum berücksichtigt werden.

Mag sein, dass eure tatsächliche Datenstruktur noch etwas mehr hat oder braucht, aber so vom Ansatz her ...

Immer eine Handbreit Wasser unterm Kiel
 Martin

---

1. Verdammt, lässt die Markdown-Syntax das linksbündige Ausrichten mit einem Doppelpunkt (also |:Text|:Text) in Kopfzellen nicht zu? ↩︎

[Klaus1]

Hallo Martin,

ich würde die Struktur liebend gerne ändern, aber das geht leider nicht. Die Anwendung ist nicht quellcode-offen. Ich bin ja schon froh, dass ich an die Tabellen rankomme und überhaupt etwas selber steuern kann.

LG Klaus

[Der Martin]

Hi Klaus,

ich würde die Struktur liebend gerne ändern, aber das geht leider nicht.
Die Anwendung ist nicht quellcode-offen.
Ich bin ja schon froh, dass ich an die Tabellen rankomme und überhaupt etwas selber steuern kann.

wie steht dein Chef dazu?

Wenn ich mich so in diese Situation reindenke, und ich würde meinem Chef sagen, ich bräuchte etwa vier Wochen zum Abreißen und Neubauen, dann würde der vermutlich einen Moment zögern und dann zustimmen: Okay. Ist zwar eigentlich nicht dein Ressort, aber mach das, sobald du deine aktuellen Projekte fertig hast!

Die vier Wochen sind jetzt nur eine Hausnummer, vermutlich unterschätze ich die tatsächliche Komplexität. Basierend auf den Informationen hier im Thread ...

Natürlich kommt dann noch reichlich Zeit dazu, um die Anwendung von der "Laborversion" zu einem robusten und fehlerfr fehlerarmen System zu machen; auch über die Datenmigration muss man sich Gedanekn machen. Aber mittelfristig könnte ich mir vorstellen, dass das eure Perspektive sein muss.

Immer eine Handbreit Wasser unterm Kiel
 Martin

[Klaus1]

Das System ist leider deutlich komplizierter, mit Explosionszeichnungen aus SVG-Quellen, mit DrillDown etc. Und ich bin eigentlich gar kein Entwickler, insofern eher der schielende Einäugige mit grauem Star.

Ich versuche aktuell den Leuten den Hals zu retten, denen siedend heiß eingefallen ist, dass sich zum 01.01.2022 die Preise ändern und die ja auch in die techn. Dokumentation müssen. Plan B wäre alle Preise zu löschen, im Sinne von besser keine Preise als falsche Preise.

Seit Freitag nachmittag habe ich den "Auftrag" und bis Donnerstag muss es funktionieren, danach bin ich erst im neuen Jahr wieder da.

LG Klaus

[Ralf]

Hi,

zunächst solltest Du das Regelwerk (Geschäftsregeln) der Anwendung ergründen. Was passiert beim Buchungsvorgang? Welche Tabellen ändern sich in welcher Weise?

In ordentlichen Applikationen trennt man zwischen Bewegungsdaten und Stammdaten und es gilt der Grundsatz "gebucht ist gebucht". Das bedeutet, dass Bewegungsdatensätze nachträglich nicht mehr geändert werden dürfen.

Eine Preisänderung für einen Artikel könnte also in der Form stattfinden, dass zuerst der Stammdatensatz für den Artikel an die Preishistorie (Bewegungsdaten) angefügt wird und dann anschließend der Preis im Stammdatensatz geändert wird.

Wenn ein Produkt verkauft wird, wird ebenfalls Menge und Preis (und weitere Daten) in die Artikelpositionsdatei übernommen (append). Auch dieser Snapshot darf später nicht mehr verändert werden.

Usw., usw.

LG
Ralf

[Klaus1]

Hallo Ralf,

siehe Antwort an Martin.

LG Klaus

[Raketenwilli]

Der im Feld 'hash' angegebene Name referenziert auf Tabellen, die alle diesen hash beinhalten. Bspw. t_2e6f3a_articlenumber.

Könnte es sein, dass diese Tabellen irgendwie verknüpft sind? Siehe:

https://dev.mysql.com/doc/refman/8.0/en/partitioning-hash.html

Rolf B: Link verlinkt

[Rolf B]

Hallo Raketenwilli,

klingt dann aber so, als hätte jemand eine manuelle Partionierung gebaut, denn die Partitions eine Table sollten doch nicht als eigene Tables sichtbar sein, oder?

Rolf

[Raketenerrichter :-)]

klingt dann aber so, als hätte jemand eine manuelle Partionierung gebaut,

Das hoffe ich - zumindest im Hinblick auf den Rest der Beschreibung - nicht. Eine manuelle Partitionierung hätte ich mir nach dem gelesenen allenfalls als Archiv vorstellen können. Aber „in die Breite bauen“ und dazu für jeden Artikel neue Tabellen anlegen?

Wenn ich ganz ehrlich bin würde ich mir DAS Projekt mal richtig gerne ansehen wollen. Auch wenn es sich wohl gerade gezeigt hat, dass das nichts für einen Einzelkämpfer ist - der ja einen Nachfolger haben müsste. Ich würde - obwohl ich sowas sicher auch „basteln“ könnte, wahrscheinlich(!) zu einem Umstieg auf Standard-Software raten.

denn die Partitions eine Table sollten doch nicht als eigene Tables sichtbar sein, oder?

Hab das mangels Grund noch nicht probiert. Also keine Ahnung.

[Raketenwilli]

denn die Partitions eine Table sollten doch nicht als eigene Tables sichtbar sein, oder?

Hab das mangels Grund noch nicht probiert. Also keine Ahnung.

Vielleicht morgen. Hab noch eine VM mit der Flughafen-DB aus dem dicken MySQL-Schinken hier herumliegen. Da sind ausreichend „große“ Tabellen mit drin.

[Rolf B]

Hallo Raketenwilli,

in meinem Mariechen war das relativ einfach, ein CREATE TABLE mit PARTITION BY HASH(ID) und - nicht vergessen - PARTITIONS n mit n > 1, sonst macht er eine partitionierte Tabelle mit genau einer Partition.

An sich ist das noch nicht so sinnvoll, sinnvoll ist diese Nummer nur, wenn man die Partitionen auch auf unterschiedliche Datenträger verteilt, aber (a) hab ich die nicht in meinem PC (eine OS-SSD und eine Daten-HDD) und (b) hatte ich keine Lust, mir jetzt die Syntax anzulesen. Das schien umfangreich. Und deshalb bestehen die 3 Partitionen aus 3 Dateien im gleichen Data-Ordner. Zum Testen reicht's ja.

Und dann INSERT INTO katalog VALUES(FLOOR(RAND()*10000), ...) mit ein paar Dummyspalteninhalten, die Values-Klammer mit Strg+C/V verfünfzigfacht und die Finger eine Weile auf der EXECUTE Taste gehalten, fertig waren ein paar tausend Datensätze.

In information_schema.tables gab es dann genau einen Eintrag: katalog. Es war eine InnoDB-Table - es gibt eine andere information_schema-Tabelle zu Innodb-Tabellen, da stand jede Partition einmal drin (katalog#p#p1...), aber das konnte man nicht SELECTen.

Wenn ich ganz ehrlich bin würde ich mir DAS Projekt mal richtig gerne ansehen wollen.

Ja. Sicherlich sehr interessant zu betrachten. Ähnlich einem schweren Verkehrsunfall, es gruselt und gruselt und man kann den Blick nicht abwenden.

Rolf

[Raketenwilli]

In information_schema.tables gab es dann genau einen Eintrag: katalog. Es war eine InnoDB-Table - es gibt eine andere information_schema-Tabelle zu Innodb-Tabellen, da stand jede Partition einmal drin (katalog#p#p1...), aber das konnte man nicht SELECTen.

Dann kann ich mir das wohl sparen.

[Klaus1]

Hallo,

ab welcher Version gibt es denn diese Hash-Partitioning ? Auf dem Server läuft noch eine MySQL 5.5er. 😱

LG Klaus

[Rolf B]

Hallo Klaus1,

keine Ahnung, die Online-Doku von MYSQL geht nur bis 5.6 zurück. Da sie dort von "Problemen mit Partioning in Version 5.6" schreiben, vermute ich, dass es da neu war.

Aber guck ins information_schema. Gibt's da eine Table namens PARTITIONS?

Ich denke aber, dass da jemand manuell partitioniert hat. Zumindest bei meinem Test-Mariechen (v10.5) ist es so, dass die Partionstabellen nicht einzeln ansprechbar sind, weil sie einen Namen wie "katalog#p#p0" tragen und ein SELECT darauf sagt, dass die Tabelle nicht existiert (JA, ich habe Backticks verwendet).

Rolf

[Samuel fiedler]

Hallo Rolf B!

Wie kommen wir eigentlich auf “Patition“?
Heißt es Partition oder was?

Au revoir,
Samuel Fiedler

[Rolf B]

Hallo Samuel,

frag den Raketenwilli, der hat am r gespat.

Rolf

[Tabellenkalk]

Hallo,

frag den Raketenwilli, der hat am r gespat.

Sowas soll gelegentlich vorkommen...

Gruß
Kalk

[Der Martin]

Hallo,

Wie kommen wir eigentlich auf “Patition“?

das ist ausgleichende Gerechtigkeit. Andere Forenteilnehmer gehen ja so verschwenderisch mit den 'r' um, verdoppeln den Buchstaben aus unerfindlichen Gründen in Wörtern wie vorraus oder wiederrum.
Ich wünsche dann gern "Gute Besserrung".

Immer eine Handbreit Wasser unterm Kiel
 Martin