Optimierung einer SQL-Abfrage

[nahpets]

Hallo,

nur mal so ein Vorschlag, der bei großen Datenmengen vermutlich suboptimal oder auch eher ein Tempokiller ist:

SQL-Code:

select Nummer, max(Genauigkeit) from
(
select Nummer, 11 as Genauigkeit from tabelle_a where substr(Nummer,1,11) = substr('08154711059',1,11)
union
select Nummer, 10 as Genauigkeit from tabelle_a where substr(Nummer,1,10) = substr('08154711059',1,10)
union
select Nummer, 09 as Genauigkeit from tabelle_a where substr(Nummer,1,09) = substr('08154711059',1,09)
union
select Nummer, 08 as Genauigkeit from tabelle_a where substr(Nummer,1,08) = substr('08154711059',1,08)
union
select Nummer, 07 as Genauigkeit from tabelle_a where substr(Nummer,1,07) = substr('08154711059',1,07)
union
select Nummer, 06 as Genauigkeit from tabelle_a where substr(Nummer,1,06) = substr('08154711059',1,06)
union
select Nummer, 05 as Genauigkeit from tabelle_a where substr(Nummer,1,05) = substr('08154711059',1,05)
union
select Nummer, 04 as Genauigkeit from tabelle_a where substr(Nummer,1,04) = substr('08154711059',1,04)
union
select Nummer, 03 as Genauigkeit from tabelle_a where substr(Nummer,1,03) = substr('08154711059',1,03)
union
select Nummer, 02 as Genauigkeit from tabelle_a where substr(Nummer,1,02) = substr('08154711059',1,02)
union
select Nummer, 01 as Genauigkeit from tabelle_a where substr(Nummer,1,01) = substr('08154711059',1,01)
) group by Nummer
order by Genauigkeit desc, Nummer

Es wird hier für jede Länge der Substrings geprüft, ob es entsprechende Sätze gibt. Je nach "Genauigkeit" (sprich Anzahl der übereinstimmenden Zeichen) wird eine Wertung vorgenommen. 11 Zeichen Genauigkeit = 11, 10 Zeichen Genauigkeit = 10 ... bis 1.
Das Ergebnis wird absteigend nach Genauigkeit sortiert und damit ist der Satz mit der höchsten Genauigkeit an erster Stelle zu finden. Davon ausgehend, dass jede der 11-stelligen Nummern nur einmal in Tabelle A vorkommt, ist der erste Satz der Ergebnismenge Dein Kandidat. Ist die Ergebnismenge leer, dann gibt es weder den gesuchten Wert noch einen Teilstring mit zumindest einem Zeichen. Sollte die 11-stellige Nummer in Tabelle A mehrfach vorkommen, könntest Du die Einzelnen, per Union verbundenen, Select-Statements noch mit einem Distinct versehen.
Aus Delphi heraus dürfte ein derartiges SQL mit einem Parameter für die 11-stellige Nummer zu befüllen sein oder innerhalb eines PL-SQL-Packages einen entsprechender Cursor definierbar sein.

[barnti]

Hallo Stephan,

Zitat von nahpets:

Hallo,

nur mal so ein Vorschlag, der bei großen Datenmengen vermutlich suboptimal oder auch eher ein Tempokiller ist:

Da triffst Du auch schon des Pudels Kern: Ich muss das Ganze optimieren. Im Moment schaffe ich so 10.000 Datensätze in 8 Minuten. Das ist viel zu langsam. Da bei Deinem Vorschlag auch noch eine Funktion in der Where-Klausel auftaucht, wird das von der Geschwindigkeit eher langsamer sein.

[nahpets]

Zitat von barnti:

Da triffst Du auch schon des Pudels Kern: Ich muss das Ganze optimieren. Im Moment schaffe ich so 10.000 Datensätze in 8 Minuten. Das ist viel zu langsam. Da bei Deinem Vorschlag auch noch eine Funktion in der Where-Klausel auftaucht, wird das von der Geschwindigkeit eher langsamer sein.

Das muss nicht sein, habe bei Orcale schon häufiger die Erfahrung gemacht, dass die Datenbank solche Sachen besser macht, als man selbst es mittels eigener Programme in C++ oder per PL-SQL-Routinen machen kann.
Probier' es aus, wenn der hier bei 10.000 Sätzen noch acht Minuten braucht, dann stimmt eventuell auch mit den Datenbankeinstellungen etwas nicht. Oder meinst Du mit 10.000 Datensätzen, dass Du für 10.000 unterschiedlicher Nummern jeweils über den ganzen Datenbestand rennen musst.
Schau Dir mal den Ausführungsplan an, was für ein Index kann für die Suche benutzt werden, sind die Statistiken aktuell?
Wie hast Du das bisher gemacht, bist Du ggfls. 11 mal über den Datenbestand gelaufen, um zu schauen, ob es da das Gesuchte gibt? Das würde bedeuten, dass Du dort bei 10.000 abzufragenden Nummern * 11 Möglichkeiten im ungünstigsten Fall 110.000 Abfragen abgesetzt hast, im Schnitt, bei einer gleichmäßigen Verteilung aber wohl noch 55.000, mit der von mir vorgeschlagenen Variante wären es aber "nur" (aber immer konstant) 10.000 Abfragen.

Habe das Ganze hier mal gegen SQL-Server laufen lassen (hab' kein Oracle zur Verfügung), dass dauert bei einer Tabelle A mit ca. 150.000 Sätzen deutlich weniger als eine Sekunde für eine Abfrage, das mal 10.000 käme dann aber doch in den Stundenbereich. Da bist Du mit 8 Minuten vielleicht doch nicht so schlecht.

[Elvis]

Da gibt es sicherlich 1.000 Möglichkeit um das in PL/SQL zu lösen.
Eine Lösung wäre es, die verkürzten Nummern in die Ergebnissmenge zu bekommen:

SQL-Code:

create or replace package BarntisNummernDings is
  type TAproxNumber is record(
     RemovedDigits Integer
    ,PartialValue  Integer);

  type TAproxNumberList is table of TAproxNumber;

  function GetAproxNumbers(input in Integer) return TAproxNumberList
    pipelined;
end BarntisNummernDings;
/
create or replace package body BarntisNummernDings is

  function GetAproxNumbers(input in Integer) return TAproxNumberList
    pipelined is
    Item          TAproxNumber;
    inputAsString VarChar(50);
  begin
    if input is null then
      return;
    end if;
  
    Item.PartialValue  := input;
    Item.RemovedDigits := 0;
    pipe row(Item);
  
    if input < 10 then
      return;
    end if;
  
    inputAsString := input;
    if Length(inputAsString) > 1 then
      for i in 1 .. Length(inputAsString) - 1 loop
        Item.RemovedDigits := Item.RemovedDigits + 1;
      
        Item.PartialValue := SubStr(inputAsString, 1, Length(inputAsString) - i);
        pipe row(Item);
      
        Item.PartialValue := SubStr(inputAsString, i + 1);
        pipe row(Item);
      end loop;
    end if;
    return;
  end;
end BarntisNummernDings;
/

SQL-Code:

SELECT *
FROM   table(BarntisNummernDings.GetAproxNumbers(12345))

Code:

0   12345
1   1234
1   2345
2   123
2   345
3   12
3   45
4   1
4   5

SQL-Code:

SELECT aproxIn.RemovedDigits
      ,aproxT.RemovedDigits
      ,t.*
FROM   table(BarntisNummernDings.GetAproxNumbers(12345)) aproxIn
      ,deineTabelle t
      ,table(BarntisNummernDings.GetAproxNumbers(t.DeinWert )) aproxT
WHERE  aproxIn.PartialValue = aproxT.PartialValue
ORDER  BY aproxIn.RemovedDigits
         ,aproxT.RemovedDigits
         ,...

[barnti]

Hi,

ich stelle grad ein paar Möglichkeiten gegenüber. Ich werde alle mal testen auch Deinen Vorschlag. Als nächstes ist mal BULK BIND FORALL dran. Evtl schreibe ich die Werte auch in eine temporäre Tabelle und mache einen join...

Mal schauen, was das alles bringt.

[nahpets]

Über was für Datenmengen unterhalten wir und denn hier überhaupt?
10.000 Datensätze oder 10.000 Abfragen auf 10.000.000 Datensätze? Damit wir mal 'ne Vorstellung bekommen, um was für Optimierungen es sich handelt.

[barnti]

Hi there,

Zitat von nahpets:

Über was für Datenmengen unterhalten wir und denn hier überhaupt?
10.000 Datensätze oder 10.000 Abfragen auf 10.000.000 Datensätze? Damit wir mal 'ne Vorstellung bekommen, um was für Optimierungen es sich handelt.

es müssen (im Moment Tendenz gegen 50 Mio) 5 Mio Datensätze gegen 50 Mio Datensätze geprüft werden.

[nahpets]

Zitat von barnti:

es müssen (im Moment Tendenz gegen 50 Mio) 5 Mio Datensätze gegen 50 Mio Datensätze geprüft werden.

Das heißt wir reden hier von einer Laufzeit von 2,5 Tagen plus etwas.
Okay, dass ist dann etwas, wo man nicht zwischen Tür und Angel mal eben ein schnelleres Statement hinschreibt.

[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!

[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.