Anzeige:
Seite 1 von 2 12 LetzteLetzte
Ergebnis 1 bis 15 von 17

Thema: SQL Query vereinfachen/performieren

  1. #1
    Registrierter Benutzer
    Registriert seit
    07.03.2005
    Beiträge
    13

    SQL Query vereinfachen/performieren

    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:

    Code:
    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.
    Geändert von milanaasde (19-04-2005 um 15:49 Uhr)

  2. #2
    Registrierter Benutzer
    Registriert seit
    22.06.1999
    Beiträge
    677
    Dann würde ich vielleicht mal den einen oder anderen Index anlegen: Stichwort "CREATE INDEX".

  3. #3
    Registrierter Benutzer Avatar von mwanaheri
    Registriert seit
    28.10.2003
    Ort
    Bayreuth
    Beiträge
    569
    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...
    Geändert von mwanaheri (19-04-2005 um 19:09 Uhr)
    Das Ziel ist das Ziel.

  4. #4
    Registrierter Benutzer
    Registriert seit
    27.12.2002
    Ort
    Matrix
    Beiträge
    194
    Zitat Zitat von milanaasde
    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

  5. #5
    Registrierter Benutzer Avatar von Hans-Georg Normann
    Registriert seit
    05.03.2000
    Ort
    Uetersen
    Beiträge
    571
    Code:
    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.
    Code:
    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
    Geändert von Hans-Georg Normann (19-04-2005 um 21:48 Uhr)
    333 Mhz, 466 MHz, neee, ich hab was neues zuhause.....

  6. #6
    Registrierter Benutzer
    Registriert seit
    27.12.2002
    Ort
    Matrix
    Beiträge
    194
    Zitat Zitat von Hans-Georg Normann
    Code:
    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
    Geändert von Jasper (19-04-2005 um 22:39 Uhr)

  7. #7
    Registrierter Benutzer Avatar von Hans-Georg Normann
    Registriert seit
    05.03.2000
    Ort
    Uetersen
    Beiträge
    571
    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
    333 Mhz, 466 MHz, neee, ich hab was neues zuhause.....

  8. #8
    Registrierter Benutzer
    Registriert seit
    27.12.2002
    Ort
    Matrix
    Beiträge
    194
    Zitat Zitat von 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.
    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

  9. #9
    Registrierter Benutzer
    Registriert seit
    22.06.1999
    Beiträge
    677
    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.

  10. #10
    Registrierter Benutzer Avatar von mwanaheri
    Registriert seit
    28.10.2003
    Ort
    Bayreuth
    Beiträge
    569
    Zitat Zitat von Jasper
    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?
    Geändert von mwanaheri (20-04-2005 um 15:24 Uhr)
    Das Ziel ist das Ziel.

  11. #11
    Registrierter Benutzer Avatar von Hans-Georg Normann
    Registriert seit
    05.03.2000
    Ort
    Uetersen
    Beiträge
    571
    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?
    333 Mhz, 466 MHz, neee, ich hab was neues zuhause.....

  12. #12
    Registrierter Benutzer
    Registriert seit
    27.12.2002
    Ort
    Matrix
    Beiträge
    194
    Zitat Zitat von mwanaheri
    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

  13. #13
    Registrierter Benutzer Avatar von mwanaheri
    Registriert seit
    28.10.2003
    Ort
    Bayreuth
    Beiträge
    569
    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?
    Das Ziel ist das Ziel.

  14. #14
    Registrierter Benutzer Avatar von elrond
    Registriert seit
    04.10.2001
    Ort
    potsdam
    Beiträge
    881
    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.
    "Um die Welt zu ruinieren, genügt es, wenn jeder seine Pflicht tut." (Winston Churchill)

  15. #15
    Registrierter Benutzer
    Registriert seit
    27.12.2002
    Ort
    Matrix
    Beiträge
    194
    Zitat Zitat von mwanaheri
    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

Lesezeichen

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •