What is the most efficient way to get single result in Slick?

Question

I wonder which of the following two lines is more efficient:

db.run(events (..filter/sort..) .take(1).result.head)

or

db.run(events (..filter/sort..) .result.head)

as I have discovered the .take comes from slick.lifted.Query

and .head from slick.profile.BasicStreamingAction

Answer 1

Below code which uses take(1) is efficient because take(1) translates to the SQL in sick and then query optimizer of the underlying relation database does the optimization to pick only the first row of the result.

db.run(events (..filter/sort..) .take(1).result.head)

But in case of only .head at the application layer, slick does not translate that code to SQL so, its not communicated to the underlying relational database. There by no optimization is done. When .head is done at application level one row will be selected from the rows returned by the relational db which match the criteria. Taking first row at app layer is clearly not efficient.

.take(1) will translate to LIMIT 1 in the SQL query.

.head does gives first element of the result set after querying the results at application layer (result set can be really huge and it can be very inefficient and very slow).

.take(1) will only give one row at database level. It is very performant and very efficient.

Answer 2

You should be using headOption, because it is not a good practice to use a head. it can blow your code if the result is empty.

db.run(events (..filter/sort..) .take(1).result.headOption)