Generische SQL-Abfragen für Listen

[ChristianK]

Hallo zusammen

Ich bewege mich in letzter Zeit etwas im Kreis. Ich versuche eine möglichst generische Implementation zu finden, um Tabellen abzufragen. Dabei gibt es eine Reihe von Bedingungen, die gelten können (WHERE sowie HAVING) und Gruppierungen, die unter Umständen durchgeführt werden müssen. Können und "unter Umständen" ist bewusst gewählt, denn es gibt praktisch keine 1:1-Abfrage...

Ein konkretes Beispiel:
Ich habe Bestellungen. Bestellungen bestehen aus Artikeln. Jede Bestellung kann zwischen 0 und n Liefereingänge haben und demnach den Status offen, teilgeliefert oder erledigt besitzen. Ein Liefereingang besteht aus Transaktionen, wobei eine Transaktion jeweils die Bewegung eines Artikels repräsentiert.

Die Aufgabe:
Erzeuge eine Liste von Bestellungen, die
- nach Status gefiltert werden kann
- und/oder nach Kunde gefiltert werden kann
- und/oder nach Artikel gefiltert werden kann

Selbstverständlich kann jede Liste noch durchsucht werden und besitzt Pagination bzw. Sortiermöglichkeit. Die Anzahl an Tabelleneinträgen ist als 100'000 anzunehmen. Eine End-/Teilverarbeitung in PHP ist also unmöglich.

Beispielquery:

SELECT
o.*
FROM order as o
LEFT JOIN supply as s
LEFT JOIN transaction as t
GROUP BY
o.order_id
HAVING SUM(t.amount) < o.ordered_amount

Die Problematik hierbei ist, dass diese vielen und/oder-Bedingungen die Komplexität des Query extrem erhöhen. Ich möchte so wenig wie möglich duplizierten Code haben und alle Bedingungen sollten kreuzweise ausgeführt werden können. Dabei soll weiterhin das Model generisch sein, denn die Aufgabe tritt mehrfach auf.

Ich möchte die Diskussion möglichst unabhängig von der Technologie haben. Ich verwende in diesem Projekt kein ORM (evt. kennt ihr meine Meinung dazu). Ich benutze jedoch einen Query-Builder (siehe Signatur) um möglichst viel Arbeit in SQL zu erledigen.

Die Frage:
Wie löst ihr solche Aufgaben? Habt ihr eine Vorlage für ein Modell? Kennt ihr irgendwelche Tricks? Mache ich mir das Leben zu schwer oder die Aufgabe zu komplex?

Meine aktuellen Ideen:
Im Moment schwirren mir einige Ideen im Kopf herum:
- Eine möglichst generische Klasse zu erstellen, die mir das Erstellen des Queries nach vordefinierten Schemen vornimmt
- Die Auslösung des Queries in Stufen, ähnlich einem mehrfachen Select (SELECT TOP 30 FROM [nach Status] FROM [nach Menge])
- mehr Kaffepausen einzulegen und Kopf -> Tisch ^^

[erc]

Je nach dem was für eine Performance gefragt ist entweder VIEWs oder die Infromationen denormalisiert speichern, In dem Fall würde ich eine Spalte status in order packen. Den Query den du gepostest hast willst du definitv nicht zur Laufzeit eines HTTP Requests ausführen.

[ChristianK]

Löst eine View das Problem tatsächlich? Wenn ich eine komplexe Abfrage für die View habe, drückt dies doch auch stark auf die Performance, die View wird dann doch relativ oft aktualisiert?

Allerdings auch eine gute Idee, ich werde mal schauen ob ich damit etwas besser zurecht komme. Würde auf jeden Fall das Filtern/Offsetten stark vereinfachen.

Wie wird das wohl in grösseren ERP-Systemen gemacht?

[hellbringer]

https://de.wikipedia.org/wiki/Sicht_...erialized_View

[erc]

Löst eine View das Problem tatsächlich?

Ein View ist ein möglicher Ansatz, ob es die Lösung ist steht auf einem anderen Blatt. Du kannst mit einem View komplexität verbergen und zentrallisieren. Wenn ich mich an deinem Beispiel orientiere und annehme du brauchst öfteres die Infromation soll und ist, dann könntest du das so als View abbilden:

CREATE VIEW order_extended AS
    SELECT
        o.*, SUM(t.amount) AS supplied_amount
    FROM order as o
        LEFT JOIN supply as s
        LEFT JOIN transaction as t
    WHERE
        //komplexität
        transaction.type IN ('in', 'transfer') AND
        supply.foo = 1 AND
        ...          
    GROUP BY
        o.order_id

Verwendung dann z.B. so:

SELECT
    *
FROM
    order_extended INNER JOIN
    customer ON (....)
WHERE
    orders_extended.ordered_amount > orders_extended.supplied_amount

SELECT
    DATE_FORMAT(order_extended.date, '%Y.%m.%d') AS date
    COUNT(*) AS orders
FROM
    order_extended INNER JOIN
    customer ON (....)
WHERE
    orders_extended.ordered_amount = orders_extended.supplied_amount AND
    orders.date BETWEEN '2015-01-01 00:00:00' AND NOW()

SELECT
    DATE_FORMAT(order_extended.date, '%Y.%m.%d') AS date
    SUM(IF(orders_extended.ordered_amount = orders_extended.supplied_amount, 1, 0)) AS orders_finish
    SUM(IF(orders_extended.ordered_amount > orders_extended.supplied_amount, 1, 0)) AS orders_open
FROM
    order_extended INNER JOIN
    customer ON (....)
WHERE
    orders.date BETWEEN '2015-01-01 00:00:00' AND NOW()

Den View kannst du natürlich auch spezieller formulieren, das musst du abschätzen was du brauchst.

Wenn ich eine komplexe Abfrage für die View habe, drückt dies doch auch stark auf die Performance, die View wird dann doch relativ oft aktualisiert?

Das ist das große ABER bei einem View. Bei performancelastige Abfragen hilft dir das in der Form nicht weiter. Performance und Komplexität korrelieren aber nicht zwangsläufig. Eine Abfrage kann für den Entwickler komplex sein, für die Datenbank aber völlig problemlos. Noch dazu kommt das durch den View eventuell Overhead dazu kommt. Bei gewissen Konstelationen muss der View als Subquery ausgeführt werden und das drückt dann auch die Leistung, unter umständen massiv. Das musst du im zweifel testen!
Ein Möglichkeit den Performanceprobleme aus dem Weg zu gehen wären dann materialized Views. Das erkaufst du dir aber durch asynchronität und eventuell mit problemen beim Schreiben. Materialized Views sind mehr für Asuwertungen geeignet als für OLTP Aufgaben. Für OLTP kommst du besser wenn du gezielt denormalisierst.

[ChristianK]

Ein View ist ein möglicher Ansatz, ob es die Lösung ist steht auf einem anderen Blatt. Du kannst mit einem View komplexität verbergen und zentrallisieren. Wenn ich mich an deinem Beispiel orientiere und annehme du brauchst öfteres die Infromation soll und ist, dann könntest du das so als View abbilden:
[...]

Die Idee ist, "inline" bereits detaillierte Informationen anbieten zu können. Also auf einer Bestellungsübersicht anzeigen zu können, wann der letzte Bestelleingang gekommen ist, wie viel % schon geliefert wurde etc. Möglichst präzise Informationen, die dem Endanwender helfen, anbieten, ohne dafür 10 Klicks mehr zu benötigen. Deshalb, ja, die View scheint mir eine mögliche (gute) Lösung zu sein.

Das ist das große ABER bei einem View. Bei performancelastige Abfragen hilft dir das in der Form nicht weiter. Performance und Komplexität korrelieren aber nicht zwangsläufig. Eine Abfrage kann für den Entwickler komplex sein, für die Datenbank aber völlig problemlos. Noch dazu kommt das durch den View eventuell Overhead dazu kommt. Bei gewissen Konstelationen muss der View als Subquery ausgeführt werden und das drückt dann auch die Leistung, unter umständen massiv. Das musst du im zweifel testen!
Ein Möglichkeit den Performanceprobleme aus dem Weg zu gehen wären dann materialized Views. Das erkaufst du dir aber durch asynchronität und eventuell mit problemen beim Schreiben. Materialized Views sind mehr für Asuwertungen geeignet als für OLTP Aufgaben. Für OLTP kommst du besser wenn du gezielt denormalisierst.

Ich kann dieses Problem allerdings umgehen, wenn ich für Updates die echte Tabelle angebe und die materialized View nur zum Lesen verwende? Weil ob der letzte Bestelleingang 127ms verzögert dargestellt wird, spielt wirklich keine Rolle mehr. Eigentlich also die View im klassischen Sinne lesend, allerdings dank MV performanter. So wie ich das verstanden habe, ist das auch ein update-on-change-Prinzip. Solange ich nur lesend auf die Tabellen (View sowie echte Tabelle) zugreife, aktualisiert sich die View auch nicht.

MV wird ja von MySQL nicht unterstützt, PG kann das allerdings, oder?

[erc]

In der Regel kannst du in ein View nicht schreiben. Wie der Name schon andeutet ist das eine Sicht auf die Daten. Es gibt Views die "durchschreibbar" sind, aber das ist eher ein Sonderfall und auch nur begrenzt möglich. Deine 127ms sind ziemlich optiomistisch für ein materialized View mit 100.000 Datensätzen. Dein "update-on-change" kannst du damit zwar realisieren, aber das ist teuer. Die Datenbank ist an der Stelle dumm und kann nur den kompletten View neu erstellen. Du kannst dir also ausrechnen was dich das kostet.

MV wird ja von MySQL nicht unterstützt, PG kann das allerdings, oder?

Ja, ist aber auch mit Mysql kein Problem. Anstatt eines Views erzeugst du eine Tabelle mit der Sturktur des "Views" und packst da je nach Bedarf mit TRUNCATE und INSERT INTO ... SELECT FROM die Daten rein.

[Perry Staltic]

Ein View bringt keine Performance (Ausnahme: hochfrequente Abfragen durch Vorkompilierung) gegenüber dem Statement selbst, er blendet nur Komplexität aus und eigenet sich dadurch z.B. für verschiedene, logische Auswertungsschichten, gezielte Rechtevergabe, Interfaces, etc.
Ich arbeite sehr viel mit Views und verfahrenstechnisch gibt es da den Nachteil, dass man alle möglichen/gewünschten Filterkriterien "an die Oberfläche" bringen muss, obwohl eine innere Einschränkung in einem offenen, komplexen Select manchmal viel günstiger wäre- immer dann, wenn man das Filter Kriterium im Ergebnis nicht sehen will- was ja bei einem adhoc Ergebnis eigentlich normal ist, idR weiß man ja sowieso, wonach man filtert.
MV helfen der Performance schon eher oder Denormalisierung. In dem Beispiel mit den Bestellungen könnte es bspw. hilfreich sein, den Bestellstatus aus der Artikelebene per Trigger zu bilden oder ggF. 2 Stati, einer nur für Artikelzustand, einer für echten Bestellstatus.
Ebenfalls hilfreich sind natürlich große Einschränkungen der Gesamtmenge, bspw. über Zeitraum, Kunde, Produktkategorie.. ein komplexes Selekt wird dann nicht mehr auf 100000 DS ausgeführt, sondern eben nur noch auf ein paar Tausend. Hier wäre die Frage, wie geschickt man das UI bauen kann, dass solche Vorfilterungen nicht den Anwender nerven.
Auch hilfreich ist ein guter Query Optimizer, da habe ich aber bei mySQL keine relevante Erfahrung.
Das ganze in ein Universal Pattern zu quetschen ist keine einfache Sache. An der Stelle würden aber Views sicher helfen, Viewgruppen die für verschiedene UI Zustände / Schritte gemacht sind. Ich würde da z.B. grundsätzlich Rohdatenviews bauen / zur Verfügung stellen und dann diese z.B. in einer 2. Schicht gruppiert anbieten, also erstmal eine sehr grobe 2 Teilung. Kann man sicher verfeinern. Ein weitere Clusterung wäre natürlich der Detaillierungsgrad, z.B. Views inkl. Artikeldaten oder exkl..

Dann wäre noch ganz unabhängig von der SQL Perspektive die Möglichkeit, bestimmte Daten (Counts, SUMs, ..) asynchron im Ui nachzuladen, dann natürlich selektiv, wenn die Seite angeblättert wird.

[ChristianK]

Denormalisierung finde ich keine gute Lösung. Das erhöht immer das Risiko, plötzlich Felder zu haben, die nicht aktualisiert werden oder dann Berechnungen, die auf Basis falscher Daten gemacht werden. Soweit möglich möchte ich nicht denormalisieren müssen.

Wenn ich das hier so lese und mir das überlege, dann denke ich, dass eine View bereits sehr viel Potential mit sich bringt. Das Beispiel mit den 100'000 war vielleicht etwas hoch gegriffen. Allerdings ist davon auszugehen, dass das System wächst und ich hatte bereits einen "Fall", wo meine Überlegungen zwar mit 100 Zeilen top funktioniert hat, mit 10'000 allerdings zu einem Disaster wurde. Deshalb muss/will ich sicherstellen, dass nach oben genug Raum besteht.

Asynchron nachladen tönt sehr interessant, ist allerdings auch wieder sehr schwierig/aufwändig zu integrieren. Diese Option wird vermutlich aus zeitlichen Gründen bereits wegfallen.

Ein Wechsel zu PG ist im Moment leider etwas ungünstig. Vermutlich werde ich mit "manuell aktualisierten materialized Views" auskommen müssen.

[strub]

Ich habe dies z.B folgenermassen umgesetzt:

Das Bestell-Objekt hat alle nötigen Spalten die für die Sortierung nötig sind.

z.B user_id, status_id, total_amount etc.

Damit habe ich auch keine komplexen Queries.


Wenn ich nun z.B. alle Bestellungen von einem Benutzer anzeigen will, muss ich nur dessen user_id wissen und schon ist die Query ein leichtes.


Edit: nun nach Artikel zu Sortieren ist natürlich auch bei mir etwas komplexer, da diese in einer seperaten Tabelle sind, was auch Sinn macht.