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. :p 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. :p 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

[Christoph]

Nein, das ist kein kartesisches Produkt, weil Du ja die Join-Bedingung in der WHERE-Klausel formulierst. Das Datenbanksystem, das da zuerst ein kartesisches Produkt berechnet muss schon einen ziemlich lausigen Query-Optimizer haben.

Bei Oracle solltest Du mit dieser Abfrage keinen Probleme haben. Bei PostgreSQL kann es sein, dass Du ANALYZE ausführen musst, damit die Indizes verwendet werden (Du hast doch hoffentlich welche anegelgt?). Von mySQL habe ich schon oft gehört, dass es bei komplexen Abfragen in der Performance zusammenbricht; da hilft vielleicht ein Upgrade auf die neusete Version und die Verwendung von "innodb" als Tabellentyp.

[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

[mwanaheri]

Die Diskussion ist ziemlich müßig, weil sie dem Originalposter nun so gar nicht weiterhilft. Zudem bauen die Optimierer aus der im OP genannten Abfrage einen join.

Dennoch: du selbst sagst:


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


es ist also ein kartesischer Join, aus dem dann in der where-Klausel (so gegeben) die tauglichen Sätze gefiltert werden sollen. Aber das wird jetzt reichlich akademisch, oder?

@milanaasde:
greif doch mal jaspers guten Vorschlag mit "explain plan" auf. Was kommt denn dabei raus?

[elrond]

interessant ist tatsächlich der Ausführungsplan.

Oft fehl tatsächlich nur ein index, um die Performance ganz weit nach vorn zu bringen. Ich würde mir daraufhin die Fremdschlüsselattribute der Tabellen ansehen. Ist die Tabelle groß genug, und das Feld nicht indiziert, bricht die Performance definitiv ein... Mein Tipp: Mit oder ohne explain - einfach mal die Tabellen unde deren Fremdschlüssen untersuchen.

Manchmal kommt der der Sache nächer, wenn man Stück für Stück eine Tabelle nach der Anderen aus der Query nimmt und dann plötzlich merkt das die Performance stprunghaft besser wird.

[Jasper]

es ist also ein kartesischer Join, aus dem dann in der where-Klausel (so gegeben) die tauglichen Sätze gefiltert werden sollen. Aber das wird jetzt reichlich akademisch, oder?


dieser logik zufolge ist _jeder_ join ein kartesischer join, weil bei _jedem_ join ausser dem katesischen join durch die join-bedingung (die auch über die where-klausel definiert werden kann) die relationen der tabellen zueinander definiert werden. dieser logik kann ich persönlich nicht ganz folgen, but YMMV.

-j

[Hans-Georg Normann]

So, habe mal die Abfragen von Jasper unter FireBird nachgestellt und den Paln mit ausgegeben. Scheinbar gibt es da wirklich keine Unterschiede, egal wie die Joins definiert werden. Ich denke danit sollte es gut sein, oder?


/* SAMPLE 1 */
SQL> select count(*)
from f, d1, d2, d3;

PLAN JOIN (F NATURAL,D1 NATURAL,D2 NATURAL,D3 NATURAL)

COUNT
============

108

/* SAMPLE 2 */
SQL> 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;

PLAN JOIN (F NATURAL,D3 INDEX (D3_KEY),D1 INDEX (D1_KEY),D2 INDEX (D2_KEY))

COUNT
============

4


/* SAMPLE 3 */
SQL> select count(*)
from f,d1,d2,d3
Where f.d1_id=d1.id
and f.d2_id=d2.id
and f.d3_id=d3.id;

PLAN JOIN (F NATURAL,D3 INDEX (D3_KEY),D1 INDEX (D1_KEY),D2 INDEX (D2_KEY))

COUNT
============

4

Hans

[milanaasde]

Möchte mich für die reichhaltigen Antworetn erstmal bedanken!!

Werde sie jetzt alle mal langsam analysieren, und anwenden/durchtesten
(nach dem ich durch diesen thread (auch verständnissmäsig) durch bin:-))

Werde mich später nochmal melden, was es gebracht hat, aber hier nochmal ein herzliches Dankeschön

VIELEN DANK für die mühe