Hello, I’ve stumbled upon a performance problem on H2 (all versions from 1.3.173) It concerns deep nested queries, and I have managed to extract a very simple test case.
// Create one table with one column, one row, containing one value CREATE TABLE MY_TABLE ( MY_NUMBER int ); INSERT INTO MY_TABLE VALUES (1); // Select using nested queries SELECT COL10 + 1 AS COL11 FROM ( SELECT COL9 + 1 AS COL10 FROM ( SELECT COL8 + 1 AS COL9 FROM ( SELECT COL7 + 1 AS COL8 FROM ( SELECT COL6 + 1 AS COL7 FROM ( SELECT COL5 + 1 AS COL6 FROM ( SELECT COL4 + 1 AS COL5 FROM ( SELECT COL3 + 1 AS COL4 FROM ( SELECT COL2 + 1 AS COL3 FROM ( SELECT COL1 + 1 AS COL2 FROM ( SELECT COL0 + 1 AS COL1 FROM ( SELECT MY_NUMBER AS COL0 FROM MY_TABLE) ) ) ) ) ) ) ) ) ) ); The benchmark for H2 is as follows : 12 nested queries (like above) : 1 minute 11 : 12.7 sec 10 : 2.9 sec 9 : 0.7 sec 8 : 0.16 sec Less than 8 : instantaneous As you can see, H2 takes exponentially more time when nested queries are added. On HSQLDB it is instantaneaous regardless of the number of nested queries. I've profiled the execution, and it seems all the computing time is spent in the query planning algorithm . Best regards, Quentin -- 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. For more options, visit https://groups.google.com/d/optout.
