Here's a bit of a corner-case, showing a prohibitive amount of calls to
MetaTable.checkIndex() and other methods within MetaTable.generateRows()
1. I've created a database schema with 15k tables (and 15k primary keys,
15k foreign keys) of this form:
DROP SCHEMA IF EXISTS large;
CREATE SCHEMA large;
CREATE TABLE large.t00000 (id INT, CONSTRAINT pk_00000 PRIMARY
KEY (id));
CREATE TABLE large.t00001 (id INT, prev_id INT, CONSTRAINT pk_00001 PRIMARY
KEY (id), CONSTRAINT fk_00001 FOREIGN KEY (prev_id) REFERENCES t00000(id));
CREATE TABLE large.t00002 (id INT, prev_id INT, CONSTRAINT pk_00002 PRIMARY
KEY (id), CONSTRAINT fk_00002 FOREIGN KEY (prev_id) REFERENCES t00001(id));
CREATE TABLE large.t00003 (id INT, prev_id INT, CONSTRAINT pk_00003 PRIMARY
KEY (id), CONSTRAINT fk_00003 FOREIGN KEY (prev_id) REFERENCES t00002(id));
-- etc, you get the idea
... Yes. A corner case ;-) I can provide you with the full script, if you
want.
2. I've run the following query here. This query runs forever:
SELECT "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FK_NAME",
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_NAME",
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA",
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKCOLUMN_NAME",
"INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_NAME",
"INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_SCHEMA"
FROM "INFORMATION_SCHEMA"."CROSS_REFERENCES"
JOIN "INFORMATION_SCHEMA"."CONSTRAINTS"
ON ("INFORMATION_SCHEMA"."CROSS_REFERENCES"."PK_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."UNIQUE_INDEX_NAME"
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."PKTABLE_NAME" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_NAME"
AND "INFORMATION_SCHEMA"."CROSS_REFERENCES"."PKTABLE_SCHEMA" =
"INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_SCHEMA")
WHERE "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA" IN ('LARGE')
ORDER BY "INFORMATION_SCHEMA"."CROSS_REFERENCES"."FKTABLE_SCHEMA" ASC,
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."FK_NAME" ASC,
"INFORMATION_SCHEMA"."CROSS_REFERENCES"."ORDINAL_POSITION" ASC
3. Profiling the above query seems to indicate, that the query results in a
cartesian product, even if join criteria might be useful for optimisation:
[image: Inline-Bild 1]
http://i.imgur.com/8gh6ld0.png
The time column is not accurate, as it includes profiling side-effects.
Also, the above data does not include the complete execution time.
But as you can see, the invocation count goes up sky-high within
generateRows(), which leads to a lot of overhead, mostly from within
checkIndex(). Is there any way that such queries can be optimised by
adding more selective criteria to avoid cartesian products? Or is this a
"bug" in H2's handling of INFORMATION_SCHEMA data?
Also, did I understand this correctly: The INFORMATION_SCHEMA is
implemented entirely in Java code, producing ad-hoc in-memory tables. There
are no physical tables with actual indexes that can be queried in a much
faster fashion...?
Cheers
Lukas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
