Query executed in memory is taking more time than when not executed in memory

[Vinod]

I have a query which is taking more time when executed in memory.

The details are:
Created a table
Created a few extra columns to achieve functional indexes.
Created indexes on these extra columns
Populated data
Executed test query.

Following are the stats:
Using the java program, when the query is executed in memory - it
takes 1500ms
Using the java program, when the query is executed not in memory - it
takes 400ms
When using the H2 console in browser(started from java program using
Server.main(null);), the query takes 160ms

I have created the sample java program(http://www.4shared.com/file/
TdVngWtZ/ImdbInMemoryPerfVsNonInMemoryP.html) with sample data. The
program is self explanatory and is following the steps as mentioned
above.

Kindly help me on what am I missing here.
In the actual application where I am trying to use IMDB, similar query
when executed in memory takes 2500ms, and non-inmemory takes 170ms.

[Vinod]

I would appreciate any help on this, as I am not able to make use of
H2 because of this pretty basic issue.

[Noel Grandin]

you are never calling commit on the connection

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>

[Vinod]

I tried by adding commit in the test method
populateData();
sharedConnection.commit();

But results are still the same:
- when run in memory - 1500ms
- when not run in memory - 426ms

[andreis]

> - when run in memory     - 1500ms
> - when not run in memory - 426ms

Due to Garbage Collector? Try setting -Xmx high enough to hold all
that data in memory.

[Vinod]

The total number of records is 1055.
I tried it with following xmx settings: -Xms1024m -Xmx2048m
Still the time taken to execute query in memory is 1500ms

[Vinod]

I wonder why nobody else has faced this issue or nobody has compared
the inMemory vs nonInMemory performance.

[Noel Grandin]

Since it happens with both imdb and h2, it is most likely a bug in your code

[Vinod]

I do not think there is a bug in the code.
Either it is an H2 issue or some H2 configuration.