Advantage of one connection per thread

[Sylvain]

Hello,

I have several process with multiple threads (from 8 to 16) that are connected to a H2 server via TCP. They all access the same database and only runs select query.

Right now, I have one single connection object that is kept open and shared across all threads. The queries may sometimes take longer than usual during peak of activity (e.g 16 thread running a select stmt on the same table - up to 10-15ms instead of < 5ms). It seems that having one connection per thread (via ThreadLocal variable) improves performances on a test I ran.

Is that the "best" configuration I could have in this case ? Is there anything else I could do to improve this ?

Thanks
Sylvain

[Ryan How]

I'm no expert, but a connection per thread sounds right. Or even better,
run a connection pool which will handle it all automatically. In most of
my web apps (smaller ones), I run a connection per request (which is
really per thread), and leave a control connection open for the entire
application. For larger I would use a connection pool.

To speed it up you could try MULTI_THREADED=TRUE (I think that is it,
just double check on the website), but it may not improve performance.

Cheers, Ryan

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/sNYGl1kEBZgJ.
> 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.