Help! Simple query with multiple LEFT JOINs taking too much time with Slick 3.0.1
Walgus
Hello,
I'm new to the Slick world and currently playing with Slick 3.0.1, got to say that it's been pretty hard to learn it, not sure if this is just me, but the documentation isn't quite newbie friendly I can say, lots of googling and githubbing involved to get to the Scala/Slick code you will see in a bit.
I've this SQL query I need it executed against an INNODB (mysql) database:
SELECT *
FROM article a
LEFT JOIN author au ON (a.authorID = au.id)
LEFT JOIN author pic ON (a.pictureID = pic.id)
LEFT JOIN game g ON (a.gameID = g.id)
LEFT JOIN picture gpic ON (g.pictureID = gpic.id)
LEFT JOIN company p ON (g.publisherID = p.id)
LEFT JOIN company d ON (g.developerID = d.id)
LEFT JOIN game_platform gp ON (g.id = gp.gameID)
LEFT JOIN platform pf ON (gp.platformID = pf.id)
LEFT JOIN game_sport gs ON (g.id = gs.gameID)
LEFT JOIN sport s ON (gs.sportID = s.id)
WHERE a.id = ?
Now, the thing is that, trying to get that query using the Slick DSL in scala, with something like the code below:
lazy protected val findByIDQueryWithJoins = for {
id <- Parameters[Int]
((((((((((article, author), articlePic), game), developer), publisher), gamePic), g2p), platform), g2s), sport) <-
articles.filter(_.id === id)
.joinLeft(authors).on(_.authorID === _.id)
.joinLeft(pictures).on(_._1.pictureID === _.id)
.joinLeft(games).on(_._1._1.gameID === _.id)
.joinLeft(companies).on(_._2.map(_.publisherID) === _.id)
.joinLeft(companies).on(_._1._2.flatMap(_.developerID) === _.id)
.joinLeft(pictures).on(_._1._1._2.flatMap(_.pictureID) === _.id)
.joinLeft(games2platforms).on(_._1._1._1._2.map(_.id) === _.gameID)
.joinLeft(platforms).on(_._2.map(_.platformID) === _.id)
.joinLeft(games2sports).on(_._1._1._1._1._1._2.map(_.id) === _.gameID)
.joinLeft(sports).on(_._2.map(_.sportID) === _.id)
} yield (article, author.map(_.nameAndSlug), articlePic.map(p => (p.id, p.fileHash, p.fileExtension)), game, gamePic.map(p => (p.id, p.fileHash, p.fileExtension)), developer.map(_.name), publisher.map(_.name), platform.map(_.name), sport.map(_.name))
Yeah, not the best looking code esp with all of those _._1._1._1 to associate the rows, I can live with that, for now at least. What i can't live with is the SQL code that get's generated by the Scala compiler, it's around 12500 characters long with many sub-select queries. On average the full generated query takes around 200ms compared the SQL code above taking only ~5ms, that's a huge difference I'm not used to.
So, I'm I don't know if i'm really bad at writing queries in Scala/Slick or Slick is really bad a generating the SQL code, even if it was relying on the Database Query Optimizer, I think it's a lot of work for the optimizer.
I've tried this on Slick 3.1.0-M2 as well but same results, even went as far as trying it on Amazon RDS Aurora (MySQL API) and I've got the same result.
I don't want to give up on it just yet but I've been battling this for quite some time now and this thought of going back to my previous architecture stack is really getting louder and louder, which I'm reeeeally tempted to if i'm going to start writing plain SQL queries again!
Anyways, thanks for having your time reading my first experience with Slick. I hope someone will shed some light and help me shut that evil voice :)
Walter.
Walgus
Stefan Zeiger
I've tried this on Slick 3.1.0-M2 as well but same results, even went as far as trying it on Amazon RDS Aurora (MySQL API) and I've got the same result.
--