SQL Query vereinfachen/performieren

[milanaasde]

Hallo,

habe nie wirklich grosse abfragen für eine datenbank erstellt..bissher immer relativ kleine abfragen, daher ist mein wissen auf diesem gebiet auch nicth wirklich ausgereift..

Doch musste jetzt einen ordentlichen batzen schreiben, und es funktioniert auch, legt nur leider die Datenbank für eine bestimme zeit VÖLLIG lahm:

SELECT gmttimestamp,valuecount,valuemin,valuemax,valuesum
FROM Summarydata,instname,parametername,patrolagent, appclassname
WHERE summarydata.appclassid =appclassname.appclassid
AND summarydata.parameterid=parametername.parameterid
AND summarydata.agentid=patrolagent.agentid
AND summarydata.instid=instname.instid
AND appclassname.name='NETWORK'
AND parametername.name='NETPacketsIn'
AND patrolagent.patrolagentname='alex'
AND instname.name='lo0'
AND gmttimestamp>= to_date('19.02.2005 15:00:00','dd.mm.yyyy hh24:mi:ss')
AND gmttimestamp<= to_date('19.04.2005 15:00:00','dd.mm.yyyy hh24:mi:ss')

Um bisschen aufzubrösseln:
Tabelle Appclassname enthält den Namen der AppKlasse und deren ID(7/NETWORK)
Tabelle Parametername enthält den Namen des Parameters und deren ID(904/NETPacketsIn)
Tabelle instname enthält den Namen der Instanz und deren ID(32/lo0)
Tabelle patrolagent enthält den Namen des Agenten und desen ID(231/alex)

In der Summarydata befindet sich folgendes:

| gmttimestamp | valuecount | valuemin | valuemax | valuesum | appclassid | parameterid | agentid | instid|

Das Statement leifert auch die gewünschten daten, aber ist VIEL zu performance-fressend.

[Christoph]

Dann würde ich vielleicht mal den einen oder anderen Index anlegen: Stichwort "CREATE INDEX".

[mwanaheri]

Hm, also ich würde vermuten, dass die aufwendigste Aktion das Vergleichen der Zeitstempel ist. Daher könnte es sich lohnen, die Abfrage so zu gestalten, dass möglichst wenige Zeitstempel verarbeitet werden müssen. Dazu könntest du eventuell mit einem Zwischenview arbeiten, der schon mal eine möglichst große Zahl von Datensätzen ausfiltert. Den beziehst du dann in die Abfrage ein. Dadurch müsste die Zahl der zu vergleichenden Zeitstempel sinken und das Ganze etwas schneller gehen.
Zu überlegen wäre auch, ob es schneller geht, wenn man die zu vergleichenden Zeitstempel in eine Variable packt, da sie dann nicht erst für den Vergleich erzeugt werden müssen.

Sicher bin ich mir da aber wirklich nicht...

[Jasper]

Das Statement leifert auch die gewünschten daten, aber ist VIEL zu performance-fressend.

lass dir den ausführungsplan (mit "explain plan" oder "autotrace" ausgeben), damit kann man dann was anfangen. sonst wird das kaffeesatzleserei.


-j

[Hans-Georg Normann]

FROM Summarydata,instname,parametername,patrolagent, appclassname

Hab's heute gelernt, also wollen wir dem mal Glauben schenken. Das ist ein Kartesischer Join! Hier werden ersteinmal alle möglichen Kombinationen der Datensätze untereinander gebildet. Aus diesem Zwischenergebnis heraus engst du dann mit deiner WHERE Klausel das Ergebnis ein. Bei der Anzahl der Tabellen wundert mich das also garnicht. Angenommen jede Datei hat nur 10 Datensätze, dann hast du 10^^5 (=100 000) Datensätze die ersteinmal temporär erzeugt werden.

Besser du gehst gleich auf einen INNER JOIN, alternativ LEFT OUTER JOIN. Kommt auf die Aufgabenstellung an.

SELECT
  gmttimestamp,
  valuecount,
  valuemin,
  valuemax,
  valuesum  
FROM Summarydata INNER JOIN instname ON (
    summarydata.instid=instname.instid
    AND instname.name='lo0'
)
  INNER JOIN parametername ON (
    summarydata.parameterid=parametername.parameterid
    AND parametername.name='NETPacketsIn'
)
  INNER JOIN patrolagent ON (
    summarydata.agentid=patrolagent.agentid
    AND patrolagent.patrolagentname='alex'
)
  INNER JOIN appclassname ON (
    summarydata.appclassid =appclassname.appclassid
    AND appclassname.name='NETWORK'
)


/* Musst du unbedingt minutengenau abrechnen?
 * Meine Erfahrung hat gezeigt, dass meistens tage genaues 
 * abrechnen ausreichend ist.

AND gmttimestamp>= to_date('19.02.2005 15:00:00','dd.mm.yyyy hh24:mi:ss')
AND gmttimestamp<= to_date('19.04.2005 15:00:00','dd.mm.yyyy hh24:mi:ss')
*/

Versuch mal, was dabei heraus kommt.

/*EDIT: Das mit den Indexen stommt natürlich auch. Die Joins sollten dir zeigen wo die sitzen müssen. EDIT */

Hans

[Jasper]

FROM Summarydata,instname,parametername,patrolagent, appclassname

Hab's heute gelernt, also wollen wir dem mal Glauben schenken. Das ist ein Kartesischer Join!

aehm, sieh dir die where-klausel nochmal genau an.

@OP:

das ist eine normale star-quey aus dem dw-bereich mit summarydata als facts-tabelle und den anderen als dimension-tabellen. oracle hat mit star_transformation_enabled einen init-parameter, der die query in etwa so in eine subquery-form umschreibt (falls bitmap-indizes auf den join-columns liegen):

SELECT gmttimestamp,valuecount,valuemin,valuemax,valuesum
FROM Summarydata
where appclassid in ( select appclassid from appclassname where appclassname.name='NETWORK' )
and parameterid in ( select parameterid from parametername where parametername.name='NETPacketsIn' )
and agentid in ( select agentid from patrolagent where patrolagentname='alex' )
and instid in ( select instid from instname where instname.name='lo0' )
AND gmttimestamp>= to_date('19.02.2005 15:00:00','dd.mm.yyyy hh24:mi:ss')
AND gmttimestamp<= to_date('19.04.2005 15:00:00','dd.mm.yyyy hh24:mi:ss')

generell sind folgende punkte für performance bei star-queries hilfreich:

- star_transformation_enabled setzen
- single-column bitmap-indizes auf alle fk-columns der fact-tabelle
- CBO verwenden, d.h. statistiken für alle tabellen + indizes generieren

aber wie gesagt, zuerst sollte man sich den ausführungsplan ansehen.

der timestamp-teil in der where-klausel ist vollkommen ok. to_date wird einmal ausgewertet und danach als konstanter vergleichswert für einen index-rangescan / tablescan (abhängig von der anzahl der zeilen und dem vorhandensein eines indexes) verwendet. die genauigkeit ist hierbei irrelevant, da date immer sekundengenau ist.


-j

[Hans-Georg Normann]

Hi Jasper, dass muss ich dir so glauben weil ich kein Oracle zur Verfügung habe. Mag sein das Oracle da etwas anders arbeitet, wie der Rest der Welt.

Aber warum wird nach wie vor eine Schreibweise von SQL genommen, die so eigentlich nicht (mehr) gewünscht ist. ANSI SQL empfiehlt m. E. die Verwendung von JOINS.

Mich würde es trotzdem interessieren, ob da ein Unterscheid zwischen der Ursprünglichen und meiner Version liegt. Wäre einfach mal nett zu wissen.

Hans

[Jasper]

Hi Jasper, dass muss ich dir so glauben weil ich kein Oracle zur Verfügung habe. Mag sein das Oracle da etwas anders arbeitet, wie der Rest der Welt.

bei welcher datenbank wird diese query als cartesian join ausgeführt?

Aber warum wird nach wie vor eine Schreibweise von SQL genommen, die so eigentlich nicht (mehr) gewünscht ist. ANSI SQL empfiehlt m. E. die Verwendung von JOINS.

das war hier irgendwo schon mal diskutiert worden. beide formen des inner join sind ansi-konform, was nicht ansi-konform ist, sind die oracle-eigenen formen der outer-joins (+). aber zugegeben, die JOIN-syntax ist besser lesbar und sollte verwendet werden, bei mir ists halt gewohnheit.

Mich würde es trotzdem interessieren, ob da ein Unterscheid zwischen der Ursprünglichen und meiner Version liegt. Wäre einfach mal nett zu wissen.

oracle führt beide formen exakt gleich aus.


-j

[mwanaheri]

aehm, sieh dir die where-klausel nochmal genau an.

der timestamp-teil in der where-klausel ist vollkommen ok. to_date wird einmal ausgewertet und danach als konstanter vergleichswert für einen index-rangescan / tablescan (abhängig von der anzahl der zeilen und dem vorhandensein eines indexes) verwendet. die genauigkeit ist hierbei irrelevant, da date immer sekundengenau ist.


-j

Ah, wieder was gelernt.

Zum karthesischen join: Von der Formulierung her ist es einer. Dass die DB mit dem Query-Analyzer was anderes draus macht, ist eine andere Sache (sie macht tatsächlich das gleiche wie bei einem expliziten join). Ich finde es zumindest einen besseren Stil, joins auch als solche zu formulieren. Bloß für die Performance macht das nicht unbedingt einen Unterschied.
Es _könnte_ aber einen Unterschied machen, wenn man den join vorzieht, der die stärkste Filterwirkung hat, weil dann bei den nachfolgenden Bedingungen weniger Fälle abgeprüft werden müssen. Oder kann man sich dabei auch auf den Optimierer verlassen?

[Hans-Georg Normann]

Hmm, scheine ich mit meiner Behauptung ja richtig ins "Fettnäpfchen" getreten zu sein Mögen hier Theorie und Praxis auch zwei paar verschiedene Schuhe sein, ich würde eine Abfrage nie in der von milanaasde geposteten Form erstellen. Aber soetwas ist auch eine Geschmacksfrage.

Momentan habe ich keine vernünftige DB im Zugriff um's selbst testen zu können. Deshalb alles nur graue Theorie.

@milanaasde

Lass dich von soviel Fachchinesich nicht unterkriegen. Wie kommst du voran?

[Jasper]

Zum karthesischen join: Von der Formulierung her ist es einer. Dass die DB mit dem Query-Analyzer was anderes draus macht, ist eine andere Sache (sie macht tatsächlich das gleiche wie bei einem expliziten join). Ich finde es zumindest einen besseren Stil, joins auch als solche zu formulieren. Bloß für die Performance macht das nicht unbedingt einen Unterschied.

auch wenn ich mich wiederhole, das ist _kein_ kartesischer join. für alle zweifler eine demo die jeder auf seiner lieblingsdatenbank testen kann:

> select * from f;
D1_ID D2_ID D3_ID
---------- ---------- ----------
1 1 1
1 1 1
1 1 1
1 2 3

> select * from d1;
ID
----------
1
2
3

> select * from d2;
ID
----------
1
2
3

> select * from d3;
ID
----------
1
2
3

> select count(*) from f join d1 on f.d1_id = d1.id join d2 on f.d2_id = d2.id join d3 on f.d3_id = d3.id;
COUNT(*)
----------
4

bisschen wenig für ein kartesisches produkt. es müsste eigentlich 4*3*3*3 = 108 rauskommen, was durch einen kartesischen join leicht überprüft werden kann:

> select count(*) from f,d1,d2,d3;
COUNT(*)
----------
108

ein kartesischer join kann teil des ausführungsplanes sein, in dem alle dimension tables über solch einen kartesischen join zu einer zwischentabelle zusammengefasst werden und diese zwischentabelle dann über einen join mit der fact-tabelle verbunden wird. das war bei oracle7 der bevorzugte weg.

Es _könnte_ aber einen Unterschied machen, wenn man den join vorzieht, der die stärkste Filterwirkung hat, weil dann bei den nachfolgenden Bedingungen weniger Fälle abgeprüft werden müssen. Oder kann man sich dabei auch auf den Optimierer verlassen?

bei RBO ist es üblich, durch die reihenfolge der tabellen zu optimieren. ich würde aber den CBO verwenden, der i.d.R. gute ergebnisse liefert _wenn_ die statistiken korrekt sind. da hapert es stellenweise bei oracle, also besser mal die statistiken nachprüfen.

-j