Optimierung einer SQL-Abfrage

[nahpets]

Hallo,

Zitat von barnti:

Hi Stephan,

ich habe Deinen Vorschlag mal beherzigt und siehe da - die Bearbeitungszeit bei 10.000 Datensätzen verkürzt sich von 8 auf 3 Minuten! Ich hoffe ich kann das noch weiter drücken! Danke!

Vorschläge sind weiterhin willkommen!

ich hoffe doch Stark, dass das noch schneller wird, habe gestern Abend noch festgestellt, dass in meinem Statement ein gravierender Fehler ist.
Es soll nicht auf Teilstrings überprüft werden, sondern auf Übereinstimmung eines verkürzten Strings. Damit sind die SubStrings in der Wherebedingung hinfällig. Dies führt zu folgender Vereinfachung (die unions können entfallen) des Statements:

SQL-Code:

select length(Nummer) As Genauigkeit, Nummer from tabelle 
where nummer in ('08154711059','0815471105´','081547110','08154711','0815471','081547','08154','0815','081','08','0');
order by 1 Desc

Auch hier gilt: Der erste Satz ist der von uns gesuchte.
Wir reduzieren hier also maximal 11 * 5 Mio = 55 Mio Abfragen auf 5 Mio Abfragen.
Das läßt sich verallgemeinern für alle Nummern:

SQL-Code:

Select 
  Length(Nummer) as Genauigkeit,
  a.Nummer, 
  b.Nummer
from Tabelle50Mio a, 
     Tabelle5Mio b
where a.Nummer in (
  b.Nummer, 
  Substr(b.Nummer,1,10),
  Substr(b.Nummer,1,9),
  Substr(b.Nummer,1,8),
  Substr(b.Nummer,1,7),
  Substr(b.Nummer,1,6),
  Substr(b.Nummer,1,5),
  Substr(b.Nummer,1,4),
  Substr(b.Nummer,1,3),
  Substr(b.Nummer,1,2),
  Substr(b.Nummer,1,1)
)
order by 
  b.Nummer asc,
  Genauigkeit desc

Hier haben wir wieder den Nachteil, dass wir in der Wherebedingung mit Substrings arbeiten, da kann die Datenbank dann keinen Index benutzen aber wir benötigen für die gesamte Abfragerei nur noch ein SQL und viel Temp-Tablespace.
Die Ergebnismenge muss TopDown durcharbeitet werden und parallel zur Tabelle5Mio die Ergebnisse per Gruppenwechsel abgerufen werden (ist halt Programmieraufwand). Die Ergebnismenge kann (theoretisch) bis 550 Mio Datensätze umfassen.
Es stehen also 55 Mio Abfragen mit kleinen Ergebnismengen einer Abfrage mit einer (sehr) großen Ergebnismenge gegenüber.

Da wir es hier mit Oracle zu tuen haben, läßt sich das SQL noch "vereinfachen":

SQL-Code:

Select 
  Length(Nummer) as Genauigkeit,
  a.Nummer, 
  b.Nummer
from Tabelle50Mio a, 
  (select /* Achtung, gewöhnungsbedürftiges Konstrukt */
     Nummer, 
     Substr(Nummer,1,10) As N10,
     Substr(Nummer,1,9) As N9,
     Substr(Nummer,1,8) As N8,
     Substr(Nummer,1,7) As N7,
     Substr(Nummer,1,6) As N6,
     Substr(Nummer,1,5) As N5,
     Substr(Nummer,1,4) As N4,
     Substr(Nummer,1,3) As N3,
     Substr(Nummer,1,2) As N2,
     Substr(Nummer,1,1) As N1
   from Tabelle5Mio
  ) b
where a.Nummer in (b.Nummer, b.N10, b.N9, b.N8, b.N7, b.N6, b.N5, b.N4, b.N3, b.N2, b.N1)
)
order by b.nummer asc,
Genauigkeit desc

Hierdurch müssen für alle Nummern aus der Tabelle5Mio nur einmal die Substrings gebildet werden. Die Abfrage gegen die 50Mio Sätze erfolgt gegen die Ergebnismenge mit den Substrings der Tabelle5Mio.

Das sollte jetzt noch schneller gehen, zum Preis einer Veränderung am Datenmodell:
Das was wir hier per SQL als Substrings produzieren, direkt in die Tabelle5Mio als Redundanz mit einbauen, also der Tabelle5Mio die Spalten N10 bis N1 hinzufügen und per Insert-Update-Trigger füllen. Sind die Felder in der Tabelle enthalten, dann können sie mit einem Index versehen werden.
Das SQL könnte dann so aussehen:

SQL-Code:

Select 
  Length(Nummer) as Genauigkeit,
  a.Nummer, 
  b.Nummer
from Tabelle50Mio a, 
     Tabelle5Mio b
where a.Nummer in (b.Nummer, b.N10, b.N9, b.N8, b.N7, b.N6, b.N5, b.N4, b.N3, b.N2, b.N1)
order by 
  b.Nummer asc,
  Genauigkeit desc

Wenn das funktioniert, sollte es kaum noch schneller gehen.

Viel Vergnügen beim Testen, die Statements sind alle so hingeschrieben und nicht auf syntaktische Korrektheit überprüft.
Warte gespannt auf das Ergebnis.