Hello folks,
in our firm we are using H2 for our test suite to perform various of
integration tests for our applications. Recently, we updated to H2 1.4.196;
until the day of the update, we used an older version of H2 1.3.166.
We found out that the execution times of some test cases became too large
after the update. After a thorough investigation we could identify that the
performance drop is due to the execution of SELECT-queries with a huge
number of 'LEFT OUTER JOIN's. The execution of such statements became 5-6
times slower. Consequently, we tested, for example, those statements on
different H2 versions and found out that, for example, there is no
performance drop on any of the 1.3 versions, but on the latest 1.4 versions
(even 1.4.197) the performance issue is existent.
Here is one example of the statements that we've tested:
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.coId = 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
Does anybody know why there is such a sudden performance drop for such SQL
statements on H2 v1.4 and is there a fix for it? We tried, for example, to
execute the statements with different DB options (such as setting ANALYZE_AUTO
to 0, etc.) but we haven't observed any visible improvement in the
execution times. Further, we relaced all 'LEFT OUTER JOIN' with 'INNER
JOIN' and observed that the execution times improved. However, comparing
the execution times of the modified SQL statements in 1.4 and 1.3, we also
observed some performance drops in 1.4 even for those statements.
I would appreciate any help or suggestion.
Cheers
Ivaylo
--
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.
