Hello,
I could generate a test case from our data model which can to some extent
reporduce the behavior of H2 v 1.4 that I mentioned above. I attatched two
SQL scripts. The first one creates the tables with part of the constraints
that the original tables in our test suite have. The second one is the
Select-statement on which one can show the performance differences between
1.4 and 1.3 for this statement.
For creating the database and executing the SQL scripts I use Squirrel (v
3.8.1). I created two aliases using the URL
'jdbc:h2:file:path/to/my/database'. I configured the first alias to use the
driver of H2v1.4 and the second one to use the driver of H2v1.3 (latest 1.3
version). Comparing the execution times of the Select-statements I observed
execution times of approximately 0.25 s for H2v1.4 and 0.09 for H2v1.3.
Although the differences for this test case are not significantly
different, executing the same Select-statement on our original data base I
could observe execution times of 1.5 s for H2v1.4 and 0.15s for H2v1.3. Of
course we have test cases that use much more complex Select-statements with
LEFT OUTER JOINs for which we observed execution times that were 5 times
slower than those on H2 v1.3.
At last, for the experiments I didn't change any of the H2 db settings
listed
here:
https://www.h2database.com/javadoc/org/h2/engine/DbSettings.html#OPTIMIZE_OR
Thank you for your help in advance.
Greetings
Ivaylo
Am Montag, 23. April 2018 09:59:47 UTC+2 schrieb Ivaylo Dobrikov:
>
> Hi Noel,
>
> thank you for the quick response. I've executed the statetement above with
> EXPLAIN ANALYZE in version 1.4 and version 1.3 and couldn't see any
> differences in the plan. However, the execution time in 1.4 was
> significantly greater than the execution time in 1.3. I am going now to
> generate a standalone test case and will submit it as soon as I am ready.
>
> Am Freitag, 20. April 2018 20:36:30 UTC+2 schrieb Noel Grandin:
>>
>> EXPLAIN ANALYZE will show you what plan the db is executing, something in
>> the planner is generating a worse plan now.
>>
>> Your best bet is to generate a standalone test case for us to look at.
>>
>>
>
--
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
CREATE TABLE TableA (
aId int,
PRIMARY KEY (aId));
CREATE TABLE TableD (
dId int,
PRIMARY KEY (dId));
CREATE TABLE TableCur (
curId int,
PRIMARY KEY (curId));
CREATE TABLE TableACL (
acId int,
PRIMARY KEY (acId));
CREATE TABLE TableFCV (
fcId int,
PRIMARY KEY (fcId));
CREATE TABLE TableMP (
mpId int,
aId int,
dId int,
pcId int,
rcId int,
acId int,
fc1Id int,
fc2Id int,
fc3Id int,
fc4Id int,
fc5Id int,
PRIMARY KEY (mpId),
CONSTRAINT FK_TableMP1 FOREIGN KEY (aId)
REFERENCES TableA(aId),
CONSTRAINT FK_TableMP2 FOREIGN KEY (dId)
REFERENCES TableD(dId),
CONSTRAINT FK_TableMP3 FOREIGN KEY (pcId)
REFERENCES TableCur(curId),
CONSTRAINT FK_TableMP4 FOREIGN KEY (rcId)
REFERENCES TableCur(curId),
CONSTRAINT FK_TableMP5 FOREIGN KEY (acId)
REFERENCES TableFCV(fcId),
CONSTRAINT FK_TableMP6 FOREIGN KEY (fc1Id)
REFERENCES TableFCV(fcId),
CONSTRAINT FK_TableMP7 FOREIGN KEY (fc2Id)
REFERENCES TableFCV(fcId),
CONSTRAINT FK_TableMP8 FOREIGN KEY (fc3Id)
REFERENCES TableFCV(fcId),
CONSTRAINT FK_TableMP9 FOREIGN KEY (fc4Id)
REFERENCES TableFCV(fcId),
CONSTRAINT FK_TableMP10 FOREIGN KEY (fc5Id)
REFERENCES TableFCV(fcId)
);
CREATE TABLE TableP (
pId int,
mpId int,
PRIMARY KEY (pId),
CONSTRAINT FK_TableP FOREIGN KEY (mpId)
REFERENCES TableMP(mpId));
CREATE TABLE TableLT (
lId int,
pId int,
PRIMARY KEY (lId),
CONSTRAINT FK_TableLT FOREIGN KEY (pId)
REFERENCES TableP(pId));
CREATE TABLE TableAGGTYPE (
atId int,
PRIMARY KEY (atId));
CREATE TABLE TableAGGTYPET (
atId int,
PRIMARY KEY (atId),
CONSTRAINT FK_TableAGGTYPET FOREIGN KEY (atId)
REFERENCES TableAGGTYPE(atId));
CREATE TABLE TableCALCP (
cpId int);
CREATE TABLE TableRV (
lId int,
cpId int,
pId int,
atId int,
rvId int,
PRIMARY KEY (rvId),
CONSTRAINT FK_TableRV1 FOREIGN KEY (cpId)
REFERENCES TableCALCP(cpId),
CONSTRAINT FK_TableRV2 FOREIGN KEY (pId)
REFERENCES TableP(pId),
CONSTRAINT FK_TableRV3 FOREIGN KEY (lId)
REFERENCES TableLT(lId),
CONSTRAINT FK_TableRV4 FOREIGN KEY (atId)
REFERENCES TableAGGTYPET(atId),
CONSTRAINT UC_RV UNIQUE (lId,cpId,pId,atId)
);
CREATE TABLE TableDIUSERS (
uId int,
PRIMARY KEY (uId));
CREATE TABLE TableDEBUGI (
rvId int,
psId int,
vrId int,
vpId int,
pcId int,
spsId int,
iId int,
uId int,
vcId int,
CONSTRAINT FK_TableDEBUGI1 FOREIGN KEY (rvId)
REFERENCES TableRV(rvId),
CONSTRAINT FK_TableDEBUGI2 FOREIGN KEY (uId)
REFERENCES TableDIUSERS(uId),
CONSTRAINT UC_DEBUGI UNIQUE (vrId,vpId,pcId,spsId,iId,vcId,vrId));
CREATE TABLE TableS (
sId int,
PRIMARY KEY (sId));
CREATE TABLE TablePST (
sId int,
PRIMARY KEY (sId),
CONSTRAINT FK_TablePST FOREIGN KEY (sId)
REFERENCES TableS(sId));
CREATE TABLE TableDISPST (
sId int,
PRIMARY KEY (sId),
CONSTRAINT FK_TableDISPST FOREIGN KEY (sId)
REFERENCES TableS(sId));
CREATE TABLE TablePC (
pcId int,
PRIMARY KEY (pcId));
CREATE TABLE TablePCT (
pcId int,
PRIMARY KEY (pcId),
CONSTRAINT FK_TablePCT FOREIGN KEY (pcId)
REFERENCES TablePC(pcId));
CREATE TABLE TableDIVR (
vrId int,
PRIMARY KEY (vrId));
CREATE TABLE TableDIVRT (
vrId int,
PRIMARY KEY (vrId),
CONSTRAINT FK_TableDIVRT FOREIGN KEY (vrId)
REFERENCES TableDIVR(vrId));
CREATE TABLE TableDIVP (
vpId int,
PRIMARY KEY (vpId));
CREATE TABLE TableDIVPT (
vpId int,
PRIMARY KEY (vpId),
CONSTRAINT FK_TableDIVPT FOREIGN KEY (vpId)
REFERENCES TableDIVP(vpId));
CREATE TABLE TableDIVC (
vcId int,
PRIMARY KEY (vcId));
CREATE TABLE TableDIVCT (
vcId int,
PRIMARY KEY (vcId),
CONSTRAINT FK_TableDIVCT FOREIGN KEY (vcId)
REFERENCES TableDIVC(vcId));
CREATE TABLE TableDII (
viId int,
PRIMARY KEY (viId));
CREATE TABLE TableDIIT (
viId int,
PRIMARY KEY (viId),
CONSTRAINT FK_TableDIIT FOREIGN KEY (viId)
REFERENCES TableDII(viId));
CREATE TABLE TableFTS (
tsId int,
PRIMARY KEY (tsId));
CREATE TABLE TableCPFO (
cpId int,
tsId int,
PRIMARY KEY (cpId),
CONSTRAINT FK_TableCPFO FOREIGN KEY (tsId)
REFERENCES TableFTS(tsId));
CREATE TABLE TableFTST (
tsId int,
PRIMARY KEY (tsId),
CONSTRAINT FK_TableFTST FOREIGN KEY (tsId)
REFERENCES TableFTS(tsId));
CREATE TABLE TableCPBO (
cpId int,
PRIMARY KEY (cpId));
SELECT *
FROM TableLT LT
JOIN TableP P
ON P.pId = LT.pId
JOIN TableMP MP
ON MP.mpId = P.mpId
JOIN TableA A
ON MP.aId = A.aId
JOIN TableD D
ON MP.dId = D.dId
JOIN TableRV RV
ON RV.lId = LT.lId
JOIN TableCALCP CALCP
ON CALCP.cpId = RV.cpId
LEFT OUTER JOIN TableAGGTYPET AGGTYPET
ON AGGTYPET.atId = RV.atId
LEFT OUTER JOIN TableDEBUGI DEBUGI
ON DEBUGI.rvId = RV.rvId
LEFT OUTER JOIN TablePST PST
ON PST.sId = DEBUGI.psId
LEFT OUTER JOIN TablePCT PCT
ON PCT.pcId = DEBUGI.pcId
LEFT OUTER JOIN TableDIVRT DIVRT
ON DIVRT.vrId = DEBUGI.vrId
LEFT OUTER JOIN TableDIVPT DIVPT
ON DIVPT.vpId = DEBUGI.vpId
LEFT OUTER JOIN TableDISPST DISPST
ON DISPST.sId = DEBUGI.spsId
LEFT OUTER JOIN TableDIVCT DIVCT
ON DIVCT.vcId = DEBUGI.vcId
LEFT OUTER JOIN TableDIIT DIIT
ON DIIT.viId = DEBUGI.iId
LEFT OUTER JOIN TableDIUSERS DIUSERS
ON DIUSERS.uId = DEBUGI.uId
LEFT OUTER JOIN TableCPFO CPFO
ON CPFO.cpId = RV.cpId
LEFT OUTER JOIN TableFTS FTS
ON FTS.tsId = CPFO.tsId
LEFT OUTER JOIN TableFTST FTST
ON FTST.tsId = FTS.tsId
LEFT OUTER JOIN TableCPBO CPBO
ON CPBO.cpId = RV.cpId
