ScalaQuery's query/queryNA several times slower than JDBC?

Question

In the following performance tests of many queries, this timed JDBC code takes 500-600ms:

      val ids = queryNA[String]("select id from account limit 1000").list
      val stmt = session.conn.prepareStatement("select * from account where id = ?")
      debug.time() {
        for (id <- ids) {
          stmt.setString(1, id)
          stmt.executeQuery().next()
        }
      }

However, when using ScalaQuery, the time goes to >2s:

      val ids = queryNA[String]("select id from account limit 1000").list
      implicit val gr = GetResult(r => ())
      val q = query[String,Unit]("select * from account where id = ?")
      debug.time() {
        for (id <- ids) {
          q.first(id)
        }
      }

After debugging with server logs, this turns out to be due to the fact that the PreparedStatements are being repeatedly prepared and not reused.

This is in fact a performance issue that we've been hitting in our application code, so we're wondering if we're missing something regarding how to reuse prepared statements properly in ScalaQuery, or if dropping down to JDBC is the suggested workaround.

If that's actually the code you're running, you may want to make sure that, in your first example, HotSpot isn't just throwing away your loop, i.e., that it really is executing the statement 1000 times. — ig0774, Jul 17 '11 at 23:40

score 1 · Accepted Answer · edited May 19 '13 at 11:01

Got an answer from the scalaquery mailing list. This is just how ScalaQuery is designed - it assumes that you're something that provides statement pooling underneath:

Nowadays ScalaQuery always requests a new PreparedStatement from the Connection. There used to be a cache for PreparedStatements in early versions but I removed it because there are already good solutions for this problem. Every decent connection pool should have an option for PreparedStatement pooling. If you're using a Java EE server, it should have an integrated connection pool. For standalone applications, you can use something like http://sourceforge.net/projects/c3p0/

ScalaQuery's query/queryNA several times slower than JDBC?

1 Answers1