Using a connection per query via Slick and Hikari

Question

I have a web application that runs some database queries to fulfil some request. I am using aka http along with Slick(with HikariCP). I have used for comprehension to make sure these queries run in parallel. However, when I run it I see that only 1 connection is used to run all queries. What I want is the queries to run in parallel on the db i.e. use a separate connection.

eventFilter : DBIO[] = ??
vendorFilter : DBIO[] = ??

val flags = for {
  event <- eventFilter
  vendor <- vendorFilter
} yield (event, vendor)

Here eventFilter and vendorFilter are database actions that would be run in separate threads. However they are using the same connection to the database. I want to use separate connections so that the queries could actually run in parallel. I am using HikariCP that has 20 connections in idle state. I was hoping I could use them.

Is my understanding correct? Any idea as to how I can implement this. Any changes in Hikari config that could help bring this change. Also please point out any drawbacks related with this approach. Thanks.

can you provide a code sample to show the current problem? – Ramón J Romero y Vigil Jan 03 '18 at 20:24 — Ramón J Romero y Vigil, Jan 03 '18 at 20:24

score 2 · Accepted Answer · answered Jan 15 '18 at 02:36

2

Remember that the for comprehension is just syntactic sugar for applications of map and flatMap. The for comprehension desugars into:

val flags = eventFilter.flatMap { event =>
  vendorFilter.map { vendor =>
    (event, vendor)
  }
}

From the documentation for flatMap:

Use the result produced by the successful execution of this action to compute and then run the next action in sequence. The resulting action fails if either this action, the computation, or the computed action fails.

So vendorFilter isn't executed until after eventFilter completes.

If you want the two filters to execute in parallel, you'll want something like:

val eventFut = db.run(eventFilter)
val vendorFut = db.run(vendorFilter)

eventFut.zip(vendorFut)

answered Jan 15 '18 at 02:36

Levi Ramsey

18,884
1
16
30

I have defined eventFilter and vendorFilter before the for block. As such these actions should be running in parallel. However, my issue is not this. The problem I'm facing is that these queries are being run in a single database connection(as such queries are being executed sequentially). How can I use multiple connections to run these queries. Does this make sense ? – user3294786 Jan 15 '18 at 08:25
A for comprehension doesn't run them in parallel, but in sequence, so it reuses the connection. The solution is then to not use a for comprehension over `DBIO`. – Levi Ramsey Jan 16 '18 at 00:15
`println(DateTime.now) val x = Future { Thread.sleep(5000) 1 } val y = Future { Thread.sleep(5000) 2 } val z = for { a <- x b <- y } yield (a+b) z.onComplete { x => println(DateTime.now + "-------------" + x.get) }` @LeviRamsey This example shows that both x,y run in separate threads if they are called before for block. – user3294786 Jan 17 '18 at 12:22
Yes, but a DBIO isn't a future until it's run. – Levi Ramsey Jan 17 '18 at 20:23
Yes. Missed that. Thanks – user3294786 Jan 19 '18 at 11:27

Using a connection per query via Slick and Hikari

1 Answers1