Why is Slick preparing this statement for every execution?

[fayvor]

I'm using lifted embedding, and have tried to reduce this example to its essence (although I've removed the db creds).  Troubleshooting performance for this basic setup I was surprised to see from the logs that slick appears to be preparing an identical statement for every execution.  Am I using the session and queries correctly here?  Dependencies, code, and log output below.

Thanks,

-Fayvor

-----

scalaVersion := "2.10.1"

"com.typesafe.slick" %% "slick" % "1.0.1-RC1",

"postgresql" % "postgresql" % "9.1-901.jdbc4",

-----

import scala.slick.driver.PostgresDriver.simple._

trait MbNameDAL {

  case class MbName(id: Int, name: String)

  object MbName {

    val byIdQ = for {

      id <- Parameters[(Int)]

      x <- MbNameTable if x.id is id

    } yield x

    def byId(id: Int)(implicit session: Session): MbName = byIdQ.first(id)

  }

  object MbNameTable extends Table[MbName]("artist_name") {

    def id = column[Int]("id", O.PrimaryKey)

    def name = column[String]("name")

    def * = id ~ name <> (MbName.apply _, MbName.unapply _)

  }

}

import Database.threadLocalSession

object StandaloneExample extends App with MbNameDAL {

  val db = Database.forURL("jdbc:postgresql:_", "_", "_", driver = "org.postgresql.Driver")

  db withSession {

    println(MbName.byId(1))

    println(MbName.byId(1))

    println(MbName.byId(2))

  }

}

------

11:26:47.102 [main] DEBUG s.s.compiler.FixRowNumberOrdering - No row numbers to fix

11:26:47.102 [main] DEBUG scala.slick.compiler.QueryCompiler - After phase fixRowNumberOrdering: (no change)

11:26:47.119 [main] DEBUG scala.slick.session.BaseSession - Preparing statement: select s6."id", s6."name" from "artist_name" s6 where s6."id" = ?

MbName(1,Barsotti, Marcel)

11:26:47.399 [main] DEBUG scala.slick.session.BaseSession - Preparing statement: select s6."id", s6."name" from "artist_name" s6 where s6."id" = ?

MbName(1,Barsotti, Marcel)

11:26:47.446 [main] DEBUG scala.slick.session.BaseSession - Preparing statement: select s6."id", s6."name" from "artist_name" s6 where s6."id" = ?

MbName(2,Isamar Compania)

[Stefan Zeiger]

On 2013-09-27 11:37, fayvor wrote:

I'm using lifted embedding, and have tried to reduce this example to its essence (although I've removed the db creds).  Troubleshooting performance for this basic setup I was surprised to see from the logs that slick appears to be preparing an identical statement for every execution.  Am I using the session and queries correctly here?  Dependencies, code, and log output below.

Slick only caches the SQL string but creates a new PreparedStatement for it every time. The reason for that is that PreparedStatements need to be cached at the Session level which is orthogonal to the QueryTemplates. You have to use a connection pool with PreparedStatement caching for the best performance. The 2.0 manual will contain a warning to this effect.

--
Stefan Zeiger
Slick Tech Lead
Typesafe - Build Reactive Apps!
Twitter: @StefanZeiger

[Fayvor Love]

Thanks for the quick response, Stefan.  I will try a connection pool; looking at C3PO a la this blog post: http://fernandezpablo85.github.io/2013/04/07/slick_connection_pooling.html

Let me know if you think there are better alternatives.

Cheers,

-Fayvor

--
 
---
You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/ivVdTVSTlT0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.