How do compiled requests in slick really work?

Question

I am looking for a detailed explanation about compiled query execution. I can’t understand how they simply compile once and the advantage of using them

Answer 1

Assuming this question is about using rather than internally implementing compiled queries, here is my answer:

When you write a Slick request, Slick actually creates a data structure inside all the involved expressions - the 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 an SQL string. This can be a rather lengthy process, taking more time than executing fast SQL queries in the database. Therefore, ideally, we should not do this SQL translation every time a query is to be executed. But how to avoid this? Caching a translated / compiled SQL query.

Slick might do something like compiling for the first time and caching it the next time. But this is not so, because it makes it difficult for the user to speculate on the Slick runtime, because the same code will be slow for the first time, but faster later. (Also, Slick will have to recognize the queries when they will be executed a second time and look for SQL in some internal cache, which would complicate the implementation).

So, instead, Slick compiles the request each time, unless you explicitly cache it. This makes the behavior very predictable and ultimately simpler. To cache it, you need to use Compiled and save the result in a place that will NOT be recounted at the next request. Therefore, using def as def q1 = Compiled(...) does not make much sense, because it will compile it every time. It must be val or lazy val . Also, you probably do not want to put this val in a class that you create several times. A good place instead is val at the top level of the Scala singleton object , which is evaluated only once and saved for the JVM in real time.

Thus, in other terms, Compiled does nothing magical. This allows you to explicitly run Slick Scala -to-SQL compilation and return a value containing SQL. It is important to note that this allows you to initiate compilation separately from the actual execution of the request, which allows you to compile once, but run it several times.

Answer 2

The advantage is easy to explain: compiling queries takes time, both in Slick and on the database server. If you execute the same query many times, it compiles faster only once.

A slick should compile AST with collection operations into an SQL statement. (In fact, without compiled queries, you always need to create an AST first, but compared to compilation times, this is very fast.)

The database server should build a query execution plan. This means parsing the query, translating it into your own database operations and searching for optimizations based on the data layout (for example, which index to use). This part can be avoided even if you do not use compiled queries in Slick, simply using bind variables to always have the same SQL code for different sets of parameters. The database server stores a cache of recently used / compiled execution plans, so as long as the SQL statement is identical, the execution plan is only a hash search and does not need to be calculated again. Slick relies on such caching. There is no direct connection from Slick to the database server to reuse the old query.

Regarding how they are implemented, there is some additional complexity for handling streaming / non-streaming and compiled / application / ad-hoc requests in the same way, but an interesting entry point is in Compiled :

 implicit def function1IsCompilable[A , B <: Rep[_], P, U](implicit ashape: Shape[ColumnsShapeLevel, A, P, A], pshape: Shape[ColumnsShapeLevel, P, P, _], bexe: Executable[B, U]): Compilable[A => B, CompiledFunction[A => B, A , P, B, U]] = new Compilable[A => B, CompiledFunction[A => B, A, P, B, U]] { def compiled(raw: A => B, profile: BasicProfile) = new CompiledFunction[A => B, A, P, B, U](raw, identity[A => B], pshape.asInstanceOf[Shape[ColumnsShapeLevel, P, P, A]], profile) } 

This gives you an implicit Compilable object for each Function . Similar methods for phenomena 2-22 are automatically generated. Since individual parameters require only Shape , they can also be nested tuples, HList, or any custom type. (We still provide abstractions for all functions because it is syntactically more convenient to write, say, Function10 than a Function1 , taking Tuple10 as an argument.)

In Shape there is a method that exists only to support compiled functions:

 /** Build a packed representation containing QueryParameters that can extract * data from the unpacked representation later. * This method is not available for shapes where Mixed and Unpacked are * different types. */ def buildParams(extract: Any => Unpacked): Packed 

The "packed" view created by this method may contain an AST containing QueryParameter nodes with the correct type. At compile time, they are processed in the same way as other literals, except that the actual values ​​are unknown. The extractor runs as identity at the top level and is refined to retrieve the elements of the record as needed. For example, if you have a Tuple2 parameter, the AST will end with two QueryParameter nodes that know how to extract the first and second parameters of the tuple at a later point.

This is a later point when a compiled query is applied. Executing such an AppliedCompiledFunction uses a precompiled SQL statement (or compiles it on the fly when you use it for the first time), and populates the statement parameters by streaming the value of the argument through extractors.