How do compiled queries in slick actually work?

Question

I am looking for a detailed explanation about the execution of compiled queries. I can't understand how they just compile once and the advantage behind their use

Answer 1

Assuming this question is about the usage, not the internal implementation of Compiled queries, here is my answer:

When you write a Slick query, Slick actually creates a data structure internally for all the involved expressions - an abstract syntax tree (AST). When you want to run this query, Slick takes the data structure and translates (or in other words compiles) it into a SQL string. This can be a fairly time intensive process taking more time than actually executing fast SQL queries on the DB. So ideally we shouldn't do this translation to SQL every single time the query needs to be executed. But how to avoid it? By caching the translated/compiled SQL query.

Slick could do something like only compile it the first time and cache it for the next time. But it doesn't, because that makes it harder for the user to reason about Slick's execution time, because the same code will be slow the first time, but faster later. (Also Slick would need to recognize queries when they are run a second time and lookup the SQL in some internal cache, which would complicate the implementation).

So instead Slick compiles the query every time, unless you explicitly cache it. This makes the behavior very predictable and ultimately easier. To cache it, you need to use Compiled and store the result in a place that will NOT be recomputed next time you need the query. So using a def like def q1 = Compiled(...) does not make much sense, because it would compile it every time. It should be a val or lazy val. Also you probably do not want to put that val into a class you instantiate multiple times. A good place instead is a val in a top-level Scala singleton object, which is only computed once and kept for the live time of the JVM.

So in other terms, Compiled does nothing magical. It only allows you to trigger Slick's Scala-to-SQL compilation explicitly and return a value that contains the SQL. Importantly, this allows to trigger compilation separately from actually executing the query, which allows you to compile once, but run it multiple times.