Cache Slick DBIO Actions

Question

I am trying to speed up "SELECT * FROM WHERE name=?" kind of queries in Play! + Scala app. I am using Play 2.4 + Scala 2.11 + play-slick-1.1.1 package. This package uses Slick-3.1 version.

My hypothesis was that slick generates Prepared statements from DBIO actions and they get executed. So I tried to cache them buy turning on flag cachePrepStmts=true However I still see "Preparing statement..." messages in the log which means that PS are not getting cached! How should one instructs slick to cache them?

If I run following code shouldn't the PS be cached at some point?

for (i <- 1 until 100) {
  Await.result(db.run(doctorsTable.filter(_.userName === name).result), 10 seconds)
}

Slick config is as follows:

slick.dbs.default {
  driver="slick.driver.MySQLDriver$"
  db {
    driver="com.mysql.jdbc.Driver"

    url="jdbc:mysql://localhost:3306/staging_db?useSSL=false&cachePrepStmts=true"

    user = "user"

    password = "passwd"

    numThreads = 1  // For not just one thread in HikariCP

    properties = {
      cachePrepStmts = true
      prepStmtCacheSize = 250
      prepStmtCacheSqlLimit = 2048
    }
  }

}

Update 1

I tried following as per @pawel's suggestion of using compiled queries:

val compiledQuery = Compiled { name: Rep[String] =>
  doctorsTable.filter(_.userName === name)
}


val stTime = TimeUtil.getUtcTime
for (i <- 1 until 100) {
  FutureUtils.blockFuture(db.compiledQuery(name).result), 10)
}
val endTime = TimeUtil.getUtcTime - stTime
Logger.info(s"Time Taken HERE $endTime")

In my logs I still see statement like:

2017-01-16 21:34:00,510 DEBUG [db-1] s.j.J.statement [?:?] Preparing statement: select ...

Also timing of this is also remains the same. What is the desired output? Should I not see these statements anymore? How can I verify if Prepared statements are indeed reused.

Paul Dolega Paul Dolega · Accepted Answer · 2017-01-15T11:25:16

You need to use Compiled queries - which are exactly doing what you want.

Just change above code to:

val compiledQuery = Compiled { name: Rep[String] =>
    doctorsTable.filter(_.userName === name)
}

for (i <- 1 until 100) {
  Await.result(db.run(compiledQuery(name).result), 10 seconds)
}

I extracted above name as a parameter (because you usually want to change some parameters in your PreparedStatements) but that's definitely an optional part.

For further information you can refer to: http://slick.lightbend.com/doc/3.1.0/queries.html#compiled-queries

Cache Slick DBIO Actions

2 Answers