Is there anyway to optimize performance for query ...

# exposed

[Viet Hoang]

Is there anyway to optimize performance for query with multiple join clauses like this one ?

Table1.leftJoin(otherTable = Table2, onColumn = { Table1.id }, otherColumn = { Table2.table1Id })
      .leftJoin(otherTable = Table3, onColumn = { Table1.table3Id }, otherColumn = { Table3.id })
      .join(otherTable = Table4, joinType = JoinType.LEFT, additionalConstraint = { (Table1.table5Id eq Table4.table5Id) and (Table1.table3Id eq Table4.table3Id) })
      .leftJoin(otherTable = Table5, onColumn = { Table1.table5Id }, otherColumn = { Table5.id })
      .leftJoin(otherTable = Table6, onColumn = { Table1.table5Id }, otherColumn = { Table6.table5Id })
      .leftJoin(otherTable = Table7, onColumn = { Table3.table7Id }, otherColumn = { Table7.id })
      .leftJoin(otherTable = Table8, onColumn = { Table7.table8Id }, otherColumn = { Table8.id })
      .leftJoin(otherTable = Table9, onColumn = { Table5.table9Id }, otherColumn = { Table9.id] })
      .leftJoin(otherTable = Table10, onColumn = { Table2.table10Id }, otherColumn = { Table10.id })
      .leftJoin(otherTable = Table11, onColumn = { Table2.table11Id }, otherColumn = { Table11.id })
      .leftJoin(otherTable = Table12, onColumn = { Table2.table12Id }, otherColumn = { Table12.id })
      .select {
          println("JOIN: " + (System.currentTimeMillis() - start)/1000)
          start =  System.currentTimeMillis()
          Table1.id inList ids 
      }
      .map {
          println("EXECUTE: " + (System.currentTimeMillis() - start)/1000)
          it.toMasterData()
      }

It took roughly 3 seconds just for generating query when executed in my local environment.

[tapac]

Are you sure that the problem in generating SQL and not in the query itself (missing indices, a lot of data to fetch as I do not see slice at all)?

[Viet Hoang]

Sorry for the late response.
If my understand is correct, statements inside select{ .... } are called before query is executed, and the ones inside map{....} are called after. When i executed it, i got

JOIN: 3
EXECUTE: 0

so i think the problem is in generating SQL.

[tapac]

No, generating SQL happens "between" select and map when creating iterator for map based on Query.

Maybe there is some performance problems with a lot of table joins but without a CPU/Memory profiles it's impossible to find out the root cause.