AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Index geschickt setzen für Tabellenvergleich

Index geschickt setzen für Tabellenvergleich

Ein Thema von Jumpy · begonnen am 24. Mär 2021 · letzter Beitrag vom 25. Mär 2021
Antwort Antwort
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.733 Beiträge
 
Delphi 6 Enterprise
 
#1

Index geschickt setzen für Tabellenvergleich

  Alt 24. Mär 2021, 12:24
Datenbank: MariaDB • Version: 10.? • Zugriff über: JDBC
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?
Ralph
  Mit Zitat antworten Zitat
Benutzerbild von Uwe Raabe
Uwe Raabe
Online

Registriert seit: 20. Jan 2006
Ort: Lübbecke
10.934 Beiträge
 
Delphi 12 Athens
 
#2

AW: Index geschickt setzen für Tabellenvergleich

  Alt 24. Mär 2021, 13:00
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.
Uwe Raabe
Certified Delphi Master Developer
Embarcadero MVP
Blog: The Art of Delphi Programming
  Mit Zitat antworten Zitat
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.733 Beiträge
 
Delphi 6 Enterprise
 
#3

AW: Index geschickt setzen für Tabellenvergleich

  Alt 24. Mär 2021, 13:16
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.
Ralph
  Mit Zitat antworten Zitat
Blup

Registriert seit: 7. Aug 2008
Ort: Brandenburg
1.429 Beiträge
 
Delphi 10.4 Sydney
 
#4

AW: Index geschickt setzen für Tabellenvergleich

  Alt 24. Mär 2021, 13:37
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.

Geändert von Blup (24. Mär 2021 um 13:52 Uhr)
  Mit Zitat antworten Zitat
Delphi.Narium

Registriert seit: 27. Nov 2017
2.400 Beiträge
 
Delphi 7 Professional
 
#5

AW: Index geschickt setzen für Tabellenvergleich

  Alt 24. Mär 2021, 17:10
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

Geändert von Delphi.Narium (24. Mär 2021 um 17:27 Uhr) Grund: Fehler behoben
  Mit Zitat antworten Zitat
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.733 Beiträge
 
Delphi 6 Enterprise
 
#6

AW: Index geschickt setzen für Tabellenvergleich

  Alt 25. Mär 2021, 18:20
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)
Ralph

Geändert von Jumpy (25. Mär 2021 um 18:25 Uhr)
  Mit Zitat antworten Zitat
Benutzerbild von joachimd
joachimd

Registriert seit: 17. Feb 2005
Ort: Weitingen
672 Beiträge
 
Delphi 10.4 Sydney
 
#7

AW: Index geschickt setzen für Tabellenvergleich

  Alt 25. Mär 2021, 18:47
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
Joachim Dürr
Joachim Dürr Softwareengineering
http://www.jd-engineering.de
  Mit Zitat antworten Zitat
Delphi.Narium

Registriert seit: 27. Nov 2017
2.400 Beiträge
 
Delphi 7 Professional
 
#8

AW: Index geschickt setzen für Tabellenvergleich

  Alt 25. Mär 2021, 18:49
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.
  Mit Zitat antworten Zitat
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.733 Beiträge
 
Delphi 6 Enterprise
 
#9

AW: Index geschickt setzen für Tabellenvergleich

  Alt 25. Mär 2021, 20:05
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.
Ralph
  Mit Zitat antworten Zitat
Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 23:59 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz