Compiled Queries are a huge pain

[schmitch]

Hello, currently I'm using slick and I try to compile all queries, however I only do this, since the queries are really slow if I won't compiled them, they take a HUGE hit into performance.

However Compiling a query is sometimes really hard and should be improved. Instead of writing just a simple function like that:

db.run(tables.filter(_.id === id).result)

I need to write the following:

db.run(compiledGet(id).result)
def getQuery(id: Rep[Long]) = tables.filter(_.id === id)
val compiledGet = Compiled(getQuery _)

This is really overly verbose. I think that should definitely improved.

Without any improvements it's way easier to just use sql, sqlu and tsql to write any query since they won't need to be compiled and run already really fast.

[cvogt]

what do you suggest as a less verbose way to express it?

[schmitch]

Currently I have no idea how that could be better, however I think maybe it's better to have always Compiled Queries, except the user explicitly wan't non compiled, that would be an option. I mean slick already had a huge gain by introducing sql, sqlu, tsql and Future responses. Now if it would be less painfull to have fast queries, it would be the greatest "ORM-like" Library.

I mean without compiled queries people get performance hits by more than 5-10ms, which is huge really huge. The tables.filter query is 5 ms slower than the corresponding sql"SELECT * FROM tables WHERE id = $id"; Even when you need to manually map the response to the correct case class. Which takes more lines to write.

[nafg]

db.run(compiledGet(id).result)`
def getQuery(id: Rep[Long]) = tables.filter(_.id === id)
val compiledGet = Compiled(getQuery _)

You don't have to write it that way. You can just write

val compiledGet = Compiled { id: Rep[Long] => tables.filter(_.id === id) }

db.run(compiledGet(id).result)

That's just the way scala works. Compiled takes a function literal, it can be anonymous if you like.

[cvogt]

@schmitch Compiling by default would take away explicit control from the user when compilation happens and where the compilation result is stored. Right now, you are in control. You decide where you place the val, if you rely on Compiled's default lazy compilation or if you trigger it upfront. We consider that an advantage over compilation as a caching side-effect as ORMs may do it. I doubt the syntax can be any shorter than it is right now given this design choice. Closing this now, but feel free to reopen, if you have a good suggestion how.

[nafg]

Not sure, can you show me how to do it the verbose way?

On Mon, Jun 8, 2015, 4:50 AM Schmitt Christian [email protected] wrote:

@nafg https://github.com/nafg and how would one compile a simple SELECT

• FROM table; query? If I just try to write a Compiled { tables } it won't work.

— Reply to this email directly or view it on GitHub https://github.com/slick/slick/issues/1155#issuecomment-109914287.

[schmitch]

@cvogt how could I compile a query that takes no parameters??

[cvogt]

@szeiger?

[szeiger]

It should "just work" like any other query. Maybe a variance bug? I've fixed those for other corner cases. What's the compiler error?

On June 10, 2015 11:34:46 PM GMT+02:00, Jan Christopher Vogt [email protected] wrote:

@szeiger?

---

Reply to this email directly or view it on GitHub: https://github.com/slick/slick/issues/1155#issuecomment-110921535

Sent from my Android phone with K-9 Mail. Please excuse my brevity.

[schmitch]

Query:

    val compilde = Compiled {
      articles.sortBy(_.id.desc)
    }
    dbConfig.db.run(compilde.result)

Error:

type mismatch;
 found   : compilde.type (with underlying type slick.lifted.CompiledStreamingExecutable[slick.lifted.Query[de.envisia.article.daos.ArticleTable,de.envisia.article.daos.ArticleTable#TableElementType,Seq],Seq[de.envisia.article.daos.ArticleTable#TableElementType],de.envisia.article.daos.ArticleTable#TableElementType])
 required: ?{def result: ?}
Note that implicit conversions are not applicable because they are ambiguous:
 both method streamableCompiledQueryActionExtensionMethods in trait API of type [RU, EU](c: slick.lifted.StreamableCompiled[_, RU, EU])de.envisia.all.PostgresDriver.StreamingQueryActionExtensionMethods[RU,EU]
 and method runnableStreamableCompiledQueryActionExtensionMethods in trait API of type [R, RU, EU, C[_]](c: slick.lifted.RunnableCompiled[de.envisia.all.PostgresDriver.api.Query[R,EU,C],RU])de.envisia.all.PostgresDriver.StreamingQueryActionExtensionMethods[RU,EU]
 are possible conversion functions from compilde.type to ?{def result: ?}

Or if i put the result directly at the Query:

    val compilde = Compiled {
      articles.sortBy(_.id.desc).result
    }
    dbConfig.db.run(compilde)

I Get:

Computation of type de.envisia.all.PostgresDriver.StreamingDriverAction[Seq[de.envisia.article.daos.ArticleTable#TableElementType],de.envisia.article.daos.ArticleTable#TableElementType,slick.dbio.Effect.Read] cannot be compiled (as type C)

[schmitch]

Also a Huge impact is a bigger Query, where the where part will be dynamically generated. There is no way to have such a query compiled, ok there is a way if you make individual queries, based on the Where part, but that would take way longer (and will contain duplicated code) than just writing a string interpolated query with a splicing literal, however that has another drawback, such as that all the parts in the splicing literals aren't sql injection safe.

That's the part where slick definitely needs some improvement.

[cvogt]

@schmitch Slick's plain SQL interpolation is injection safe. Caching dynamically composed queries can indeed be tricky. You can use the conditions used to determine what query to build as cache keys and e.g. use a mutable Map from key to compiled query as the cache.

[schmitch]

@cvogt i know that the plain SQL interpolation is injection safe, however that is not safe:

val whereList = (s"username = '$username'" :: s"custome_id = $id" List())
val where = whereList.mkstring(" AND ")

val query = sql"SELECT * FROM table #$where;"

I mean that wouldn't make that much sense, however it would make more sense if some builts the list dynamically while username is a Option[String] and it should only appended when the option is non empty. However username is not sql safe it will definitly print an error while giving a query as $username

and yes something like the Mutable Map is something that i do currently, however the query is still really good even on the non compiled parts, however this query has two joins and if we need to have more data we would need to use Elasticsearch or another NoSQL solution anyway.

However would still be nice if we could chain sql"" especially when sql"" would have a mkstring function. This is really nice to build queries. And codegen makes it really easy to have both, the Functional Query and the Plain Queries.

[cvogt]

composing plain SQL strings sounds like a cool feature. can you add a separate issue for that? Or even better, a PR that implements it. This is an open source library :).

[schmitch]

hm i will definitly make an issue, however currently my time is really really limited. But i will take a look into it.

[schmitch]

I've created a new Issue at #1161 however there is still a lot of work to do before even implementing a single line of code since this feature is more than just "compose sql strings"

[v6ak]

I have some idea on a better support of compiled queries:

@compiledQuery def get(id: Rep[Long]) = tables.filter(_.id === id).result

would be compiled as:

private val compiledGet = tables.filter(_.id === id).result

def get(id: Rep[Long]) = compiledGet(id)

For creating some service class, it would be useful to have a simple way to define get(id: Long) instead of get(id: Rep[Long]), maybe through a different annotation.

[nafg]

You can pass a Long where a Rep[Long] is expected (if you have the implicits in scope)

On Fri, Jul 24, 2015, 4:26 PM Vít Šesták [email protected] wrote:

I have some idea on a better support of compiled queries:

@compiledQuery def get(id: Rep[Long]) = tables.filter(_.id === id).result

would be compiled as:

private val compiledGet = tables.filter(_.id === id).result

def get(id: Rep[Long]) = compiledGet(id)

For creating some service class, it would be useful to have a simple way to define get(id: Long) instead of get(id: Rep[Long]), maybe through a different annotation.

— Reply to this email directly or view it on GitHub https://github.com/slick/slick/issues/1155#issuecomment-124710800.

[v6ak]

That's true. However, this is not OK for some level of encapsulation. One might want to receive just Long and return just Future[ResultType]

[perotom]

I haven't found any example on where to store the compiled query. At the moment i am using:

class NotificationDAOJDBC @Inject()(dbConfigProvider: DatabaseConfigProvider) {

  import dbConfig.profile.api._

  /*--------- QUERIES ---------*/
  private def queryCount() = Identification.table.length

  /*--------- COMPILED ---------*/
  val compiledCount = Compiled(queryCount())

  def count(): Future[Int] =
      db.run(compiledCount.result)
}

I think the compiled val gets every time evaulated which is not good. Any tips where to put the cached queries?

[Starofall]

You could use some scala magic and use this:

val findById: UUID => Future[Option[Entity]] = {
      val query = Compiled((id: Rep[UUID]) => tableQuery.filter(_.id === id))
      id: UUID => db.run(query(id).result.headOption)
}

[mahesh2492]

Hi I am having kind of the same issue performance issue in compiled query and I am not getting an answer what should I do. I have posed the question in stackoverflow. Please anyone guide me on how can I reduce the time.

https://stackoverflow.com/questions/67380865/big-time-diff-in-slick-plain-sql-query-and-plain-sql-query-with-dynamic-keys

Thanks