Index geschickt setzen für Tabellenvergleich

[Jumpy]

Hallo,

ich soll für eine unveränderbare Fremdsoftware, die an dieser Stelle keine Protokolierung hat, Verändeungsdaten liefern, wobei Veränderungen auch 2 Jahre Rückwirkend noch passieren können.

Mein Plan:
"Heute" eine Kopie der betreffenden Daten speichern und diese mit der Kopie der Daten von "Gestern" vergleichen, ob sich bei einem Datensatz etwas geändert hat. Das erstellen der Kopien läuft ca. 5 Minuten, womit ich leben kann, aber mein Vergleich läuft super lange, bei ca. 300 Tsd. Datensätzen.

Aufbau der "Kopie"-Tabellen:
FirmenNummer
Abteilungsnummer
PersonenNummer
Kennzeichen (varchar)
Tag (datum)
Dauer (integer)

Das SQL-Statement für den Vergleich:

SQL-Code:

Select
  Heute.FirmenNummer, Heute.Abteilungsnummer, Heute.PersonenNummer,
  Heute.Kennzeichen, Heute.Tag, Heute.Dauer
From Heute
Left Join Gestern
  On Heute.FirmenNummer=Gestern.FirmenNummer And Heute.Abteilungsnummer=Gestern.Abteilungsnummer
  And Heute.PersonenNummer=Gestern.PersonenNummer And Heute.Kennzeichen=Gestern.Kennzeichen
  And Heute.Tag=Gestern.Tag And Heute.Dauer=Gestern.Dauer
Where
  Gestern.FirmenNummer is NULL

Fragen nun:
Kann man das Beschleunigen durch Indizies? Wie? Worauf?
Oder stellt man die Abfrage anderweitig um, damit Sie schneller wird? Vielleicht irgendwas mit Exists?

[Uwe Raabe]

Wenn die Heute-Tabelle eine eindeutige ID (AUTO INCREMENT) hat, wäre es vermutlich besser, den JOIN auf diese zu legen und in der WHERE Klausel auf Veränderungen der Felder zu prüfen. In der Gestern-Tabelle wäre die ID dann halt ein normales Feld, auf dem dann der Index liegt.

[Jumpy]

Zitat von Uwe Raabe:

Wenn die Heute-Tabelle eine eindeutige ID (AUTO INCREMENT) hat, wäre es vermutlich besser, den JOIN auf diese zu legen und in der WHERE Klausel auf Veränderungen der Felder zu prüfen. In der Gestern-Tabelle wäre die ID dann halt ein normales Feld, auf dem dann der Index liegt.

Leider ist in der Ursprungstabelle, von der Heute und Gestern nur Teilkopien von relevanten Feldern sind, schon kein AutoInc Feld vorhanden. Der Primary Key ist dort ein zusammengesetzter Schlüssel aus Firma,Abteilung,Person und Tag.

[Blup]

Für den reinen Vergleich, ob sich etwas geändert hat, würde ich die Daten eines Objekts komplett in Text überführen.
Das könnte z.B. JSON- oder XML-Format sein, Notfalls geht auch CSV.

Damit speichert man einmal bei der Neuanlage diesen Text.
Wenn eine Änderung auftritt, erneut Speichern mit Datum und Revisionsnummer.

Code:

Tabelle Protokoll (ID Primärschlüssel, * natürlicher Schlüssel = eindeutiger Index)
(PK) ID
* Schlüssel für das Objekt (z.B. Externe ID, Vorgangsnummer oder Personalnummer)
* RevisionNr
* Datum
Text

Was wenn man bei späteren Auswertungen nach spezifischen Unterkriterien einschränken muss?

Code:

Tabelle Kriterien (ID Primärschlüssel, * natürlicher Schlüssel = eindeutiger Index)
(PK) ID
* Name

Es bietet es sich an, schon beim Speichern der Revision die geänderten Kriterien in einer zusätzlichen Tabelle abzulegen.
Hier wird nur die Ämderung an sich benötigt. Alternativ kann man aber auch den neuen Wert zB. als Text ablegen.
Für die erste Revisison eines Objektes würde man alle Kriterien mit Werten ablegen.

Code:

Tabelle ProtokollKriterien (Primärschlüssel über * natürlichen Schlüssel)
(FK) * ID_Protokoll 
(FK) * ID_Kriterium
Text (optional)

Welchen Objekten wurde seit 2020 das Kennzeichen Kz565 zugeordnet?

Code:

select    a.*, c.Text Old_Text, c.Datum Old_Datum
from      Protokoll a
left join ProtokollKriterien b on (b.id_protokoll = a.id) and (b.id_kriterium = (select id from Kriterium where name = 'Kennzeichen'))
left join Protokoll c on (c.Schlüssel = a.Schlüssel) and (c.Revision = a.Revision - 1)
where     (a.datum >= '01.01.2020') and (b.Text = 'Kz565')

Mit C.Text and A.Text kann man den alten und neuen Zustand des Objekts einfach gegenüberstellen.

[Delphi.Narium]

Unter FireBird bringt sowas sehr viel:

SQL-Code:

create index ix_gestern on Gestern (FirmenNummer, Abteilungsnummer, PersonenNummer, Kennzeichen, Tag, Dauer);
create index ix_heute on Heute (FirmenNummer, Abteilungsnummer, PersonenNummer, Kennzeichen, Tag, Dauer);

Wobei:

SQL-Code:

Left Join Gestern 
  On Heute.FirmenNummer = Gestern.FirmenNummer 
...
Where
  Gestern.FirmenNummer is NULL

finde ich befremdlich. Null ist jetzt nicht zwingend ein Wert, dessen Abfrage man über 'nen Index besonders gut beschleunigen kann.
Was genau hast Du vor?

Left Join liefert ja alles von heute, auch wenn es das nicht konkret so gestern gab. Bei denen ist die FirmenNummer dann Null. Vermute mal, dass Du genau diese "Dinger" haben willst.

Würde das eher auf zwei Schritte aufteilen:

SQL-Code:

Select
  FirmenNummer,
  Abteilungsnummer,
  PersonenNummer,
  Kennzeichen,
  Tag,
  Dauer,
  FirmenNummer_2
from (
  Select
    Heute.FirmenNummer, 
    Heute.Abteilungsnummer, 
    Heute.PersonenNummer,
    Heute.Kennzeichen, 
    Heute.Tag, 
    Heute.Dauer,
    Gestern.FirmenNummer as FirmenNummer_2
  From Heute
  Left Join Gestern
    On  Heute.FirmenNummer     = Gestern.FirmenNummer 
    And Heute.Abteilungsnummer = Gestern.Abteilungsnummer
    And Heute.PersonenNummer   = Gestern.PersonenNummer 
    And Heute.Kennzeichen      = Gestern.Kennzeichen
    And Heute.Tag              = Gestern.Tag 
    And Heute.Dauer            = Gestern.Dauer
)
Where
  FirmenNummer_2 is NULL

Meine (zugegeben etwas flache) Indexregel ist: Alles was im Where steht, kommt in den Index.
Hilft meist, aber nicht immer und es kommt ggfls. auf die Datenbank an.

Was immer hilft: Ausführungsplan anschauen. Steht da was von Fulltablescan, ist's eher übel. Je mehr aus der Where-Bedingung dort aufgeführt wird, um so größer ist die Wahrscheinlichkeit, dass 'ne Abfrage schnell wird.

Natürlich kann man jetzt in 'ner Datenbank nicht grundsätzlich für jede potentiell mögliche Wherebedingung nach dieser Regel 'nen Index erstellen. Wenn Du für den Vergleich aber eine eigenen Datenbank zu Verfügung hast, so spricht nix dagegen, es mit 'nem Index nach dieser Regel zu versuchen. Aber: Je komplexer ein Index, um so langsamer wird das Befüllen, da der Index dabei ja mit aufgebaut werden muss.
Es könnte sich von daher als sinnvoll erweisen, zuerst den Index zu löschen, dann (wenn erforderlich) die Tabelle leeren, dann die Tabelle neu befüllen, dann den Index neu erstellen.
Kommt auf die Datenmenge, die Datenbank, die darunterliegende Hardware an. Manchmal hilft da eher "Probieren geht über Studieren" als sinnvolles, lehrbuchmäßiges Vorgehen

[Jumpy]

Zitat von Delphi.Narium:

Left Join liefert ja alles von heute, auch wenn es das nicht konkret so gestern gab. Bei denen ist die FirmenNummer dann Null. Vermute mal, dass Du genau diese "Dinger" haben willst.

Ja, genau das war mein Gedanke dahinter. Das Problem scheint aber schon darin zu bestehen, dass aufgrund der vielen Datensätze ca. 300 Tsd. der Join schon so lange dauert.

Ich hab schon überlegt ob ich alle Spalten in einer zusammenführe via Concat und da dann einen Index auf die Spalte lege. Das Erzeugen dieser Tabellenkopie würde dann zwar länger dauern, aber vielleicht wäre der Join aber dann schneller. Muss das morgen mal probieren. Dann geht vielleicht auch:

SQL-Code:

Select Heute.SpalteMitAllemDrin
From Heute
Where Not Exists (Select * From Gestern Where Heute.SpalteMitAllemDrin=SpalteMitAllemDrin)

[joachimd]

Da sollte aber ein OUTER JOIN eigentlich schneller sein:

Code:

Select h.* 
From Heute h
LEFT OUTER JOIN Gestern g on h.SpalteMitAllemDrin=g.SpalteMitAllemDrin
WHERE g.spalte1 IS NULL

[Delphi.Narium]

Vor 'nem Concat über alle Spalten würd' ich zuerst mal 'nen Index über alle Spalten versuchen.

Not Exists ist ohne einen passenden Index vermutlich auch nicht gerade schnell.

Was noch gehen könnte, weiß aber nicht ob MariaDB sowas unterstütz, unter Oracle müsste es in etwa so aussehen:

SQL-Code:

select FirmenNummer, Abteilungsnummer, PersonenNummer, Kennzeichen, Tag, Dauer from Heute
minus
select FirmenNummer, Abteilungsnummer, PersonenNummer, Kennzeichen, Tag, Dauer from Gestern

Laut dieser Seite How to use Minus in MySQL or MairaDB geht es wohl nicht und da wird dann der bereits von Dir genutzte Weg als Alternative vorgeschlagen.

Das Not Exists würd' ich etwas abändern:

SQL-Code:

Select Heute.*
From Heute
Where Not Exists (Select 1 From Gestern Where Heute.SpalteMitAllemDrin = Gestern.SpalteMitAllemDrin)

Aber: Das könnte eventuell auch anstelle des Joins machbar sein, in etwa sowas:

SQL-Code:

Select
  Heute.FirmenNummer,
  Heute.Abteilungsnummer,
  Heute.PersonenNummer,
  Heute.Kennzeichen,
  Heute.Tag,
  Heute.Dauer
From Heute
Where Not Exists
  (Select 1 From Gestern  
    Where Heute.FirmenNummer = Gestern.FirmenNummer
    And Heute.Abteilungsnummer = Gestern.Abteilungsnummer
    And Heute.PersonenNummer = Gestern.PersonenNummer
    And Heute.Kennzeichen = Gestern.Kennzeichen
    And Heute.Tag = Gestern.Tag
    And Heute.Dauer = Gestern.Dauer
  )

Aber ohne Index in der Art

SQL-Code:

create index ix_gestern on Gestern (FirmenNummer, Abteilungsnummer, PersonenNummer, Kennzeichen, Tag, Dauer);
create index ix_heute on Heute (FirmenNummer, Abteilungsnummer, PersonenNummer, Kennzeichen, Tag, Dauer);

, wird das vermutlich nicht schnell werden.

[Jumpy]

So ich hab es mit der ConcatSpalte versucht. Das erzeugen / kopieren der Tabellen in der anderen Datenbank dauert damin ca. 15 Sek. länger, was bei Gesamtdauer von ca. 7 minuten nicht weiter aiffällt. Mit der langen dauer dafür muss ich leider erst mal Leben. Hier muss ich später mal schauen, ob ich das mit einem Bulk-Insert nicht beschleunigen kann.

Die sinngemäße Abfrage

SQL-Code:

Select Heute.*
From Heute
Where Not Exists (Select 1 From Gestern Where Heute.SpalteMitAllemDrin = Gestern.SpalteMitAllemDrin)

dauert damit ca. 13 Sekunden, statt der über 2 Stunden mit dem Join. Es macht aber keinen unterschied ob 1 oder *, weil Mysql da eh keine Ergebnismenge zusammenbaut, es wird (so hab ich es verstanden) sofort abgebrochen, wenn ein Match gefunden ist.

Abschließend hab ich auf die ConcatSpalte in beiden Tabellen noch einen Index gesetzt und bin bei 1.4 Sekunden, womit ich schon mehr als zufrieden bin.