Laufzeitproblem mit Abfrage

[Overlord04]

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):


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 :)

[BLUESCREEN3D]

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.

[Overlord04]

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:



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:

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:

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:

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 :)

[jan61]

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.

[BLUESCREEN3D]

edit: jan61 war schneller - und seine Anmerkung im edit ist wichtig :D


Die 1. Abfrage sieht jetzt so aus:



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 :D
Mach es so:

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.


Abfrage 2:

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:

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';


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.

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%BCssel_%28Datenbank%29

[Overlord04]

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 :(

[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.
Warum sollte das nicht gehen?

[Overlord04]

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.

[BLUESCREEN3D]

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.

[Overlord04]

Hi,

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


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 :confused:

[BLUESCREEN3D]

Poste bitte nochmal die EXPLAIN-Ausgabe zu diesem Query.
Außerdem steht in der H2-Doku was von ANALYZE - lass das vorher mal laufen.

[Overlord04]

Hi,

...aber gerne:

mit EXPLAIN:


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 (http://www.h2database.com/html/grammar.html?highlight=analyze#sql29). Das sagt mir nichts. Habe das mal ausgeführt und einen Zeitwert von etwas mehr als 3000ms bei 1000 bekommen... Mhmm...

[BLUESCREEN3D]

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.

[jan61]

Hi,

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


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 :confused:

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:

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

[BLUESCREEN3D]

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.

[jan61]

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.

Ist richtig, aber ich habe schon diverse Male erlebt, dass die Reihenfolge nicht egal ist. Einen Versuch sollte es wert sein ;-) Eine andere Variante zum Probieren fiel mir noch ein: Manchmal kann auch die Reihenfolge in der FROM-Klausel eine Rolle spielen (habe ich beim ach so tollen Oracle schon gesehen). Da in der kritischen Abfrage nach Kunden selektiert wird, sollte man vielleicht mal CUSTOMERS an die 1. Stelle nehmen statt ARTICLES.

Jan

[Overlord04]

Habe mir mal die Muehe gemacht und ein paar unterschiedliche Tabellen erstellt und die Abfragezeiten protokolliert. Immer mit geaenderten IDs fuer Artikel bzw Kunden, so dass er immer ne neue Aufgabe bekam. Vielleicht schreibt ihr mal, wie ihr die Tabelle anlegen wuerdet, wenn eure Version (falls falsch verstanden) noch nicht dabei ist. H2 legt uebrigens automatisch, wie ihr sehen koennt, Constraint-Indizies an. Die 2. Abfrage ist am schnellsten.
Als Testabfrage habe ich die Abfrage von jan61 genommen, die die Artikel ermittelt, fuer welche ein Kunde keinen eigenen Rabatt bekommen hat:


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);



Ich weiss nicht, wie sinnvoll meine Taetigkeit war, aber vielleicht koennt ihr ja damit was anfangen. Danke auf jeden Fall schon mal fuer eure Mitarbeit :)

1.)

CREATE TABLE ARTICLES_CUSTOMERS_PL1(
articles_id INT(11) NOT NULL,
customers_id INT(11) NOT NULL,
articles_price_netto DECIMAL(15,4) NOT NULL DEFAULT 0,
PRIMARY KEY(articles_id, customers_id),
FOREIGN KEY(articles_id) REFERENCES articles(articles_id),
FOREIGN KEY(customers_id) REFERENCES customers(customers_id));

Indexe (laut H2):
o PRIMARY_KEY_11
- eindeutig
- ARTICLES_ID, CUSTOMERS_ID
o CONSTRAINT_INDEX_4
- nicht eindeutig
- CUSTOMERS_ID


SELECT A.ARTICLES_ID
FROM PUBLIC.ARTICLES A /* PUBLIC.ARTICLES_TABLE_SCAN */
INNER JOIN PUBLIC.CUSTOMERS C /* PUBLIC.PRIMARY_KEY_4: CUSTOMERS_ID = 250 */
WHERE (C.CUSTOMERS_ID = 250) AND (NOT EXISTS(SELECT ARTICLES_ID
FROM PUBLIC.ARTICLES_CUSTOMERS_PL1 ACP /* PUBLIC.PRIMARY_KEY_11: CUSTOMERS_ID = C.CUSTOMERS_ID AND ARTICLES_ID = A.ARTICLES_ID */
WHERE (ACP.CUSTOMERS_ID = C.CUSTOMERS_ID) AND (ACP.ARTICLES_ID = A.ARTICLES_ID)))

Ergebnis: 5704 ms (1. Durchgang), 2890 ms (2. Durchgang), 4047 ms (Tag spaeter)

2.)

CREATE TABLE ARTICLES_CUSTOMERS_PL2(
articles_id INT(11) NOT NULL,
customers_id INT(11) NOT NULL,
articles_price_netto DECIMAL(15,4) NOT NULL DEFAULT 0,
PRIMARY KEY(customers_id, articles_id),
FOREIGN KEY(articles_id) REFERENCES articles(articles_id),
FOREIGN KEY(customers_id) REFERENCES customers(customers_id));

Indexe (laut H2):
o PRIMARY_KEY_12
- eindeutig
- ARTICLES_ID, CUSTOMERS_ID
o CONSTRAINT_INDEX_5
- nicht eindeutig
_ ARTICLES_ID


SELECT A.ARTICLES_ID
FROM PUBLIC.ARTICLES A /* PUBLIC.ARTICLES_TABLE_SCAN */
INNER JOIN PUBLIC.CUSTOMERS C /* PUBLIC.PRIMARY_KEY_4: CUSTOMERS_ID = 350 */
WHERE (C.CUSTOMERS_ID = 350) AND (NOT EXISTS(SELECT ARTICLES_ID
FROM PUBLIC.ARTICLES_CUSTOMERS_PL2 ACP /* PUBLIC.PRIMARY_KEY_12: CUSTOMERS_ID = C.CUSTOMERS_ID AND ARTICLES_ID = A.ARTICLES_ID */
WHERE (ACP.CUSTOMERS_ID = C.CUSTOMERS_ID) AND (ACP.ARTICLES_ID = A.ARTICLES_ID)))

Ergebnis: 656 ms, 360 ms, 407 ms

3.)

CREATE TABLE ARTICLES_CUSTOMERS_PL3(
articles_id INT(11) NOT NULL,
customers_id INT(11) NOT NULL,
articles_price_netto DECIMAL(15,4) NOT NULL DEFAULT 0,
PRIMARY KEY(articles_id, customers_id),
FOREIGN KEY(articles_id) REFERENCES articles(articles_id),
FOREIGN KEY(customers_id) REFERENCES customers(customers_id));

CREATE INDEX IDX_PL3 ON ARTICLES_CUSTOMERS_PL3(articles_id, customers_id);

Indexe (laut H2):
o PRIMARY_KEY_13
- eindeutig
- ARTICLES_ID, CUSTOMERS_ID
o CONSTRAINT_INDEX_6
- nicht eindeutig
- CUSTOMERS_ID
o IDX_PL3
- eindeutig
- ARTICLES_ID, CUSTOMERS_ID


SELECT A.ARTICLES_ID
FROM PUBLIC.ARTICLES A /* PUBLIC.ARTICLES_TABLE_SCAN */
INNER JOIN PUBLIC.CUSTOMERS C /* PUBLIC.PRIMARY_KEY_4: CUSTOMERS_ID = 450 */
WHERE (C.CUSTOMERS_ID = 450) AND (NOT EXISTS(SELECT ARTICLES_ID
FROM PUBLIC.ARTICLES_CUSTOMERS_PL3 ACP /* PUBLIC.PRIMARY_KEY_13: CUSTOMERS_ID = C.CUSTOMERS_ID AND ARTICLES_ID = A.ARTICLES_ID */
WHERE (ACP.CUSTOMERS_ID = C.CUSTOMERS_ID) AND (ACP.ARTICLES_ID = A.ARTICLES_ID)))

Ergebnis: 6156 ms, 4125 ms, 5672 ms

4.)

CREATE TABLE ARTICLES_CUSTOMERS_PL4(
articles_id INT(11) NOT NULL,
customers_id INT(11) NOT NULL,
articles_price_netto DECIMAL(15,4) NOT NULL DEFAULT 0,
PRIMARY KEY(articles_id, customers_id),
FOREIGN KEY(articles_id) REFERENCES articles(articles_id),
FOREIGN KEY(customers_id) REFERENCES customers(customers_id));

CREATE INDEX IDX_PL4 ON ARTICLES_CUSTOMERS_PL4(customers_id, articles_id);

Indexe (laut H2):
o PRIMARY_KEY_14
- eindeutig
- ARTICLES_ID, CUSTOMERS_ID
o CONSTRAINT_INDEX_7
- nicht eindeutig
- CUSTOMERS_ID
o IDX_PL4
- eindeutig
- CUSTOMERS_ID, ARTICLES_ID


SELECT A.ARTICLES_ID
FROM PUBLIC.ARTICLES A /* PUBLIC.ARTICLES_TABLE_SCAN */
INNER JOIN PUBLIC.CUSTOMERS C /* PUBLIC.PRIMARY_KEY_4: CUSTOMERS_ID = 550 */
WHERE (C.CUSTOMERS_ID = 550) AND (NOT EXISTS(SELECT ARTICLES_ID
FROM PUBLIC.ARTICLES_CUSTOMERS_PL4 ACP /* PUBLIC.PRIMARY_KEY_14: CUSTOMERS_ID = C.CUSTOMERS_ID AND ARTICLES_ID = A.ARTICLES_ID */
WHERE (ACP.CUSTOMERS_ID = C.CUSTOMERS_ID) AND (ACP.ARTICLES_ID = A.ARTICLES_ID)))

Ergebnis: 4359 ms, 3563 ms, 4859 ms

5.)

CREATE TABLE ARTICLES_CUSTOMERS_PL5(
articles_id INT(11) NOT NULL,
customers_id INT(11) NOT NULL,
articles_price_netto DECIMAL(15,4) NOT NULL DEFAULT 0,
PRIMARY KEY(articles_id, customers_id),
FOREIGN KEY(articles_id) REFERENCES articles(articles_id),
FOREIGN KEY(customers_id) REFERENCES customers(customers_id));

CREATE UNIQUE INDEX IDX_PL5 ON ARTICLES_CUSTOMERS_PL5(customers_id,articles_id);
CREATE INDEX IDX_PL5_1 ON ARTICLES_CUSTOMERS_PL5(articles_id);
CREATE INDEX IDX_PL5_2 ON ARTICLES_CUSTOMERS_PL5(customers_id);

Indexe (laut H2):
o PRIMARY_KEY_15
- eindeutig
- ARTICLES_ID, CUSTOMERS_ID
o CONSTRAINT_INDEX_8
- nicht eindeutig
- CUSTOMERS_ID
o IDX_PL5
- eindeutig
- CUSTOMERS_ID, ARTICLES_ID
o IDX_PL5_1
- nicht eindeutig
- CUSTOMERS_ID
o IDX_PL5_2
- nicht eindeutig
- ARTICLES_ID


SELECT A.ARTICLES_ID
FROM PUBLIC.ARTICLES A /* PUBLIC.ARTICLES_TABLE_SCAN */
INNER JOIN PUBLIC.CUSTOMERS C /* PUBLIC.PRIMARY_KEY_4: CUSTOMERS_ID = 650 */
WHERE (C.CUSTOMERS_ID = 650) AND (NOT EXISTS(SELECT ARTICLES_ID
FROM PUBLIC.ARTICLES_CUSTOMERS_PL5 ACP /* PUBLIC.PRIMARY_KEY_15: CUSTOMERS_ID = C.CUSTOMERS_ID AND ARTICLES_ID = A.ARTICLES_ID */
WHERE (ACP.CUSTOMERS_ID = C.CUSTOMERS_ID) AND (ACP.ARTICLES_ID = A.ARTICLES_ID)))

Ergebnis: 5172 ms,3875 ms, 6140ms

[jan61]

Moin,

was mir z. B. bei (1) auffällt: Es wird zwar auf CUSTOMERS_ID ein Index angelegt, aber nicht auf ARTICLES_ID (offenbar, weil die Spalte im Primary Key an 1. Stelle auftritt). Und dann erfolgt ein "Table Scan" auf die Artikel-Tabelle. Kannst Du da mal explizit einen Index drauf legen? H2 scheint etwas eigenwillig zu sein mit den Indizes ;-)

Sorry, für eine intensive Analyse aller Deiner Ergebnisse bin ich zu müde. Nur noch eine Frage: Wie sehen denn die Artikel-IDs aus im Vergleich zu den Kunden-IDs? Kann es sein, dass die Kundennummern besser differenzieren als die Artikel-IDs?

Jan

[Overlord04]

Hi!


was mir z. B. bei (1) auffällt: Es wird zwar auf CUSTOMERS_ID ein Index angelegt, aber nicht auf ARTICLES_ID (offenbar, weil die Spalte im Primary Key an 1. Stelle auftritt).

Ja genau. Das sieht man ja bei (2). Da passiert das ganze anders herum...


Und dann erfolgt ein "Table Scan" auf die Artikel-Tabelle. Kannst Du da mal explizit einen Index drauf legen?

Worauf jetzt genau? Auf die Artikel-Tabelle oder auf die Artikel-ID in der Rabatt-Tabelle? Letzteres hatte ich ja in den anderen Test-Tabellen gemacht.


H2 scheint etwas eigenwillig zu sein mit den Indizes ;-)

Allerdings. Das scheint mir auch so! Mhmm...


Wie sehen denn die Artikel-IDs aus im Vergleich zu den Kunden-IDs? Kann es sein, dass die Kundennummern besser differenzieren als die Artikel-IDs?

Besser differenzieren? Die sind eigentlich identisch angelegt. Da gibt es keine Unterschiede zu.


Sorry, für eine intensive Analyse aller Deiner Ergebnisse bin ich zu müde.

Schäm dich!! Ne, wäre ja noch schöner :) Bin schon super glücklich, wie Du (zusammen mit BLUESCREEN3D) mir geholfen hast/habt! Danke noch mal dafür!

[jan61]

Moin,

wie schauts denn mittlerweile aus? Würde mich echt interessieren.

Jan