Beschleunigen einer SQL Abfrage (Inner Join)

Dom312_01e3cb · 9. November 2019 um 21:31

Hallo Forum.
ich habe (wie fast immer wenn ich poste) ein Geschwindigkeitsproblem.
Ich werte eine Tabelle mit rd. 80,000 Datensätzen aus. Ein Datensatz wird dabei über 3 Felder (Code, Datum, Integerfeld) identifiziert. Meine Auswertung:

Je Feld (ohne Schlüssel)
auf Basis einer aufsteigenden Sortierung
je Monat

5 gleichgroße Abschnitte (Anzahl Datensätze)
die Mediane jedes Abschnitts sowie den Gesamtmedian des Monats

Zunächst werden die Werte für einen Monat aufsteigend in ein Recordset geschrieben. Dabei werden Null und ein paar andere Feldwerte (Flags) ausgeschlossen. Auf dem Recordset werden dann die Mediane berechnet.

Diese Rst-Bildung dauert einen Lidschlag.
Jetzt muss ich ein weiteres Kriterium berücksichtigen. Dieses ist in einer zweiten Tabelle je Code (Teil des Schlüssels in Tab1) abgelegt. Nachdem ich das SQL Statement zur Bildung des Rst um das Kriterium und einen Inner Join auf Tab2 erweitert habe, Dauert die Ausführung 12sec(!!!).
Das alleine bedingt eine zusätzliche Durchlaufzeit von 5,5 std. Mein letzter Versuch war zunächst Recordsets der Basistabellen anzulegen und diese in SQL zu referenzieren. Klappt auch, dauert aber die angespr. 12sec.

Hat jemand eine Idee?
i: mir ist bewusst, dass ich die Datenstruktur der DB ändern könnte (Feld aus Tab2 in Tab1 packen). Dies würde aber zu Redundanzen führen. Und das will ich nicht.

i2: Das Kriterium aus Tab2 wird folgendermaßen abgefragt:
AND s.[ICB Sector Code] Not Like " & Chr(39) & „8*“ & Chr(39) & " " &
Führt die Einschränkung mit * vieleicht zur Verzögerung?

Greetz Dom

Netwolf · 9. November 2019 um 21:32

Hallo Dom,

i2: Das Kriterium aus Tab2 wird folgendermaßen abgefragt:
AND s.[ICB Sector Code] Not Like " & Chr(39) & „8*“ & Chr(39)
& " " &
Führt die Einschränkung mit * vieleicht zur Verzögerung?

watten datten?

not ist ne Bremse
Like ist ne Bremse
* ist die super - Bremse

die Kombination ist natürlich mit CHR(39) der HAMMER

Chr(39) muß jedes mal in ’ umgewandelt werden …

schreibe mal einfach:
AND s.[ICB Sector Code] Not Like ‚" & „8*“ & "‘"
noch kürzer und besser:
AND s.[ICB Sector Code] Not Like ‚8*‘"

wenn sowieso egal ist, was nach der 8 kommt (dein Sternchen), dann prüfe doch nur, ob eine 8 an erster Stelle vorhanden ist, z.B. mit der Funktion MID.

and MID(s.[ICB Sector Code],1,1) „8“

natürlich wäre es besser, die Abfrage gleich entsprechend zu filtern, dass gar keine 8*er vorkommen, dann müsste man nicht für jeden Datensatz die Bedingung prüfen und erfüllen.

Grüße aus Raben Steinfeld (bei Schwerin)
Wolfgang
(Netwolf)

Dom312_01e3cb · 9. November 2019 um 21:33

Hi NetWolf,

and MID(s.[ICB Sector Code],1,1) „8“

baue ich ein

natürlich wäre es besser, die Abfrage gleich entsprechend zu
filtern, dass gar keine 8*er vorkommen, dann müsste man nicht
für jeden Datensatz die Bedingung prüfen und erfüllen.

Das bitte genauer: Ich filtere das 8er Kriterium heraus und lasse meine weiteren Einschränkungen auf das gefilterte Ergebnis laufen. Das macht Sinn. Aber wie?

Die Inhalte eines Recordsets kann ich nicht über SQL einschränken und einem weitern Rst zuweisen, da ich das Recordsetobject im SQL Statement nicht referenzieren kann.

Was ich also brauche, ist ein Abfrageergebnis, das ich EINMAL erstelle und auf das ich beliebig viele Abfragen ausführen kann. So etwas wie eine temporäre Tabelle, die im Arbeitsspeicher liegt und nur zur Laufzeit existiert. Dachte Rsts würden das leisten. Fehlanzeige

Gruß aus D am R
Dom

Manfred_Tessin · 9. November 2019 um 21:33

Hi, Dom!

Was ich also brauche, ist ein Abfrageergebnis, das ich EINMAL
erstelle und auf das ich beliebig viele Abfragen ausführen
kann. So etwas wie eine temporäre Tabelle, die im

Das wäre dann eine Abfrage. Oder ggf. auch mehrere. Und für die Performance: Mach Dir doch 'ne temporäre Tabelle für so was. Oder geht das nicht? Was für 'ne DB hast Du denn am laufen?

Mal so als grobe Faustformel: Einschränkungen (WHERE) möglichst früh, Joins möglichst spät verwenden.

Gruß, Manfred

Dom312_01e3cb · 9. November 2019 um 21:33

Hi Manfred

Das wäre dann eine Abfrage. Oder ggf. auch mehrere. Und für
die Performance: Mach Dir doch 'ne temporäre Tabelle für so
was. Oder geht das nicht? Was für 'ne DB hast Du denn am
laufen?

Das ganze läuft auf MS Access (langsam fange ich an zu bereuen). Habe ursprünglich MySQL gelernt. CREATE TEMPORARY TABLE gibt es aber, soweit ich das beurteilen kann, nicht in Access.

Mal so als grobe Faustformel: Einschränkungen (WHERE)
möglichst früh, Joins möglichst spät verwenden.

Das hier wäre die erste Abfrage:
„SELECT f.* FROM tblStandingdata AS s INNER JOIN tblFactors_Stoxx600 AS f ON s.[JCF Code] = f.[JCF Code] WHERE MID(s.[ICB Sector Code],1,1) ‚8‘;“

Aktuell muss ich beide Teile gemeinsam abfragen. Dabei stehen die Einschränkungen auf f vor dem MID(s - Kriterium:

SELECT f.Date, f.[" & gstrFactorName & „], f.Price, f.[Price Next Month]
FROM tblStandingData AS s INNER JOIN " & strTblName & " AS f ON s.[JCF Code] = f.[JCF Code]
WHERE f.Date=#“ & dateMonth & „# "
AND t.[“ & gstrFactorName & „] 0.0000000000111
AND t.[“ & gstrFactorName & „] 0.0000000000222
AND t.[“ & gstrFactorName & „] 0.0000000000333
AND t.[“ & gstrFactorName & „] 0.0000000000444
AND t.[“ & gstrFactorName & „] Is Not Null
AND t.Price Is Not Null
AND t.[Price Next Month] Is Not Null
AND MID(s.[ICB Sector Code],1,1) ‚8‘
ORDER BY t.[“ & gstrFactorName & "];

Von 80000 Datensätzen sollten vor dem Join nur noch etwa 600 - 1000 übrig sein, bevor 8 eingeschränkt wird.
Würdest Du hier gemäß Deiner Fausformel etwas umstellen?

Gruß
Dom

Dom312_01e3cb · 9. November 2019 um 21:33

Habe mal versuchsweise auf LEFT JOIN umgestellt. Das stellt offenbar die gewünschte Einschränkungsreihenfolge her. Geht bedeutend schneller.
he he

Bin aber immernoch der festen Überzeugung, dass eine Zerlegung in zwei Abfragen (z.B. in Form einer Temp Table) bedeutendschneller wäre.

Greetz
Dom

Manfred_Tessin · 9. November 2019 um 21:33

Hallo, Dom!

Das ganze läuft auf MS Access (langsam fange ich an zu
bereuen). Habe ursprünglich MySQL gelernt. CREATE TEMPORARY
TABLE gibt es aber, soweit ich das beurteilen kann, nicht in
Access.

Dann nimm doch eine quasi-temporäre Tabelle, die Du erzeugst und wieder löschst.

SELECT f.Date, f.[" & gstrFactorName & „], f.Price, f.[Price
Next Month]
FROM tblStandingData AS s INNER JOIN " & strTblName & " AS f
ON s.[JCF Code] = f.[JCF Code]
WHERE f.Date=#“ & dateMonth & „# "
AND t.[“ & gstrFactorName & „] 0.0000000000111
AND t.[“ & gstrFactorName & „] 0.0000000000222
AND t.[“ & gstrFactorName & „] 0.0000000000333
AND t.[“ & gstrFactorName & „] 0.0000000000444
AND t.[“ & gstrFactorName & „] Is Not Null
AND t.Price Is Not Null
AND t.[Price Next Month] Is Not Null
AND MID(s.[ICB Sector Code],1,1) ‚8‘
ORDER BY t.[“ & gstrFactorName & "];

Von 80000 Datensätzen sollten vor dem Join nur noch etwa 600 -
1000 übrig sein, bevor 8 eingeschränkt wird.
Würdest Du hier gemäß Deiner Fausformel etwas umstellen?

Ungefähr so:
DoCmd.Runsql „SELECT tblStandingdata.* INTO s FROM tblStandingdata WHERE MID([ICB Sector Code],1,1) ‚8‘“

damit hast Du schon mal Deine Tabelle/Alias „s“.

Das dann noch mit f und t (woher kommt das?) analog, und dann erst Deine Abfrage öffnen mit Zugriff auf s, f, t.

Gruß, Manfred