This Gist is related to the article The Rough Experience with Slick which points out performance problems with a Slick DSL query with 3 joins. The article is discussed on Linkedin and in the Slick newsgroup
I've tried this query on Slick 3.0M1 for Postgresql.
The query that uses the join DSL expression
val salesQuery = {
val salesJoin = sales join purchasers join products join suppliers on {
case (((sale, purchaser), product), supplier) =>
sale.productId === product.id &&
sale.purchaserId === purchaser.id &&
product.supplierId === supplier.id
}
def query(minTotal: Rep[BigDecimal]) = for {
(((sale, purchaser), product), supplier) <- salesJoin
if sale.total >= minTotal
} yield (purchaser.name, supplier.name, product.name, sale.total)
Compiled(query _)
}results in the following SQL query that contains 7 select statements and 3 inner join ... on (I had to do some indenting by hand):
select x2.x3, x4.x5, x2.x6, x2.x7
from
(select x8.x9 as x10, x8.x11 as x12, x8.x13 as x14, x8.x15 as x7, x8.x16 as x17, x8.x18 as x3, x8.x19 as x20, x21.x22 as x23, x21.x24 as x25, x21.x26 as x6
from
(select x27.x28 as x9, x27.x29 as x11, x27.x30 as x13, x27.x31 as x15, x32.x33 as x16, x32.x34 as x18, x32.x35 as x19
from
(select x36."id" as x28, x36."purchaser_id" as x29, x36."product_id" as x30, x36."total" as x31
from "sale" x36)
x27 inner join
(select x37."id" as x33, x37."name" as x34, x37."address" as x35
from "purchaser" x37)
x32 on 1=1
)
x8 inner join
(select x38."id" as x22, x38."supplier_id" as x24, x38."name" as x26
from "product" x38)
x21 on 1=1)
x2 inner join
(select x39."id" as x40, x39."name" as x5, x39."address" as x41
from "supplier" x39)
x4 on ((x2.x14 = x2.x23) and (x2.x12 = x2.x17)) and (x2.x25 = x4.x40)
where x2.x7 >= ?This query on the other hand does not use the join DSL term and results in a SQL equijoin query that's much closer to what one naïvely would expect. This is the Scala code for the query
val salesQuery3 ={
val salesJoin = for {
sa <- sales
pu <- purchasers if pu.id === sa.purchaserId
pr <- products if pr.id === sa.productId
su <- suppliers if su.id === pr.supplierId
} yield (sa,pu,pr,su)
def query(minTotal: Rep[BigDecimal]) = for {
(sale,purchaser,product,supplier) <- salesJoin
if sale.total >= minTotal
} yield (purchaser.name, supplier.name, product.name, sale.total)
Compiled(query _)
}and it results in this SQL:
select x2."name", x3."name", x4."name", x5."total"
from "sale" x5, "purchaser" x2, "product" x4, "supplier" x3
where (((x2."id" = x5."purchaser_id")
and (x4."id" = x5."product_id"))
and (x3."id" = x4."supplier_id"))
and (x5."total" >= ?)Btw this is the full code I used to create these results on Slick 3.0M1:
import scala.concurrent.{Future, Await}
import scala.concurrent.duration._
import scala.concurrent.ExecutionContext.Implicits.global
import scala.reflect.ClassTag
import scala.slick.driver.PostgresDriver.api._
import scala.slick.lifted.{Shape, MappedProjection, ProvenShape}
object SlickJoin extends App {
val suppliers = TableQuery[Suppliers]
val purchasers = TableQuery[Purchasers]
val products = TableQuery[Products]
val sales = TableQuery[Sales]
val db = Database.forURL(url = "jdbc:postgresql://localhost/test?user=postgres", driver = "org.postgresql.Driver")
val schema = (suppliers.schema ++ purchasers.schema).create
val insertSuppliers = suppliers ++= Seq(
Supplier(32, "Supplier Inc", "Kirchestrasse 5, Berlin"),
Supplier(32, "Supplier Gmbh", "Hauptstrasse 5, München")
)
val fetchNames = suppliers.map(_.name)
val salesQuery = {
val salesJoin = sales join purchasers join products join suppliers on {
case (((sale, purchaser), product), supplier) =>
sale.productId === product.id &&
sale.purchaserId === purchaser.id &&
product.supplierId === supplier.id
}
def query(minTotal: Rep[BigDecimal]) = for {
(((sale, purchaser), product), supplier) <- salesJoin
if sale.total >= minTotal
} yield (purchaser.name, supplier.name, product.name, sale.total)
Compiled(query _)
}
val salesQuery2 ={
def query(minTotal:Rep[BigDecimal])= for {
sa <- sales if sa.total >= minTotal
pu <- purchasers if pu.id === sa.purchaserId
pr <- products if pr.id === sa.productId
su <- suppliers if su.id === pr.supplierId
} yield (pu.name, su.name,pr.name,sa.total)
Compiled(query _)
}
val salesQuery3 ={
val salesJoin = for {
sa <- sales
pu <- purchasers if pu.id === sa.purchaserId
pr <- products if pr.id === sa.productId
su <- suppliers if su.id === pr.supplierId
} yield (sa,pu,pr,su)
def query(minTotal: Rep[BigDecimal]) = for {
(sale,purchaser,product,supplier) <- salesJoin
if sale.total >= minTotal
} yield (purchaser.name, supplier.name, product.name, sale.total)
Compiled(query _)
}
def fetchSales(minTotal: BigDecimal) = salesQuery(minTotal)
println(salesQuery3(500).compiledQuery.getDumpInfo)
}
case class Supplier(id: Int, nasme: String, address: String)
class Suppliers(tag: Tag) extends Table[Supplier](tag, "supplier") {
def id = column[Int]("id", O.AutoInc, O.PrimaryKey)
def name = column[String]("name")
def address = column[String]("address")
def * = (id, name, address) <>(Supplier.tupled, Supplier.unapply)
}
case class Purchaser(id: Int, name: String, address: String)
class Purchasers(tag: Tag) extends Table[Purchaser](tag, "purchaser") {
def id = column[Int]("id", O.AutoInc, O.PrimaryKey)
def name = column[String]("name")
def address = column[String]("address")
def * : ProvenShape[Purchaser] = (id, name, address) <>(Purchaser.tupled, Purchaser.unapply)
}
case class Product(id: Int, supplierId: Int, name: String)
class Products(tag: Tag) extends Table[Product](tag, "product") {
def id = column[Int]("id", O.AutoInc, O.PrimaryKey)
def supplierId = column[Int]("supplier_id")
def name = column[String]("name")
def * = (id, supplierId, name) <>(Product.tupled, Product.unapply)
}
case class Sale(id: Int, purchaserId: Int, productId: Int, total: BigDecimal)
class Sales(tag: Tag) extends Table[Sale](tag, "sale") {
def id = column[Int]("id", O.AutoInc, O.PrimaryKey)
def purchaserId = column[Int]("purchaser_id")
def productId = column[Int]("product_id")
def total = column[BigDecimal]("total")
def * : ProvenShape[Sale] = (id, purchaserId, productId, total) <> (Sale.tupled, Sale.unapply)
}