2

I need to do small (but frequent) operations on my database, from one of my api methods. When I try wrapping them into "withSession" each time, I get terrible performance.

db withSession {
  SomeTable.insert(a,b)
}

Running the above example 100 times takes 22 seconds. Running them all in a single session is instantaneous.

Is there a way to re-use the session in subsequent function invocations?

Rogach
  • 26,050
  • 21
  • 93
  • 172

2 Answers2

3

Do you have some type of connection pooling (see JDBC Connection Pooling: Connection Reuse?)? If not you'll be using a new connection for every withSession(...) and that is a very slow approach. See http://groups.google.com/group/scalaquery/browse_thread/thread/9c32a2211aa8cea9 for a description of how to use C3PO with ScalaQuery.

If you use a managed resource from an application server you'll usually get this for "free", but in stand-alone servers (for example jetty) you'll have to configure this yourself.

Community
  • 1
  • 1
thoredge
  • 12,237
  • 1
  • 40
  • 55
  • I don't think there is any connection pooling - I just access the in-memory hsql database. By the way - do those connections have some "expiry" time? Maybe I can open one connection and leave it opened all time? – Rogach Apr 30 '12 at 07:06
  • If it uses 22 seconds for 100 inserts it would seem that the hsql database have a huge overhead on connection instantiation. Expiry time is up to the server. This often represent a problem for connection pools; one way they solve it is by running a test query before handing out the connection, for example 'select 1'. – thoredge Apr 30 '12 at 08:31
1

I'm probably stating the way too obvious, but you could just put more calls inside the withSession block like:

db withSession {
  SomeTable.insert(a,b)
  SomeOtherTable.insert(a,b)
}

Alternately you can create an implicit session, do your business, then close it when you're done:

implicit val session = db.createSession
SomeTable.insert(a,b)
SomeOtherTable.insert(a,b)
session.close
jxstanford
  • 3,339
  • 3
  • 27
  • 39
  • Yes, that's really obvious :) I can't batch them up - the request to do something comes from other, non-database code, which shouldn't be bothered with database logic. But the idea with implicit session is good - can I open one session and leave it around? Won't it die after some time? – Rogach Apr 30 '12 at 07:09
  • The HSQLDB docs indicate that "an application that is not both multi-threaded and transactional, such as an application for recording user login and logout actions, does not need more than one connection. The connection can stay open indefinitely and reopened only when it is dropped due to network problems." So, it seems that you may be able to open one and keep it around for the life of the app. There are other potentially useful tips here: http://hsqldb.org/doc/guide/deployment-chapt.html#dec_managing_connections – jxstanford Apr 30 '12 at 13:56