Optimierung einer SQL-Abfrage

[barnti]

Hallo Gemeinde,

ich habe heute mal wieder eine Frage zum Thema Datenbanken. Folgende Problemstellung:

ich habe eine 11-stellige Nummer, die ich mit Einträgen aus einer Tabelle vergleichen möchte. Dabei möchte ich nicht nur die aktuelle Nummer gegen die Tabelle testen sondern auch verkürzte Bestandteile.

z.B:

1. Test: ist die Nummer 08154711059 in Tabelle A vorhanden? Wenn ja, Suche abbrechen, wenn nein

2. Test: ist die Nummer 0815471105 in Tabelle A vorhanden? Wenn ja, Suche abbrechen, wenn nein suche nächste verkürzte Nummer(bis Länge(Nummer) <= 4)


Bisher iteriere ich in einer Schleife über die zu suchenden Nummer und übergebe diese einem Cursor, der die Abfrage ausführt. Ich hätte nun gern gewusst, wie ich das optimieren kann?

Hat jemand eine Idee?

[Bernhard Geyer]

Wie wäre es mit IN?

SQL-Code:

SELECT * FROM <Tabelle> where Suchfeld in [Wert1, Wert2, ...];

[DeddyH]

Dann erhält man aber u.U. mehrere Datensätze, sonst könnte man auch gleich auf die Minimallänge kürzen und mit like suchen.

[edit] Um 1337 gepostet, ob das was zu sagen hat? :mrgreen: [/edit]

[barnti]

Hallo Bernhard,

Danke für die schnelle Antwort!

Daran hatte ich auch schon gedacht. Aber wie übergebe ich dann die Werte an das Select-Statement? Es kann auch vorkommen, dass eine Nummer mal kürzer ist, so dass ich da keine feste Variablenzahl nehmen kann. Kann ich die Werte irgendwie in ein Array schreiben und dann mit WHERE IN vergleichen? Kannst Du das evtl. etwas genauer beschreiben?

[barnti]

Hi,

Zitat:

Zitat von DeddyH

Dann erhält man aber u.U. mehrere Datensätze, sonst könnte man auch gleich auf die Minimallänge kürzen und mit like suchen.

Like Vergleich geht leider nicht. Ich will ja nicht jede beliebige Nummer finden, sondern immer die aktuell zu suchende.

[DeddyH]

Das Problem hast Du bei der Suche mit IN aber auch, das war ja meine Aussage oben.

[barnti]

Hallo,

Zitat:

Zitat von DeddyH

Das Problem hast Du bei der Suche mit IN aber auch, das war ja meine Aussage oben.

Ich denke, das ist so nicht richtig. Wenn ich einzelne Werte mit WHERE IN vergleiche bekomme ich auch nur diskrete Treffer Für diese Werte. Will heißen:

Wert1: 08154711
Wert2: 0815471
Wert3: 081547

Dann bekomme ich nur Treffer, wenn Wert1 oder Wert2 oder Wert3 in der TAbelle enthalten ist (kein exor wohlgemerkt).

[DeddyH]

Sry, dann habe ich die Aufgabenstellung falsch verstanden.

[edit] Soll der SQL-String in Delphi generiert werden? [/edit]

[DeddyH]

Hab mal was gebastelt:

Delphi-Quellcode:

function SeekSQL(const sNumber: string; MinLength: integer = 1): string;
var i: integer;
    sl: TStringList;
begin
  Result := '';
  sl := TStringList.Create;
  try
    for i := Length(sNumber) downto MinLength do
      sl.Add(QuotedStr(Copy(sNumber,1,i)));
    Result := sl.CommaText;
  finally
    sl.Free;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
const sSQL = 'SELECT * FROM Tabelle WHERE Suchfeld IN (%s)';
begin
  ShowMessage(Format(sSQL,[SeekSQL('08154711059',4)]));
end;

[barnti]

Hi-ho,

ja so in der Art wäre fein. Ich habe nur noch keine Möglichkeit gefunden, das in Pl/Sql zu implementieren. Vararray geht als Typ im Statement nicht(Cursor).

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

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:

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:

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:

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:

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.

[barnti]

Moin Stephan,

puh, das ist ne Menge neuer Ideen! Ich halte das für sehr vielversprechend. Zumal ich gestern immer noch bei 100.000 Records/h lag. Das ist wirklich noch zu langsam.

Ich mache mich gleich mal an die Arbeit und schaue mir Deine Ausführungen genauer an. Wird eine Weile dauern bis ich berichten kann.
Vielen Dank schon einmal für Deine Mühe!

[nahpets]

Hallo,

Zitat:

Zitat von barnti

Moin Stephan,

puh, das ist ne Menge neuer Ideen! Ich halte das für sehr vielversprechend. Zumal ich gestern immer noch bei 100.000 Records/h lag. Das ist wirklich noch zu langsam.

Ich mache mich gleich mal an die Arbeit und schaue mir Deine Ausführungen genauer an. Wird eine Weile dauern bis ich berichten kann.
Vielen Dank schon einmal für Deine Mühe!

ich gehe davon aus, dass damit Dein heutiger Tage "gerettet" ist :wink:
D. h.: Du bist jetzt noch bei knapp über 2 Tagen Laufzeit, dass ist noch nicht wirklich viel besser.
Indiskrete Frage, wie oft muss der Job gemacht werden?
Wöchentlich, monatlich, hoffentlich nicht täglich :wink:

[Elvis]

Wenn du soviele Records hast, dann ist das allerallerallerletzte, was du jemals tun willst, das Zeug zu sortieren... :shock: Der muss doch sonst ALLES ausführen und in ene temp table werfen bevor er dir auch nur den ersten Record geben könnte.
Du willst die Abfrage so bauen, dass er mit sowenig wie möglich Speicher durch die Daten "scrollen" kann.
Eine pipeline Funktion wie die dich in in dem einfachen Beispiel oben gab, sollte das möglich machen.
Ob du dieDaten dann in Ora lassen willst, oder durch eine externe App ziehen und speichern willst, musst du wissen.
ABER KEINE SORTIERUNG IN DER DB!

[nahpets]

Zitat:

Zitat von Elvis

Wenn du soviele Records hast, dann ist das allerallerallerletzte, was du jemals tun willst, das Zeug zu sortieren... :shock: Der muss doch sonst ALLES ausführen und in ene temp table werfen bevor er dir auch nur den ersten Record geben könnte.
Du willst die Abfrage so bauen, dass er mit sowenig wie möglich Speicher durch die Daten "scrollen" kann.
Eine pipeline Funktion wie die dich in in dem einfachen Beispiel oben gab, sollte das möglich machen.
Ob du dieDaten dann in Ora lassen willst, oder durch eine externe App ziehen und speichern willst, musst du wissen.
ABER KEINE SORTIERUNG IN DER DB!

Ja, die DB muss alles sortieren, wenn nicht, muss Du aus der Applikation heraus Dir sonst den besten von 11 möglichen Treffern je Datensatz holen und das machst Du nicht schneller als die Datenbank.
Habe ähnlichen "Scheiß" schon mit deutlich größeren Datenmengen machen müssen und immer wieder feststellen dürfen, dass die Datenbank (vorausgesetzt Du hast einen vernünftigen Server dafür) das schneller kann als Du mit C++ oder sonstwas.
Wir haben früher auch mal 170 Mio. Sätze ohne Sortierung aus der Datenbank geholt, dass dann in C++ in 'ne Map gepackt und dort sortiert, um mit den Ergebnissen weiterzuarbeiten. Die Datenbank war da immer schneller.
Wesentlich ist, bei der SQL-Abfrage von vorneherein die Ergebnismenge so gering wie möglich zu halten, auch wenn die SQL's durch Schachtelung annähernd unleserlich werden. Wir hatten da mal 'nen Job mit über 170 Mio. Sätzen in der größten Tabelle, gejoint mit ca. 'nem halben Dutzend weiterer Tabellen in der Größenordnung von 15.000 bis 60 Mio. Sätzen, der eben nicht von der Datenbank sortiert wurde und wir nach 2,5 Tagen noch kein Ergebnis hatten. Nachdem ich gesagt hatte, Schammdrüber, die Datenbank sortiert das, die kann das besser als wir, hatten wir die ersten Ergebnisse nach 25 Minuten und konnten die Ergebnismenge Topdown in die entsprechenden Ausgabedateien schreiben.
Dieses Pauschale "Die Datenbank sortiert nicht" oder "Alles macht die Datenbank" ist von beiden Seiten her kontraproduktiv. Man muss bei jedem Job dadurch, auszuarbeiten, was das Beste ist. Ich halte es für falsch, für irgendwelche Aufgaben bestimmte Vorgehensweisen grundsätzlich für Tabu zu erklären. Ob die von mir beschriebenen Möglichkeiten auch für SQL-Server geeignet sind, weiß ich nicht, für Access und dBase ist es mit Sicherheit nichts und für Interbase, Firebird, DB2, Postgres und wie sie alle heißen, habe ich keine Erfahrungswerte. Für Oracle ist meine Erfahrung, lass es die Datenbank machen, sie kann es besser als wir mit unseren Programmierkenntnissen und Werkzeugen.

[barnti]

Hi ihr zwei,

ich muss den Job einmal die Woche oder einmal im Monat laufen lassen. Je nachdem wie das mit der Performance noch wird. Zum Thema Sortierung: Ich brauche eigentlich keine sortierte Ausgabe. Mir reicht es zu wissen, ob es einen Treffer gab.

[Elvis]

Zitat:

Zitat von nahpets

Dieses Pauschale "Die Datenbank sortiert nicht" oder "Alles macht die Datenbank" ist von beiden Seiten her kontraproduktiv. Man muss bei jedem Job dadurch, auszuarbeiten, was das Beste ist. Ich halte es für falsch, für irgendwelche Aufgaben bestimmte Vorgehensweisen grundsätzlich für Tabu zu erklären.

Jupp, da wirst du für den allgemeinen Fall keine Widerworte hören. :-)

Für Barntis MENGE an unscharf verknüpften Records (50*10^6 * 50*10^6? :shock: ) wäre es aber besser nicht in der DB zu sortieren sondern in einem client.
Und zwar wenn er die pipelines zu emittieren der "Substring-Integers" nutzt.
Denn wie man es von pipeline function gewohnt ist, injizieren sie ihre Ergebnisse immer schön hintereinander PRO Ursprungsrecord. Du kannst im client also jeweils aus 10 bis max vllt 100 Werten den besten Match hernehmen. Und das über Queue und Threadpool sogar parallel zu dem Wasserfall an Daten, die da von Ora hereinschneien. Ein weiterer Vortei ist, dass der Ora Server damit nicht plattgemacht wird. Andere Sessions würden davon nicht viel merken.

[nahpets]

Hallo,

ausgehend von meinen Vorschlägen habe ich keine Idee, wie man an den besten von 11 Treffern kommen soll, wenn man das Ergebnis nicht sortiert, hier müsste man ja dann jeweils die gesamte Ergebnismenge durchsuchen, das kann es noch nicht sein.

Würde dann sowas reichen?

SQL-Code:

Select 
  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)

Dann müsste hier ja nurnoch in der Ergebnismenge nach einem Treffer gesucht werden, auch wenn es ggfls. bis zu 11 geben könnte.

SQL-Code:

select * from tabelle5Mio
where exists 
(
  Select 
    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) 
)

Das sollte dann mit der redundanten Ablage von N1 bis N10 und entsprechenden Indexen (oder Indizes, was ist da eigentlich richtig?) razfaz gehen.
Das innere SQL in 'ne temporäre Tabelle, die mit Index auf Nummer und aktuellen Statistiken, spart dann auch noch das ggfls. für exists erforderliche sortieren.
Na, da stellt sich ja dann demnächst die Frage: Was hat länger gedauert, unsere Diskussion oder das Programm zum auswerten der Daten über den vollen Datenbestand :wink: :wink: :wink:

[barnti]

Halli-hallo,

ja, das sieht schon sehr einfach aus! Ich brauche definitiv nur die Aussage, ob eine Nummer in ganzer Länge oder verkürzt in der Vergleichstabelle vorliegt. Das Bearbeiten des Ergebnis ist dann nicht mehr der große Aufwand.

Ich habe die Abfrage weiter optimiert und werde auch weiter optimieren. Ich scheue mich noch ein wenig alles in einer Abfrage zu machen. Bevor ich das versuche, werde ich die Struktur meiner Tabelle, wie vorgeschlagen anpassen und einen Index auf die redundanten Einträge setzen.

Ja, die Diskussion ist schon etwas ausführlich geraten, ich bin aber sehr dankbar von so viel Erfahrung profitieren zu können. Ich schätze eure Hilfestellung sehr!

[Elvis]

Zitat:

Zitat von nahpets

ausgehend von meinen Vorschlägen habe ich keine Idee, wie man an den besten von 11 Treffern kommen soll, wenn man das Ergebnis nicht sortiert, hier müsste man ja dann jeweils die gesamte Ergebnismenge durchsuchen, das kann es noch nicht sein.

Richtig, würde nicht gehen.
Aber wenn das Zelegen der Zahlen von beiden Records in einer Pipeline Function erfolgt, ann werden eben deren ergbnisse in die Ergebnismenge an genau der Stelle eingefügt.
Manverknüpft dann wo jeweils 2 bruchstücke gleich sind.
Du hast dann in der App jeweils einen Block von möglichen Links zu einem Paar von IDs aus beiden Tabellen.
Der Thread, der die Daten einliest braucht also eigentlich immer nur die Ergebnisse in eine Liste eintragen und wenn sie eine der IDs ändert issa mit dem Paar fertig und kann es in die Queue packen, die von einem (oder mehreren) anderen Threads abgearbeitet wird. Die wiederum sehen ja dann immer nur die Links eines Paares. Und da es 2+ Threads sind, wird der erste Thread ohne Unterbrechung Daten sammeln können und der andere pickt sich den besten raus.

Zitat:

Zitat von nahpets

Na, da stellt sich ja dann demnächst die Frage: Was hat länger gedauert, unsere Diskussion oder das Programm zum auswerten der Daten über den vollen Datenbestand :wink: :wink: :wink:

Hehe

[barnti]

Hallo,

hier mal die Ergebnisse des ersten Tests:

Tabelle a 3,7 Mio Einträge
Tabelle b 2,7 Mio Einträge

Ergebnis der Abfrage in 158 Sekunden!


Vielen Dank für eure Unterstützung!

[nahpets]

Hallo,

Zitat:

Zitat von barnti

Hallo,

hier mal die Ergebnisse des ersten Tests:

Tabelle a 3,7 Mio Einträge
Tabelle b 2,7 Mio Einträge

Ergebnis der Abfrage in 158 Sekunden!


Vielen Dank für eure Unterstützung!

heißt das, wenn wir jetzt mal hochrechnen, dass Du mit ein bisserl Glück auf eine Zeit von Minuten kommst, die der vorherigen Zeit in Stunden entsprach? Drück Dir die Daumen, dass es so wird. :wink:
Und das entgültige Ergebnis teilst Du uns dann mit incl. des Weges, wie Du da hingekommen bist. Bin schon gespannt, aber jetzt ist trotzdem erstmal Wochenende.

[barnti]

Hallo noch einmal,

ich denke, dass ich am Ende nächster Woche ein Ergebnis vorweisen kann. Dann gibt es einen ausführlichen Bericht!

So und nu wirlich: Schönes Wochenende!