H2 10x Slower Than HSQLDB And 6x Than Apache Derby For Specific ...

Skip to content Dismiss alert {{ message }} h2database / h2database Public

• Notifications You must be signed in to change notification settings
• Fork 1.3k
• Star 4.6k

• Code
• Issues 385
• Pull requests 27
• Actions
• Projects
• Security

  Uh oh!

  There was an error while loading. Please reload this page.

• Insights

Additional navigation options

H2 10x slower than HSQLDB and 6x than Apache Derby for specific query with GROUP BY and DISTINCT subquery #1097New issueCopy linkNew issueCopy linkClosedClosedH2 10x slower than HSQLDB and 6x than Apache Derby for specific query with GROUP BY and DISTINCT subquery#1097Copy link

Description

tbalashovopened on Apr 27, 2018Issue body actions

This is a benchmark based on real life query which shows some performance issue. Profiler shows several hotspots in org.h2.util.ValueHashMap class.

package test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; public class GroupByDistinctBenchmark { public static void main(String[] args) throws Exception { Connection c = DriverManager.getConnection("jdbc:h2:file:/tmp/test"); // Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/tmp/test"); // Connection c = DriverManager.getConnection("jdbc:derby:/tmp/test"); Statement st = c.createStatement(); // create and populate test table st.execute("create table tst (a double, b double)"); PreparedStatement ps = c.prepareStatement("insert into tst values (?, ?)"); for (int i = 1; i <= 3_000_000; i++) { ps.setDouble(1, i); ps.setDouble(2, i); ps.addBatch(); if (i % 10000 == 0) ps.executeBatch(); } ps.close(); st.execute("create index tst_a_idx on tst (a)"); // benchmark long start = System.nanoTime(); st.executeQuery("select a, sum(b) from (select distinct a, b from tst) as t group by a"); long elapsed = System.nanoTime() - start; System.out.println(elapsed / 1_000_000_000.0); c.close(); } }

Approximate timings on my pc:

• H2: 180s
• HSQLDB: 18s
• Derby: 30s

Metadata

Metadata

Assignees

No one assigned

Labels

No labelsNo labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions

You can’t perform that action at this time.