In memory query optimizations

[Ol Strom]

Hello,

I'd like to use H2 to parse and execute prepared queries, without storing tables. These queries are quiete simple (without any join, order, sub queries, ...).
Most of the time it is something like:

- SELECT a = 2 and b = 5;

- SELECT CASE WHEN a = 2 and b = 5 THEN 1 ELSE 0 END;

Here is what I do to achieve it:

1) Set up a database, session and H2 parser.
Should I use "jdbc:h2:mem:" ?

2) Prepare queries :
Format on my own prepared string queries. To do that I can use Parser.parseExpression() to explore the query and replace field names with '?'.

Prepare the query : Parser.prepare()

3) Executing the queries with new parameters:

Set manually the parameters in the Prepared statement : Parameter.setValue()
Execute the query : Prepared.query(1)

This works fine. However I'd like to know what are the H2/system parameters to set in order to optimize the performances.
I guess the following parameters could be useful to set:
MV_STORE, MVCC, MODE, MAX_MEMORY_ROWS, CACHE_SIZE, CACHE_TYPE=SOFT_LRU, ..

Regards,

[Noel Grandin]

Trying to use H2's internals directly is not a good place to start.

Rather use the JDBC API.

I wouldn't bother setting any parameters initially unless you run into a problem, H2 is pretty fast out the box.

[0lst...@gmail.com]

I'm not sure to understand.
For my use case I cannot rely on any schemas. This is why I use H2 mainly to figure out what are the fields to replace with values. H2 can infer it when parsing string expressions (ExpressionColumn objects are the fields to replace).

Br,