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

Thema: Laufzeitproblem mit Abfrage

  1. #1
    Registrierter Benutzer
    Registriert seit
    10.07.2004
    Beiträge
    55

    Laufzeitproblem mit Abfrage

    Hi,

    ich habe ein Problem bei einer SQL-Abfrage betreffend der benötigten Laufzeit, aber erst mal zur Problemstellung:

    Ich habe Kunden und Artikel. Neben dem Standard-Artikelpreis, soll für jeden Kunden ein eigener Rabatt festgesetzt werden können. Um ein bisschen die Performance zu testen, habe ich 1000 Kunden und 1000 Artikel angelegt
    und Zufallsrabatte gesetzt: Kunden x Artikel -> also 1000000 Rabatt-Datensätze.

    Was ich vorhabe ist, dass man zu einem bestimmten Artikel alle Kunden angezeigt bekommt und entweder mit zugehörigem Rabatt (falls gesetzt) und dem daraus berechneten Endpreis oder halt
    nur der Standard-Artikelpreis, wenn halt für den Artikel bzw. den Kunden kein Rabatt vorhanden ist.

    Genauso möchte ich auch für einen bestimmten Kunden alle Artikel mit oben bereits erwähnten Informationen anzeigen lassen.

    Zu meinem Problem:

    Für einen Artikel alle Kunden mit Rabatten und berechneten Endpreisen anzeigen lassen ist kein Problem. Die benötigte
    Zeit bei den 1000000 Rabatten ist zufriedenstellend!

    ABER: Beim Anzeigen aller Artikel für einen Kunden beträgt die benötigte Zeit allerdings mehr als !8 Sekunden!, was
    natürlich alles andere als aktzeptabel ist. Ich finde aber einfach nicht die richtige Abfrage um ein ähnlich gutes Ergebnis wie bei
    der ersten Abfrage zu erhalten, wobei ich den Unterschied zu beiden im zeitlichen Umfang nicht wirklich ausmachen und dann halt optimieren kann:

    Hier mal die Randinformation:

    - Tabelle 'Customers' (customers_id, ...)
    - Tabelle 'Articles' (articles_id, articles_name, ...)
    - Tabelle 'Articles_Prices' (articles_id, articles_price_netto)
    - Tabelle 'Articles_Customers_PriceList' (articles_id, customers_id, articles_price_discount)

    Abfrage 1 mit guter Laufzeit (für einen Artikel (zB. ID = 11) alle Kunden):

    SELECT C.customers_id, COALESCE(ACP.articles_price_discount,0) AS discount, CAST((AP.articles_price_netto-AP.articles_price_netto/100*COALESCE(ACP.articles_price_discount,0)) AS DECIMAL(15,4)) AS discount_result

    FROM ARTICLES_PRICES AS AP, CUSTOMERS AS C LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON C.customers_id = ACP.customers_id AND ACP.articles_id = '11'

    WHERE AP.articles_id = '11' order by C.customers_id;
    Abfrage 2 mit 8-Sekunden Laufzeit (für einen Kunden (zB. ID = 26) alle Artikel):

    SELECT AP.articles_id, A.articles_name, AP.articles_price_netto, COALESCE(ACP.articles_price_discount,0),
    CAST((AP.articles_price_netto*(COALESCE(ACP.articl es_price_discount,0)/100)) AS DECIMAL(15,4)) AS discount_result

    FROM ARTICLES AS A, ARTICLES_PRICES AS AP LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON ACP.customers_id = '26' AND AP.articles_id = ACP.articles_id

    WHERE AP.articles_id = A.articles_id order by A.articles_id;
    Selbst in vereinfachter Form ohne Berechnungen der Endpreise dauert es nur unerheblich kürzer (mal 3 Versionen):

    Code:
    1.) SELECT AP.articles_id, ACP.articles_price_discount FROM ARTICLES_PRICES AS AP LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON ACP.CUSTOMERS_ID = '26' AND AP.ARTICLES_ID = ACP.ARTICLES_ID;
    2.) SELECT AP.articles_id, ACP.articles_price_discount FROM ARTICLES_PRICES AS AP LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON AP.ARTICLES_ID = ACP.ARTICLES_ID WHERE ACP.CUSTOMERS_ID = '26';
    3.) SELECT AP.articles_id, ACP.articles_price_discount FROM ARTICLES_PRICES AS AP LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON ACP.CUSTOMERS_ID = '26' WHERE ACP.articles_ID = AP.ARTICLES_ID ;
    Selbst ohne Berechnungen braucht man mit der vereinfachten Form recht lange. Woran liegt das genau? Wie muss ich das ganze umstellen?
    Wie kommen diese erheblichen Unterschiede zur 1. Abfrage zustande?
    Ich habe schon recht viel ausprobiert, komme aber nicht auf die Loesung.

    Habt ihr da Loesungsansaetze??

    Vielen Dank schon mal im Voraus

  2. #2
    Registrierter Benutzer Avatar von BLUESCREEN3D
    Registriert seit
    08.11.2002
    Beiträge
    665
    Erstmal was allgemeines: Man sollte WHERE-Bedingungen nicht hinter das ON schreiben. Da gehört nur das hin, was für den JOIN wichtig ist. Umgekehrt gilt das gleiche für WHERE (da hast du beim 2. Query eine Bedingung, die eig. hinter das ON sollte, welches wiederum nicht existiert, weil du A und AP ohne JOIN aufgelistet hast).

    Und noch was: Benutzt bitte das [code]-Tag.

    Wozu brauchst du die Tabelle Articles_Prices?
    Es wird jawohl jeder Artikel genau einen Preis haben und der kann dann doch auch in Articles stehen.

    Zu deiner Frage: Vllt. löst sich das Problem schon, wenn du oben geschriebenes beachtest, also setz das erstmal um.
    Danach schreibst du direkt vor das SELECT einfach mal ein EXPLAIN und postest für beide Queries die Ausgabe davon hier (nächste Frage: Welches DBMS benutzt du?).
    Daran kann man ablesen, wo das Problem liegt.

  3. #3
    Registrierter Benutzer
    Registriert seit
    10.07.2004
    Beiträge
    55
    Hi,

    erst mal vielen Dank für deine Antwort
    Das, was ich schon mal mit Sicherheit beantworten kann: Das DBMS ist H2.

    Hatte mich auch gewundert, dass du mich auf das CODE-Tag aufmerksam machst, bei dem ich mir eigentlich sicher war es verwendet zu haben
    Dann habe ich gesehen, dass ich dieses nur bei der letzten Anfrage benutzt habe und mich bei den ersten beiden verklickt hatte. Also normalerweise benutze ich es

    So... die Tabelle articles_prices habe ich gelöscht und führe wie du sagtest den Preis jetzt in der Tabelle articles auf.

    Den Punkt, den ich nicht verstehe, ist deine Anmerkung über ON und WHERE.

    Die 1. Abfrage sieht jetzt so aus:

    Code:
    SELECT C.customers_id, CONCAT(C.customers_lastname,', ',C.customers_firstname) AS name, COALESCE(ACP.articles_price_discount,0) AS discount, CAST((A.articles_price_netto-A.articles_price_netto/100*COALESCE(ACP.articles_price_discount,0)) AS DECIMAL(15,4)) AS discount_result FROM ARTICLES AS A, CUSTOMERS AS C LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON C.customers_id = ACP.customers_id AND ACP.articles_id = '720' WHERE A.articles_id = ACP.articles_id order by C.customers_id;
    Die WHERE steht jetzt hinter dem ON, aber anders geht es ja nicht, oder?
    Denn gejoint wird ja Customers und Article_Customers_Pricelist. Muss ja noch iirgendwie den Artikelnamen aus Articles bekommen oder soll ich das auch mit nem Join machen?

    Diese Abfrage mit Ergebnis von 1000 Datensätzen dauert 140ms. War ja vorher auch sehr schnell...

    Zu meinem Problemfall
    Die ganz simple Form ohne Berechnungen:

    Abfrage 2:
    Code:
    SELECT A.articles_id, ACP.articles_price_discount FROM ARTICLES AS A LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON ACP.CUSTOMERS_ID = '726' AND A.ARTICLES_ID = ACP.ARTICLES_ID;
    Ohne WHERE. Fehlt das in diesem Falle? War das in diesem Falle das, was gefehlt hat oder habe ich dich da falsch verstanden? Sind ja nur noch 2 Tabellen jetzt, so dass ich ja alles im ON erledigen können müsste, oder nicht?
    Umzustellen wüsste ich jetzt auch nichts

    Laufzeit: Schlappe 16234 ms, also ein Windhund für Beamte...
    Leider bin ich keiner...

    Nun mit Explain davor:

    Abfrage 1:
    Code:
    SELECT C.CUSTOMERS_ID, CONCAT(C.CUSTOMERS_LASTNAME, ', ', C.CUSTOMERS_FIRSTNAME) AS NAME, COALESCE(ACP.ARTICLES_PRICE_DISCOUNT, 0) AS DISCOUNT, CAST(A.ARTICLES_PRICE_NETTO - ((A.ARTICLES_PRICE_NETTO / 100) * COALESCE(ACP.ARTICLES_PRICE_DISCOUNT, 0)) AS DECIMAL(15, 4)) AS DISCOUNT_RESULT
    FROM PUBLIC.CUSTOMERS C /* PUBLIC.PRIMARY_KEY_5 */
    LEFT OUTER JOIN PUBLIC.ARTICLES_CUSTOMERS_PRICELIST ACP /* PUBLIC.PRIMARY_KEY_9: CUSTOMERS_ID = C.CUSTOMERS_ID AND ARTICLES_ID = '720' */ ON (ACP.ARTICLES_ID = 720) AND (C.CUSTOMERS_ID = ACP.CUSTOMERS_ID)
    INNER JOIN PUBLIC.ARTICLES A /* PUBLIC.PRIMARY_KEY_12: ARTICLES_ID = ACP.ARTICLES_ID */
    WHERE A.ARTICLES_ID = ACP.ARTICLES_ID
    Abfrage 2:
    Code:
    SELECT A.ARTICLES_ID, ACP.ARTICLES_PRICE_DISCOUNT
    FROM PUBLIC.ARTICLES A /* PUBLIC.TEMP_TABLE_0_0_TABLE_SCAN */
    LEFT OUTER JOIN PUBLIC.ARTICLES_CUSTOMERS_PRICELIST ACP /* PUBLIC.PRIMARY_KEY_9: CUSTOMERS_ID = '726' AND ARTICLES_ID = A.ARTICLES_ID */ ON (ACP.CUSTOMERS_ID = 726) AND (A.ARTICLES_ID = ACP.ARTICLES_ID)
    Hoffe, dass du damit was anfangen kannst!

    Liebe Grüße

  4. #4
    Registrierter Benutzer
    Registriert seit
    07.05.2007
    Beiträge
    656
    Moin,

    einfache Formel: ON-Klausel immer und nur für Join-Bedingungen (also a.id1=b.id1), WHERE-Klausel immer und nur für Einschränkungen der Ergebnismenge (also a.id1=42). Und: Immer für alle in der FROM-Klausel aufgezählten Tabellen die Join-Bedingungen aller Foreign Keys zu den Primary Keys der anderen zutreffenden Tabellen angeben (Article_Customers_Pricelist.customers_id = Customers.customers_id AND Article_Customers_Pricelist.articles_id = Articles.articles_id).

    Jan

    EDIT: Vielleicht wäre es auch eine gute Idee, auch in der 2. Abfrage die einschränkende WHERE-Bedingung customers_id auf die Tabelle Customers anzuwenden (die also mit in die FROM-Klausel aufnehmen, inkl. Join!) - Du suchst nämlich nach dieser ID ausgerechnet in der Tabelle, in der sie gar nicht vorhanden sein muss.
    Geändert von jan61 (10-12-2007 um 20:14 Uhr)

  5. #5
    Registrierter Benutzer Avatar von BLUESCREEN3D
    Registriert seit
    08.11.2002
    Beiträge
    665
    edit: jan61 war schneller - und seine Anmerkung im edit ist wichtig

    Zitat Zitat von Overlord04 Beitrag anzeigen
    Die 1. Abfrage sieht jetzt so aus:

    Code:
    SELECT ...
    FROM ARTICLES AS A, CUSTOMERS AS C LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON C.customers_id = ACP.customers_id AND ACP.articles_id = '720' WHERE A.articles_id = ACP.articles_id order by C.customers_id;
    Die WHERE steht jetzt hinter dem ON
    So meinte ich das nicht
    Mach es so:
    Code:
    SELECT ...
    FROM ARTICLES AS A, CUSTOMERS AS C
    LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON A.articles_id = ACP.articles_id AND C.customers_id = ACP.customers_id
    WHERE A.articles_id = '720'
    order by C.customers_id;
    Erläuterung: Dass die IDs von A, ACP und C zueinander passen, gehört zum JOIN und dass du nur Artikel 720 sehen willst hat mit dem JOIN nichts zu tun und kommt deshalb zum WHERE.

    Zitat Zitat von Overlord04 Beitrag anzeigen
    Abfrage 2:
    Code:
    SELECT ...
    FROM ARTICLES AS A LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON ACP.CUSTOMERS_ID = '726' AND A.ARTICLES_ID = ACP.ARTICLES_ID;
    Und das so:
    Code:
    SELECT ...
    FROM ARTICLES AS A, CUSTOMERS AS C
    LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON A.ARTICLES_ID = ACP.ARTICLES_ID AND C.CUSTOMERS_ID = ACP.CUSTOMERS_ID
    WHERE C.CUSTOMERS_ID = '726';
    Zitat Zitat von Overlord04 Beitrag anzeigen
    Nun mit Explain davor: (...)
    H2 stellt das etwas komisch dar und in der Doku steht auch nichts dazu ...
    Ich vermute mal folgendes:
    Du hast in der Tabelle Articles_Customers_PriceList weder einen Index noch einen Primärschlüssel (der auch als Index fungieren würde).
    Dadurch muss die ganze Tabelle durchsucht werden (ein Index würde die Suche beschleunigen - vergleichbar mit dem Inhaltsverzeichnis einen Buches, wo du sonst jede Seite einmal angucken müsstest, um irgendwas bestimmtes zu finden).
    Also leg am besten in Articles_Customers_PriceList einen Primärschlüssel über die beiden Spalten articles_id und customers_id an. Dadurch stellst du auch gleichzeitig sicher, dass es für keine Artikel-Kunden-Kombination mehr als einen Rabatt gibt.
    Code:
    CREATE PRIMARY KEY ON Articles_Customers_PriceList(articles_id, customers_id)
    Mehr Infos:
    http://de.wikipedia.org/wiki/Datenbankindex
    http://de.wikipedia.org/wiki/Schl%C3...28Datenbank%29
    Geändert von BLUESCREEN3D (10-12-2007 um 20:44 Uhr) Grund: Query geändert

  6. #6
    Registrierter Benutzer
    Registriert seit
    10.07.2004
    Beiträge
    55
    So...

    @jan61: Danke für die Erklärung. Kann ich nachvollziehen!

    Zu deinem 'Edit': Hast mit der Einschränkung natürlich recht. Bin einfach davon ausgegangen, dass es die customers_id dort gibt. Mit "inkl. Join" meinst Du dann einen 2. Join oder einfach die Bedingung "customers_id = '816'" in der WHERE als auch im einzigen JOIN?

    @BLUESCREEN3D:

    Deine umgestellte Abfrage (zu Nr. 2) ist nicht möglich (aber erst geschrieben, dass sie genauso langsam ist, aber jetzt noch mal probiert und stelle fest, dass er A.articles_id im JOIN ja garnicht geht. Das ist also so nicht möglich!

    Und zu deiner Anmerkung mit dem Index. Ein Primärschlüssel ist vorhanden! Und natürlich auch über articles_id und customers_id. Daran kann es also nicht liegen. War mir erst nicht sicher und habe noch mal nachgeguckt, aber ich habe ihn leider gesetzt, so dass es daran nicht liegt
    Geändert von Overlord04 (10-12-2007 um 21:46 Uhr)

  7. #7
    Registrierter Benutzer Avatar von BLUESCREEN3D
    Registriert seit
    08.11.2002
    Beiträge
    665
    Zitat Zitat von Overlord04 Beitrag anzeigen
    Deine umgestellte Abfrage (zu Nr. 2) ist nicht möglich (aber erst geschrieben, dass sie genauso langsam ist, aber jetzt noch mal probiert und stelle fest, dass er A.articles_id im JOIN ja garnicht geht.
    Warum sollte das nicht gehen?

  8. #8
    Registrierter Benutzer
    Registriert seit
    10.07.2004
    Beiträge
    55
    Tja... weiss ich auch nicht, aber er sagt mir, dass er entsprechende Column oder Table nicht kennt. Weiss nicht, ob das nur etwas H2-Typisches ist, aber meine, so wie ich mich entsinnen kann, dass er nur mit den beiden Tables arbeiten kann, die mit nem JOIN in Verbindung stehen.

  9. #9
    Registrierter Benutzer Avatar von BLUESCREEN3D
    Registriert seit
    08.11.2002
    Beiträge
    665
    Muss an H2 liegen - man kann alles im JOIN verwenden, was irgendwo davor steht.

    Ersetz mal das Komma hinter dem FROM durch ein "CROSS JOIN".
    Vllt. geht es dann.

  10. #10
    Registrierter Benutzer
    Registriert seit
    10.07.2004
    Beiträge
    55
    Hi,

    mit dem CROSS JOIN geht es. Sieht ja dann so aus:

    Code:
    SELECT A.articles_id
    FROM ARTICLES AS A CROSS JOIN CUSTOMERS AS C
    LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON A.ARTICLES_ID = ACP.ARTICLES_ID AND C.CUSTOMERS_ID = ACP.CUSTOMERS_ID
    WHERE C.CUSTOMERS_ID = '726';
    Diese Abfrage dauert bei mir 5453ms (bei Wiederholung mit anderer ID bei 3391), was mich also noch nicht weitergebracht hat. Könnte langsam echt k...
    Woran könnte das ganze noch liegen
    Geändert von Overlord04 (12-12-2007 um 18:28 Uhr)

  11. #11
    Registrierter Benutzer Avatar von BLUESCREEN3D
    Registriert seit
    08.11.2002
    Beiträge
    665
    Poste bitte nochmal die EXPLAIN-Ausgabe zu diesem Query.
    Außerdem steht in der H2-Doku was von ANALYZE - lass das vorher mal laufen.

  12. #12
    Registrierter Benutzer
    Registriert seit
    10.07.2004
    Beiträge
    55
    Hi,

    ...aber gerne:

    mit EXPLAIN:

    Code:
    SELECT A.ARTICLES_ID
    FROM PUBLIC.ARTICLES A /* PUBLIC.ARTICLES_TABLE_SCAN */
    INNER JOIN PUBLIC.CUSTOMERS C /* PUBLIC.PRIMARY_KEY_4: CUSTOMERS_ID = 726 */ /* WHERE C.CUSTOMERS_ID = 726*/
    LEFT OUTER JOIN PUBLIC.ARTICLES_CUSTOMERS_PRICELIST ACP /* PUBLIC.PRIMARY_KEY_8: ARTICLES_ID = A.ARTICLES_ID AND CUSTOMERS_ID = C.CUSTOMERS_ID */ ON (A.ARTICLES_ID = ACP.ARTICLES_ID) AND (C.CUSTOMERS_ID = ACP.CUSTOMERS_ID)
    WHERE C.CUSTOMERS_ID = 726
    und ANALYZE sagt was aus? Guck mal hier. Das sagt mir nichts. Habe das mal ausgeführt und einen Zeitwert von etwas mehr als 3000ms bei 1000 bekommen... Mhmm...

  13. #13
    Registrierter Benutzer Avatar von BLUESCREEN3D
    Registriert seit
    08.11.2002
    Beiträge
    665
    Zitat Zitat von Overlord04 Beitrag anzeigen
    und ANALYZE sagt was aus?
    Das sagt nichts aus, aber ich dachte, danach ist der Query vllt. schneller.

    Und die obige Ausgabe von EXPLAIN sagt, dass überall die richtigen Keys benutzt werden. Schneller wird es wohl nicht mehr werden - das liegt entweder an H2 oder es wird von irgendwas anderem ausgebremst.

  14. #14
    Registrierter Benutzer
    Registriert seit
    07.05.2007
    Beiträge
    656
    Zitat Zitat von Overlord04 Beitrag anzeigen
    Hi,

    mit dem CROSS JOIN geht es. Sieht ja dann so aus:

    Code:
    SELECT A.articles_id
    FROM ARTICLES AS A CROSS JOIN CUSTOMERS AS C
    LEFT JOIN ARTICLES_CUSTOMERS_PRICELIST AS ACP ON A.ARTICLES_ID = ACP.ARTICLES_ID AND C.CUSTOMERS_ID = ACP.CUSTOMERS_ID
    WHERE C.CUSTOMERS_ID = '726';
    Diese Abfrage dauert bei mir 5453ms (bei Wiederholung mit anderer ID bei 3391), was mich also noch nicht weitergebracht hat. Könnte langsam echt k...
    Woran könnte das ganze noch liegen
    Hm, ich kenne H2 nicht. Was Du aber noch probieren kannst:

    1. Hast Du Indizes auf den Spalten ARTICLES_CUSTOMERS_PRICELIST.CUSTOMERS_ID und ARTICLES_CUSTOMERS_PRICELIST.ARTICLES_ID (also jeweils einen eigenen Index auf jeder Spalte)? Wie ist Dein Primary Key in dieser Tabelle aufgebaut? ARTICLES_ID, CUSTOMERS_ID oder anders rum? Wenn in der o. g. Reihenfolge, dann lege doch mal einen UNIQUE INDEX in der anderen Reihenfolge an? Probiere die Abfrage dann mal sowohl mit 2 separaten Indizes und mit dem zusätzlichen UNIQUE INDEX. Wie sieht es dann aus?

    2. Wie sieht das Ganze zeitmäßig aus, wenn Du statt des LEFT JOIN einen CROSS JOIN benutzt (also erstmal nur die Artikel raussammelst, die auch in der Sonderpreistabelle stehen)? Damit könnte man sich herantasten, ob das Problem tatsächlich im LEFT JOIN liegt.

    3. Wenn 2. gut aussieht und H2 EXISTS unterstützt, dann kannst Du Dir mal gezielt die Sätze holen, die nicht in der Sonderpreistabelle stehen:
    Code:
    SELECT A.articles_id
     FROM ARTICLES AS A CROSS JOIN CUSTOMERS AS C
    WHERE C.CUSTOMERS_ID = '726' AND NOT EXISTS (SELECT ARTICLES_ID FROM ARTICLES_CUSTOMERS_PRICELIST AS ACP WHERE ACP.CUSTOMERS_ID = C.CUSTOMERS_ID AND ACP.ARTICLES_ID = A.ARTICLES_ID);
    Wie sieht es dann aus?

    4. Wenn 1. nix bringt, aber 2. und 3. signifikant bessere Zeiten liefern - und wenn H2 UNION unterstützt, dann kannst Du beide Abfragen per UNION verknüpfen.

    Jan
    Geändert von jan61 (13-12-2007 um 01:09 Uhr)

  15. #15
    Registrierter Benutzer Avatar von BLUESCREEN3D
    Registriert seit
    08.11.2002
    Beiträge
    665
    Zu 1.: Laut EXPLAIN-Ausgabe werden beim LEFT JOIN beide Spalten des Primärschlüssels genutzt. Falls die Reihenfolge wichtig ist, stimmt die also bereits und ein zusätzlicher Index dürfte nichts bringen.
    Aber probieren kann man es ja mal.

Lesezeichen

Berechtigungen

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