Slick performance at large scale, optimistic locking, and STM

[Greg Zoller]

Hello,

I'm a longtime user of Scala w/Mongo but now need transactional abilities, so I'm shopping for a Scala-based solution.

I know there are several options out there but since Slick is Typesafe's offering, it makes sense to start there.

My needs are pretty large-scale... 10s or even 100s of millions of rows.

From what I read Slick doesn't yet support optimistic locking or STM.  Most of this info/articles related to Slick v1, not the 

new v2 series.  Is that still true?  Are these on the roadmap?  Is Slick "slow" relative to other options that may have these

features?

I've also looked at Activate, which looks really cool, but it yet support Scala 2.11.

Any thoughts or ideas appreciated.

Thanks,

Greg

[Christopher Vogt]

Hi Greg,

> From what I read Slick doesn't yet support optimistic locking or STM.
> Most of this info/articles related to Slick v1, not the
> new v2 series.
> Is that still true?

that is still true and it is our impression that these should be built
as libraries on top of Slick

> Are these on the roadmap?

Not at the moment. But we are primarily an open source project, not
primarily a commercial product (despite type-safe offering commercial
extensions and support) and open for contributions.

> Is Slick
> "slow" relative to other options that may have these
> features?

Slick is as fast as plain old JDBC. One additional cost is the time for
compiling Slick queries to SQL, but since you can cache them, that
overhead is constant per app instance. Also, the SQL the Slick compiler
generates is not in all cases optimized well enough by all dbms.
Especially with MySQL there are cases known where nested queries, which
Slick currently generates in some cases, stop MySQL from using indices
and lead to unnecessary table scans. In these cases you have to fall
back to hand-written SQL at the moment, but it is rather straight
forward to do so as Slick is used in the same way. More info on
http://slick.typesafe.com/doc/2.1.0/orm-to-slick.html

Also interesting
http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html

> I've also looked at Activate, which looks really cool, but it yet
> support Scala 2.11.

I haven't used Activate, so anybody please correct me if I am wrong.
From what I understood, Activate uses an STM as a local cache. So if you
do modifications and they become obsolete by other modifications (e.g.
overwriting values multiple times or inserting rows and deleting them
within a short time) you don't actually need to write them to the
database and save some queries making you effectively faster than using
JDBC in these cases. However that is only true, if you manage to write
back the accumulated changes without conflicts. If you do happen to have
conflicting changes it may take longer, because transactions have to be
replayed on top of the changed situation. This can take longer or giving
unfortunate configuration and number of merge conflicts... forever in
the worst case. I suspect it depends on your actual access patterns and
application distribution if STM means a win or a loss for you. I may be
off here. I would love to hear more about this from someone who knows
for sure.

Chris

[Andrew Gaydenko]

Hi, Chris,

In reality I have not found a way to cache ("prepare", "compile"), say, "in set" query. At the moment in critical places I use very dirty hack, for example, "preparing" has form:

  val (idsPrefix, idsSuffix) = {
    val sel = Q.filter(_.id inSet List(111L, 777L)).selectStatement
    val prefix = sel.substring(0, sel.indexOf("111"))
    val suffix = sel.substring(sel.indexOf("777") + 3)
    (prefix, suffix)
  }

with further prefix + real ids + suffix using in static query (Q here is a table query). The hack results in four times speed up of some critical queries in the project in hands. Is there some more legal way?

[Christopher Vogt]

That's right. inSet can't be compiled at the moment. If the number of
elements in the list is static, you can use === and || instead. If the
number of elements is dynamic, you can do it like this:

/** Contains pre-compiled inSet queries for various arities */
class PreCompiledInSeq[T,E,C[_],V](
q: Query[T,E,C[_]], c: Query[T,E,C[_]] => V
){
def condition[T](lhs: Column[T], args: Column[T]*)
= args.map(lhs === _).reduce(_ || _)

type C = Column[V]
val _1 = Compiled{(a1:C) => q.filter(q => condition(c(q),a1) }
val _2 = Compiled{
(a1:C,a2:C) => q.filter(q => condition(c(q),a1,a2) }
val _3 = Compiled{
(a1:C,a2:C,a3:C) => q.filter(q => condition(c(q),a1,a2,a3) }
...

def inSet( seq: Seq[Int] ) = seq match {
case Seq(a1) => q1(a1)
case Seq(a1,a2) => q2(a1,a2)
case Seq(a1,a2,a3) => q2(a1,a2,a3)
...
}
}

object QInSetCompiled extends PreCompiledInSet(Cars,_.id)

Q.inSet(Seq(1))
Q.inSet(Seq(9,3,4))

Voila, pre-compiled inseq. (I didn't compile this, please comment on the
gist for fixes https://gist.github.com/cvogt/e0d62be47f6ffa1f1407 )

You may want to call compiledDelete, compiledInsert, compiledQuery,
compiledUpdate to trigger pre-compilation immediately rather than
lazily. Also see
http://slick.typesafe.com/doc/2.1.0/api/#scala.slick.lifted.CompiledFunction

Chris

[Christopher Vogt]

Fixed the gist in some spots
https://gist.github.com/cvogt/e0d62be47f6ffa1f1407

[Andrew Gaydenko]

Chris, thanks! So at first I need to detect those use cases having strictly limited set size.

[Greg Zoller]

Thanks for this great response!  I'm fine w/JDBC-level performance.  It's a well-known thing form a performance/scale standpoint.

Good heads-up on the MySQL table scans.  I'll be mindful of that as we do use MySQL in our mix.

[Christopher Vogt]

> Thanks for this great response!

You are very welcome.

> Good heads-up on the MySQL table scans. I'll be mindful of that as we
> do use MySQL in our mix.

Some addendum to that: The SQL statements are logged or you can println
them using println(yourQuery.selectStatement). If you notice performance
problems, throw them into describe. If the query is the cause, consider
optimizing it by hand and using plain SQL to execute it.

Chris