SQL-Abfrage Effizienz?

[Sven]

Hi,

ich möchte wissen, ob 2 Tabellen identisch viele Einträge zu einer ID innerhalb eines Vorganges XY haben.

SELECT ID 
FROM tabelle1 
WHERE Vorgang='XY' AND ID NOT IN (
SELECT ID FROM tabelle2 WHERE Vorgang='XY')
UNION 
SELECT ID 
FROM tabelle2 
WHERE Vorgang='XY' AND ID NOT IN (
SELECT ID FROM tabelle1 WHERE Vorgang='XY')

Ist das effizient oder geht das auch besser?

Sven

[Rolf B]

Hallo Sven,

Bitte erzähle mehr über deine Daten.

• es sieht offensichtlich danach aus, aber bestätige es bitte: Eine Vorgangskennung kann in der Tabelle in mehr als einer Zeile vorkommen?
• was hat es mit der ID auf sich? Ist sie - wie der Name suggeriert - eindeutig in der Tabelle? Oder nur eindeutig innerhalb einer Vorgangskennung? Oder selbst das nicht?

Rolf

[Sven]

Hi Rolf,

Bitte erzähle mehr über deine Daten.

• es sieht offensichtlich danach aus, aber bestätige es bitte: Eine Vorgangskennung kann in der Tabelle in mehr als einer Zeile vorkommen?
• was hat es mit der ID auf sich? Ist sie - wie der Name suggeriert - eindeutig in der Tabelle? Oder nur eindeutig innerhalb einer Vorgangskennung? Oder selbst das nicht?

1. Ja
2. Die ist eindeutig, auch über die Vorgänge hinweg

Sven

[Rolf B]

Hallo Sven,

2 Tabellen identisch viele Einträge zu einer ID innerhalb eines Vorganges XY

Normalerweise würde ich jetzt einen FULL OUTER JOIN über beide Tabellen vorschlagen, aber das ist ein SQL Feature, das MYSQL bis heute nicht unterstützt. Mit dem könntest Du dann schauen, ob in einer der beiden Tabellen für die ID der NULL-Wert herauskommt. Natürlich gibt es diverse Möglichkeiten, einen FULL JOIN zu nachzubilden. Aber das ergibt keine einfachere Lösung als das, was Du schon hast.

So kann ich Dir nur noch UNION ALL vorschlagen statt UNION, weil das Weglassen der ALL Option einen weiteren Durchlauf durch das Ergebnis produziert, der Duplikate entfernen soll. Die sind in deiner Abfrage aber nicht möglich.

Statt NOT IN (SELECT ID ...) könnte man auch NOT EXISTS verwenden, aber das ändert nichts an dem, was der Server daraus macht. Zumindest entsteht bei mir daraus der gleiche EXPLAIN. Ich habe im unten stehenden Statement mal die NOT EXISTS Variante für den zweiten Teil aufgeschrieben, falls Du nicht weißt, was ich meine. Du kannst ja bei deiner DB mal schauen, ob Du damit einen günstigeren EXPLAIN bekommst (meine war vielleicht zu klein), aber ich glaube nicht dran.

Du könntest die SELECT Liste noch um eine Tabellenkennung erweitern, damit Du weißt, in welcher Tabelle eine überzählige ID steht.

SELECT 'Tabelle1' as Quelle, ID 
FROM tabelle1 
WHERE Vorgang='XY' 
  AND ID NOT IN (SELECT ID FROM tabelle2 WHERE Vorgang='XY')
UNION ALL
SELECT 'Tabelle2' as Quelle, ID 
FROM tabelle2 
WHERE Vorgang='XY' 
  AND NOT EXISTS (SELECT * FROM tabelle1 
                  WHERE Vorgang='XY'
                    AND tabelle1.ID = tabelle2.ID)

Was auf jeden Fall auch hilft, ist je ein Index auf Vorgang und ID.

Rolf

[MudGuard]

Hi,

ich möchte wissen, ob 2 Tabellen identisch viele Einträge zu einer ID innerhalb eines Vorganges XY haben.

SELECT ID 
FROM tabelle1 
WHERE Vorgang='XY' AND ID NOT IN (
SELECT ID FROM tabelle2 WHERE Vorgang='XY')

Ist das effizient oder geht das auch besser?

bei Mysql 8 könnte man auch mit EXCEPT statt dem NOT IN arbeiten.

Ob das effizienter wäre, kann ich nicht sagen.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo MudGuard,

ja, hm, das wäre dann sowas wie

(SELECT id FROM tabelle 1 WHERE vorgang='XY'
EXCEPT
SELECT id FROM tabelle 2 WHERE vorgang='XY')
UNION ALL
(SELECT id FROM tabelle 2 WHERE vorgang='XY'
EXCEPT
SELECT id FROM tabelle 1 WHERE vorgang='XY')

In meinem Mariechen hat der Explain zu diesem Ding 8 Zeilen und ich verstehe ihn nicht wirklich.

Weglassen der Klammern um den ersten Teil liefert das gleiche, hat aber nur 7 Zeilen im EXPLAIN:

SELECT id FROM tabelle 1 WHERE vorgang='XY'
EXCEPT
SELECT id FROM tabelle 2 WHERE vorgang='XY'
UNION ALL
(SELECT id FROM tabelle 2 WHERE vorgang='XY'
EXCEPT
SELECT id FROM tabelle 1 WHERE vorgang='XY')

Die Klammern um den zweiten Teil sind erforderlich, sonst wird zu viel entfernt.

Der Explain zu Svens Ansatz hat 4 Zeilen. Aber ein kleinerer Explain ist nicht unbedingt eine schnellere Query, das müsste man mit einer größeren DB und korrekten Indexen messen. Meine ist zu klein und ich mag mir jetzt keine Dummy-DB zusammenscripten.

Rolf

[Sven]

Hi Andreas, Hi Rolf,

danke für Eure Ansätze. ich habe mir nun eine andere Strategie überlegt.
Hintergrund ist, dass ich diese Abfrage für verschiedene IDs in verschiedenen Tabellen recht oft abfragen müsste, das wird mir so aber nicht effizient genug. Bei einer einzelnen Abfrage ist die Query ok, aber diese dann ca. 6-8 mal plus noch 4-6 weitere kompliziertere Abfragen machen das dann über Ajax alle 3-6 Sekunden eingebunden wirklich zu einem Reccourcenfresser.

Was mache ich da eigentlich?

Es geht darum, dass von verschiendenen Usern Vorgänge bearbeitet werden können, die dann in einer Art Endvorgang enden. Die Daten des Endvorgangs sollen immer aktuell sein, also muss ich dem User einen Hinweis geben, wenn sich Daten geändert haben und der Vorgang aktuallisiert werden muss. Dennoch soll der user selber entscheiden, ob oder wann er aktualisiert, er soll nur informiert werden. Und da ich keine Websockets nutzen kann, muss hier JS, bzw. Ajax her.

Meine neue Strategie ist nun aber, auf alle relevanten Tabellen je einen Insert, Update und Delete Trigger zu legen, der VorgangsID und Datetime in einer Kontrolltabelle speichert. So braucht mein per Ajax eingebundenes Script nur noch prüfen, ob es einen Eintrag in der Kontrolltabelle gibt, der eine der VorgangsIDs des Endvorgangs beinhaltet und zugleich jünger ist als der Zeitpunkt des Aufrufes des Endvorgangs.

Da ich nach Beendigung des Endvorgangs die Kontrolltabelle säubern kann, könnte ich sicher mit weniger als 1000 aktuellen Einträgen in der Kontrolltabelle auskommen. Da dürfte dann die eigentliche Kontrolle rasend schnell gehen und ich kann sie alle 2-3 Sekunden ausführen.

Sven

[Raketenwilli]

Die Daten des Endvorgangs sollen immer aktuell sein, also muss ich dem User einen Hinweis geben, wenn sich Daten geändert haben und der Vorgang aktuallisiert werden muss.

Ich nehme mal an, dass die Benutzer in einer Tabelle eine oder mehrere Spalten updaten...

Du machst etwas wie

UPDATE table SET foo = 1 WHERE id = 2

und willst verhindern, dass das gemacht wird, wenn ein anderer Benutzer inzwischen den Wert (für foo) oder einen anderen Wert in der Zeile verändert hat.

Also machst Du ganz einfach:

UPDATE table SET foo = 1 WHERE id = 2 AND foo = 0 AND bar = 3;

(Das Update wird sodann also nur gemacht, wenn ALLE Spalten dem jeweils erwarteten/bekannten Wert entsprechen.)

• Du bekommst die Anzahl der veränderten Zeilen zurück.

Und „Heureka!“ Ist die Anzahl der veränderten Zeilen == Null (0) dann „bedarf der Vorgang einer Aktualisierung“. Und das ganze ohne die Tabelle[n] oder Zeile[n] performancefressend auf Veränderungen zu überwachen.

Freilich könntest Du einen Updatezähler als Spalte in die Tabelle einbauen und den prüfen… müsstest den dann nur bei jedem Update der Zeile eins höher setzen als er aktuell ist (SET UpdateCounter=UpdateCounter+1)

[Rolf B]

Hallo Raketenwilli,

was Du da beschreibst, nennt sich "optimistisches Sperrverfahren" und lässt sich am einfachsten mit einer "last_update" oder "last_version" Spalte je Table lösen. In last_update steht der Timestamp des letzten Update, in last_version ein Zähler, der bei jedem Update inkrementiert wird (und gerne nach einer geeigneten^[1] Zahl von Updates neu starten kann).

Diese Spalte kann man wiederum mit einem Trigger pflegen. Man muss sich beim SELECT den last_xxx Wert merken und bei UPDATE und ggf. auch DELETE das WHERE last_xxx = :last_xxx hinzufügen.

Ein expliziter Test aller Spalten auf "geändert" ist zerbrechlicher und aufwändiger Code. Vom Tempo will ich nicht reden, das ist vermutlich vernachlässigbar. Man muss aber stets die Alt-Werte aller Spalten in den Update geben, und man muss beim Hinzufügen/Entfernen von Spalten sämtliche Update-Statements für diese Tabelle anpassen. Ein Repository bzw. ein ORM-Framework kann das kapseln, aber es ist dennoch ordentlich Arbeit.

Rolf

---

1. Eignungskriterium ist die Updatefrequenz und die erwartete Dauer, die eine Application einen Datensatz im Speicher hält ↩︎

[Raketenwilli]

Hm.

• "last_update" kann zu ungenau sein. (Sekunden...). Kann man aber für Informationszwecke vorhalten. (Dann wohl auch gleich mit dem Auslöser, was oder wer auch immer das sei).

• Das von mir favorisierte "last_version" - hatte ich als „UpdateCounter“ im letzten Absatz vorgeschlagen. Womöglich hätte ich auf die (sodann von Dir genannten) Vorteile hinweisen sollen. Als bigint kann das bis zu 2^64-1 (= 18446744073709551615) Updates durchnummerieren. Das reicht eine Weile. Aber man kann den Wert ja tatsächlich, wenn man nicht so dumm ist, keine(n) kleinere(n) last_version/UpdateCounter zu erwarten, wie von Dir beschrieben den Wert auch auf 0 setzen. Womöglich tun es die rund 4 Milliarden von int.

[Rolf B]

Hallo Raketenwilli,

"last_update" kann zu ungenau sein

Ja, sorry, ich hätte TIMESTAMP(6) schreiben sollen. Ich bin nicht der intensive MYSQL-User und habe nicht bedacht, dass der Default für die Sekunden-Nachkommalänge dort 0 ist. Aber auch im Mikrosekundenbereich kann der Teufel ein Eichhörnchen sein und zu gleichen Timestamps führen. Ich denke da zu oft an DB2 für z/OS, das einem garantiert, dass zwei Abfragen von CURRENT_TIMESTAMP nicht den gleiche Wert liefern. Das ist aber nur dort so.

hatte ich als „UpdateCounter“ im letzten Absatz vorgeschlagen

Das lief parallel. Du hast das um 13:42 eingefügt und meinen Post habe ich um 13:46 abgeschickt.

Und ja, das ist die bessere Idee.

Rolf

[Raketenwilli]

Ein expliziter Test aller Spalten auf "geändert" ist zerbrechlicher und aufwändiger Code. Vom Tempo will ich nicht reden, das ist vermutlich vernachlässigbar.

Die Datenbank wird sich ja hoffentlich erst auf die (hoffentlich) indexierte ID stürzen, sodann die eine gefundene Zeile hernehmen und die Werte der Spalten vergleichen... Ich glaube, alle Datenbanken optimieren die Abfragen im ersten Step genau so…

[Sven]

Hallo zusammen,

und willst verhindern, dass das gemacht wird, wenn ein anderer Benutzer inzwischen den Wert (für foo) oder einen anderen Wert in der Zeile verändert hat.

Nein, darum gings mir nicht.
Es geht nicht nur, aber z.b. um Rechnungsstellung. Jemand schreibt eine Rechnung, währenddes ein andere User oder sogar der rechnungsstellende User im Vorgang noch etwas ändert. Wenn dann die Rechnung nicht aktualisiert wird, fehlt z.b ein Artikel.

Selbst wenn die Rechnung alle Daten dynamisch aus den Vorgängen ziehen würde (was sie aus gutem Grund nicht tut), wäre die einmal ausgelieferte Seite beim User dann erstmal nicht mehr korrekt, weil inzwischen verändert. Darauf will ich den User hinweisen. Alternativ könnte man auch den Artikel über JS ins DOM einbauen, aber mir ist ein Hinweis lieber als eine solche Automatik.

Sven

[Raketenwilli]

Naja. Das Problem behebt die last_version bzw. der UpdateCounter. Wenn die auf Grund des Wirkens des Bearbeiters B nicht mehr stimmen kann das Update nicht erfolgen und der Bearbeiter A erhält seinen Hinweis, kann nochmal korrigieren oder die Änderungen akzeptieren.

Genaueres oder gar anderes kann man nur bei genauer Kenntnis des Workflows sagen.

Selbst wenn die Rechnung alle Daten dynamisch aus den Vorgängen ziehen würde (was sie aus gutem Grund nicht tut),

Die gemutmaßte „Notwendigkeit“ eines manuellen Anpassens von Rechnungen ist, genau genommen, ein Problem des Workflows. Möglicherweise haben da ein paar Leute im Vertrieb zu krumme Berechtigungen, um sonstwie-bedingte Rabatte zu gewähren, welche sich mit der IT (bisher) nicht abbilden lassen.

Das ist sehr fehlerträchtig (Menschen irren sich häufig) und führt im Fehlerfall zu Streit über die Rechnung und daraus womöglich zu Kundenverlusten. Und wie macht ihr das dann mit Bestellbestätigungen und/oder Lieferscheinen, meinetwegen Zolldokumenten? Wie macht ihr das mit dem vier-Augen-Prinzip? Oder kann da ein Sachbearbeiter „frank und frei“ Rechnungen mindern und sich dann beim Kunde einen Barscheck über 10% der Minderung abholen?

Die Abbildung mitunter kruder Rabattierungen durch die IT ist das Rad, an dem in der einen oder anderen Richtung gedreht werden muss. Ich kenne da jemanden, der hat bei sowas „Spaß an der Freude“.

[Rolf B]

Hallo Sven,

Jemand schreibt eine Rechnung, währenddes ein andere User oder sogar der rechnungsstellende User im Vorgang noch etwas ändert

Das kann nicht sein. Entweder ist der Vorgang abgeschlossen, und die Rechnung wird erstellt, oder er ist es nicht, und die Rechnung kann noch nicht erstellt werden.

Dass jemand am Vorgang rumändert während ein anderer Benutzer die Rechnung erstellt, ODER jemand eine Rechnung schreibt während Änderungen stattfinden, muss technisch verhindert werden.

Die Funktion "Rechnung erstellen" kann natürlich diesen Abschluss automatisch durchführen. Falls in dem Moment Änderungen offen sind, sind die nicht mehr speicherbar. Der Vorgang wurde abgeschlossen. Dass das überhaupt passieren kann (A ändert während B abschließt), muss organisatorisch verhindert werden.

Natürlich ist denkbar, dass die Rechnung korrigiert werden muss. Dafür muss der Vorgang dann wieder geöffnet werden und eine neue Rechnung erstellt werden, das muss aber in einen sauberen Workflow eingebunden sein.

Rolf

[Sven]

Hallo Rolf,

Das kann nicht sein. Entweder ist der Vorgang abgeschlossen, und die Rechnung wird erstellt, oder er ist es nicht, und die Rechnung kann noch nicht erstellt werden.

Dass jemand am Vorgang rumändert während ein anderer Benutzer die Rechnung erstellt, ODER jemand eine Rechnung schreibt während Änderungen stattfinden, muss technisch verhindert werden.

Scheint die sauberste Lösung zu sein. Zumindest, wenn man dafür sorgt, dass nur der User den Vorgang wieder öffnen kann, der auch die Fakturierung eingeleitet hat.
Bleibt trotzdem ein riesen Problem, denn:

• User schließt Vorgang und leitet Fakturierung ein (oder Vorgang schließt automatisch bei Einleitung der Fakturierung)
• User merkt, dass er einen Artikel vergessen hat (Wert 1200 Euro)
• User macht Vorgang in neuem Tab auf, öffnet ihn, trägt Artikel ein
• User schließt Vorgang sowie Vorgangs-Tab wieder
• Telefon klingelt
• User abgelenkt
• Paketbote kommt
• Mitarbeiter verletzt sich, User leistet erste Hilfe
• ....
• ....
• User kehrt zu PC zurück
• User sieht "achja, die Rechnung muss noch raus"
• User druckt, Teil fehlt, weil nicht aktualisiert

Es bleibt also dabei, es ist egal, ob ich technisch verhindere, dass der Vorgang nach Einleitung der Fakturierung noch bearbeitet werden darf. Kann ich machen (weil nach Rechnungsdruck ists mit Beaebeiten eh vorbei), aber es nervt höchstens den User in der Praxis, dass er den Vorgang wieder öffnen und schließen muss. Ergebnis ist und bleibt, dass der User erinnert werden soll, dass er sich den aktuellen Vorgang nochmal anschauen muss.

Ich möchte einfach, dass der User vor dem Druck (der den Vorgang, sowie die Rechnung für immer versiegelt, zumindest bis zum Storno oder der Gutschrift) nochmal die aktuellste Version sehen kann/soll/will/muss.
Einzige Option wäre (ähnlich wie bei Online-Banking) vor dem Druck nochmal eine letzte Übersicht einzublenden. Aber bei teilweise bis zu 100 Artikeln in der Rechnung ist das auch nicht wirklich hilfreich. Deshalb präferiere ich die Lösung, dass der User inforniert wird, wenn sich seine clientseitige-Rechnungsdarstellung inzwischen serverseitig geändert hat.

Sven

[Raketenwilli]

Es bleibt also dabei, es ist egal, ob ich technisch verhindere, dass der Vorgang nach Einleitung der Fakturierung noch bearbeitet werden darf. Kann ich machen (weil nach Rechnungsdruck ists mit Beaebeiten eh vorbei), aber es nervt höchstens den User in der Praxis, dass er den Vorgang wieder öffnen und schließen muss.

Ganz einfache Regel: Die auf der Rechnung stehenden Posten werden durch das (beim) Erstellen der Rechnung als „fakturiert“ gekennzeichnet. Wenn jetzt noch was hinzugekommen wäre: 2. Rechnung über die bisher nicht fakturierten Positionen.

Das kostet weniger als irgendwelches Gemurkse und ist dann auch buchhalterisch simpler als „Rechnung ändern“. Denn dazu müsste nämlich zur einmal erstellten Rechnung eine Gutschrift in gleicher Höhe erstellen - und sodann eine neue Rechnung erstellt werden... dass man die „falsche“ Rechnung und die Gutschrift auch versenden muss ist ja nirgendwo festgeschrieben.

[Sven]

@@Rakete

Es bleibt also dabei, es ist egal, ob ich technisch verhindere, dass der Vorgang nach Einleitung der Fakturierung noch bearbeitet werden darf. Kann ich machen (weil nach Rechnungsdruck ists mit Beaebeiten eh vorbei), aber es nervt höchstens den User in der Praxis, dass er den Vorgang wieder öffnen und schließen muss.

Ganz einfache Regel: Die auf der Rechnung stehenden Posten werden durch das (beim) Erstellen der Rechnung als „fakturiert“ gekennzeichnet. Wenn jetzt noch was hinzugekommen wäre: 2. Rechnung über die bisher nicht fakturierten Positionen.

Das kostet weniger als irgendwelches Gemurkse und ist dann auch buchhalterisch simpler als „Rechnung ändern“. Denn dazu müsste nämlich zur einmal erstellten Rechnung eine Gutschrift in gleicher Höhe erstellen - und sodann eine neue Rechnung erstellt werden... dass man die „falsche“ Rechnung und die Gutschrift auch versenden muss ist ja nirgendwo festgeschrieben.

Ich halte sehr viel von Dir und Deinen Ratschlägen, aber dieser ist ja sowas von praxisfremd. Das würden die Kunden meiner Kunden niemals akzeptieren, so läuft das wirklich in weiten Teilen der freien Wirtschaft nicht.

Freu mich weiter über Deine tips, aber dieser war "ein Satz mit X" 😉

Sven

[Gast]

Nicht sicher, ob ich es richtig verstehe, aber warum nicht einfach etwas in der Art:

SELECT (
  (SELECT COUNT(id) FROM tabelle1 WHERE vorgang = 'XY') =
  (SELECT COUNT(id) FROM tabelle2 WHERE vorgang = 'XY')
) equal_count

Auf vorgang sollte ein Index liegen (WHERE index)... siehe dieses SQL Fiddle

[Sven]

Nicht sicher, ob ich es richtig verstehe, aber warum nicht einfach etwas in der Art:

SELECT (
  (SELECT COUNT(id) FROM tabelle1 WHERE vorgang = 'XY') =
  (SELECT COUNT(id) FROM tabelle2 WHERE vorgang = 'XY')
) equal_count

Auf vorgang sollte ein Index liegen (WHERE index)... siehe dieses SQL Fiddle

Hi,

die equal_count kenne ich gar nicht. Bist Du sicher, dass es die in mysql gibt, ich finde da nichts drüber.

Sven

[Gast]

Hi Sven,

die equal_count kenne ich gar nicht. Bist Du sicher, dass es die in mysql gibt

Nö, die "gibt's so nicht," ist lediglich ein Alias (hoffe das stimmt) fürs query result :)

[MudGuard]

Hi,

SELECT (
  (SELECT COUNT(id) FROM tabelle1 WHERE vorgang = 'XY') =
  (SELECT COUNT(id) FROM tabelle2 WHERE vorgang = 'XY')
) equal_count

schwer zu sagen, ob das auch zum richtigen Ergebnis führt, da zu wenig über die Hintergründe bekannt ist.

Aber wenn zu Vorgang XY 4 Teile A, B, C, D existieren, und je 2 davon in tabelle1 und 2 in tabelle2 sind, kommt true raus, obwohl es Unterschiede gibt.

Die Frage (an Sven) ist für mich: warum gibt es überhaupt mehrere Tabellen für die Vorgänge?

cu,
Andreas a/k/a MudGuard

[Sven]

Hallo Anfdreas,

Die Frage (an Sven) ist für mich: warum gibt es überhaupt mehrere Tabellen für die Vorgänge?

Einerseits berechtigte Frage (liegt einfach an den zu unterschiedlichen Vorgangsdaten), andererseits würde es an meinem Grundproblem nichts ändern, wenn das anders wäre.

Eine einmal ausgelieferte Seite ist nicht mehr aktuell wenn sich in den Vorgangstabellen (ob eine, zehn oder 50) etwas ändert, selbst wenn die ausgelieferte Seite alle daten dynamisch und komplett ohne Redundanz ziehen würde.

Sven