Hi...
I've some problems with the response time of a more complex query in
the H2 DB. So how can I optimise my response time?
Some comparable tests with the MySql DB have shown much better
response times.
First things first!
I am using the actual H2 DB version (Version 1.2.134 (2010-04-23)).
My structure of the DB:
CREATE TABLE item (id INT PRIMARY KEY AUTO_INCREMENT, path VARCHAR);
CREATE TABLE node (fk_item INT, fk_parent INT, lft INT, rgt INT, leaf
BOOLEAN, FOREIGN KEY(fk_item) REFERENCES item(id), FOREIGN
KEY(fk_parent) REFERENCES node(fk_item));
My test data:
INSERT INTO item (id, path) values (1, 'root');
INSERT INTO item (id, path) values (2, 'file');
INSERT INTO item (id, path) values (23, 'vz1');
INSERT INTO item (id, path) values (45, 'vz2');
INSERT INTO item (id, path) values (29, 'vz1_1');
INSERT INTO item (id, path) values (39, 'vz1_2');
INSERT INTO item (id, path) values (31, 'bild1.png');
INSERT INTO item (id, path) values (35, 'bild2.png');
INSERT INTO item (id, path) values (41, 'bild.png');
INSERT INTO item (id, path) values (25, 'bild.png');
INSERT INTO item (id, path) values (47, 'bild.png');
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (1, null,
1, 36, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (2, 1,
16, 35, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (23, 2,
21, 34, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (45, 2,
17, 20, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (29, 23,
26, 31, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (39, 23,
22, 25, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (31, 29,
29, 30, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (35, 29,
27, 28, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (41, 39,
23, 24, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (25, 23,
32, 33, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (47, 45,
18, 19, true);
The data shown as a tree:
- root
- file
- vz1
- vz1_1
- bild1.png
- bild2.png
- vz1_2
- bild.png
- bild.png
- vz2
- bild.png
In Java the class Node extends from the class Item.
The values lft and rgt (the "Modified Preorder Tree Traversal"
algorithm) from the table node help me to substantiate my search.
(More information:
http://articles.sitepoint.com/print/hierarchical-data-database).
What I'm concerned about is that my more complex queries take much
longer with the H2 DB. I use the H2 DB in the embedded mode, but still
tried it with server mode too!
My test query:
SELECT cn2.fk_item, ci2.path
FROM node cn0, item ci0, node cn1, item ci1, node cn2, item ci2
WHERE ((ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path
like 'vz1_1'
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path
like 'bild%.png')
OR (ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path
like 'vz1_2'
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path
like 'bild%.png'));
In words:
Give me all files that match with the path
/file/**/vz1_1/bild*.png
or
/file/**/vz1_2/bild*.png
H2 response time:
(3 Datensätze, 1612 ms)
Reference value by the MySql DB:
Zeige Datensätze 0 - 2 (3 insgesamt, die Abfrage dauerte 0.0009 sek.)
Good to know:
SELECT cn2.fk_item, ci2.path
FROM node cn0, item ci0, node cn1, item ci1, node cn2, item ci2
WHERE (ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path
like 'vz1_1'
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path
like 'bild%.png');
H2 needs only 20ms.
Thanks a lot for any help!!! :-)
Nico
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
