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:
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);
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.)
Code:
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
Code:
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.)
Code:
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
Code:
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.)
Code:
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
Code:
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.)
Code:
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
Code:
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.)
Code:
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
Code:
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
Lesezeichen