H2 EXTREMELY slow if WEB CONSOLE is not connected to Database!!!

[marcolopes]

H2 is not SLOW. Is one of the fastest RDBMS i have tested to date!

I need help explaining WHY i need to CONNECT to the database using the
WEB MANAGER (H2 console) to get the fantastic speed of this DB
engine... I accidentally tested my app when CONNECTED through the H2
WEB console "et voilá", problem solved.

Why??? i'm puzzled :-\

Thanks.

NOTE: App is JAVA Eclipse RCP platform + Datanucleus 2.x persistence
engine (JDO). No problems at all using MySQL and PostgreSQL (or even
MS SQL Server).

[Sergi Vladykin]

Hi,

The first thing comes to my mind is that H2 by default closes database
if there is no open connections. May be your application somehow
closes connection after each database operation? Web console keep its
connection open all the time and prevents database from being closed
and reopened each time.

regards,
Sergi

[marcolopes]

Thanks for the reply.

Is there a way of keeping the database opened (or a keep alive
setting) ?

On Nov 23, 7:37 pm, Sergi Vladykin <sergi.vlady...@gmail.com> wrote:
> Hi,
>
> The first thing comes to my mind is that H2 by default closes database
> if there is no open connections. May be your application somehow
> closes connection after each database operation? Web console keep its
> connection open all the time and prevents database from being closed
> and reopened each time.
>
> regards,
> Sergi
>
> On 23 ноя, 21:49, marcolopes <marcolo...@netc.pt> wrote:
>
> > H2 is not SLOW. Is one of the fastest RDBMS i have tested to date!
>
> > I need help explaining WHY i need to CONNECT to the database using the
> > WEB MANAGER (H2 console) to get the fantastic speed of this DB
> > engine... I accidentally tested my app when CONNECTED through the H2
> > WEB console "et voilá", problem solved.
>
> > Why??? i'm puzzled :-\
>
> > Thanks.
>

> > NOTE: App isJAVA EclipseRCP platform + Datanucleus 2.x persistence

[Sergi Vladykin]

See http://h2database.com/html/grammar.html#set_db_close_delay

[marcolopes]

Thanks! That solved the problem (at least for now!)

Can you please tell me:

if i set the value on the console, where is the value "stored"?
And if i set it through the Connection URL, does it get "stored" the
same way?
How can i consult the actual value of the "parameter"?

On Nov 23, 8:15 pm, Sergi Vladykin <sergi.vlady...@gmail.com> wrote:
> Seehttp://h2database.com/html/grammar.html#set_db_close_delay

[Sergi Vladykin]

Documentation says that this setting is persistent.You can get its
value using this query
select * from INFORMATION_SCHEMA.SETTINGS where name =
'DB_CLOSE_ON_EXIT'

But it seems to me that it would be better to dont rely on such hacky
options and use connection pool or something like. This will impove
performance not only for H2 but for other databases too.

[marcolopes]

Yes! You are right.
I'm trying to implement connection Pooling for over a year now, but i
have terrible issues with class loading under Eclipse RCP Osgi
environment. I use DataNucleus for persistence, and tried DBCP, c3p0,
proxool, and even the recent BoneCP. NONE can be loaded under OSGI +
Datanucleus.

The next release of DN will have DBCP integrated, so i will use
Pooling as soon as possible.

For now, this is just a "workaround".

Thanks!

[Thomas Mueller]

Hi,

This is a relatively common problem. I will add the following paragraph to the FAQ:

== H2 is Very Slow ==

By default, H2 closes the database when the last connection is closed. If your application closes the only connection after each operation, the database is opened and closed a lot, which is quite slow. There are multiple ways to solve this problem, see Database Performance Tuning (http://h2database.com/html/performance.html#database_performance_tuning)

Regards,
Thomas