Optimierung einer SQL-Abfrage

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

[Elvis]

Wenn du soviele Records hast, dann ist das allerallerallerletzte, was du jemals tun willst, das Zeug zu sortieren... 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 von Elvis:

Wenn du soviele Records hast, dann ist das allerallerallerletzte, was du jemals tun willst, das Zeug zu sortieren... 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 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? ) 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

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

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!