AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Optimierung einer SQL-Abfrage

Optimierung einer SQL-Abfrage

Ein Thema von barnti · begonnen am 9. Okt 2008 · letzter Beitrag vom 10. Okt 2008
Antwort Antwort
Seite 2 von 4     12 34   
nahpets
(Gast)

n/a Beiträge
 
#11

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 14:38
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.
  Mit Zitat antworten Zitat
barnti

Registriert seit: 15. Aug 2003
Ort: Mal hier mal da...
689 Beiträge
 
Delphi 7 Enterprise
 
#12

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 14:45
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.
Gruß,

Barnti
  Mit Zitat antworten Zitat
nahpets
(Gast)

n/a Beiträge
 
#13

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 15:15
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.
  Mit Zitat antworten Zitat
Elvis

Registriert seit: 25. Nov 2005
Ort: München
1.909 Beiträge
 
Delphi 2010 Professional
 
#14

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 15:18
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
         ,...
Robert Giesecke
I’m a great believer in “Occam’s Razor,” the principle which says:
“If you say something complicated, I’ll slit your throat.”
  Mit Zitat antworten Zitat
barnti

Registriert seit: 15. Aug 2003
Ort: Mal hier mal da...
689 Beiträge
 
Delphi 7 Enterprise
 
#15

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 15:22
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.
Gruß,

Barnti
  Mit Zitat antworten Zitat
nahpets
(Gast)

n/a Beiträge
 
#16

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 15:33
Ü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.
  Mit Zitat antworten Zitat
barnti

Registriert seit: 15. Aug 2003
Ort: Mal hier mal da...
689 Beiträge
 
Delphi 7 Enterprise
 
#17

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 15:38
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.
Gruß,

Barnti
  Mit Zitat antworten Zitat
nahpets
(Gast)

n/a Beiträge
 
#18

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 15:51
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.
  Mit Zitat antworten Zitat
barnti

Registriert seit: 15. Aug 2003
Ort: Mal hier mal da...
689 Beiträge
 
Delphi 7 Enterprise
 
#19

Re: Optimierung einer SQL-Abfrage

  Alt 9. Okt 2008, 17:04
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!
Gruß,

Barnti
  Mit Zitat antworten Zitat
nahpets
(Gast)

n/a Beiträge
 
#20

Re: Optimierung einer SQL-Abfrage

  Alt 10. Okt 2008, 09:41
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.
  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 03:05 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