0

I own an amount about 80 000 rows on 3 tables of database, every time I execute my code, it can take 4-5 minutes to display the result. Do you know if it's possible to reduce this time by adding whatever ?

  def findAirportAndRunwayByCountry(code: String)(implicit session : DBSession = AutoSession) : List[(Airports,Runway)] =    {
    val (c, a, r) = (Country.syntax("c"), Airports.syntax("a"), Runway.syntax("r"))
    withSQL { select.from(Airports as a).innerJoin(Runway as r).on(r.ID, a.ID).innerJoin(Country as c).on(c.Code, a.Country).where.eq(c.Code, code).or.like(c.Name, code + "%") }.map(rs => (Airports(rs),Runway(rs))).list.apply()
  }

This is the code taking the time described (~4-5 mins)

Gaël J
  • 11,274
  • 4
  • 17
  • 32
Kev74
  • 103
  • 7
  • 1
    Have you tried the same SQL query directly on the database? How much time does it take? What are the indexes and foreign keys? – Gaël J Dec 27 '22 at 07:31
  • Assuming the joins are made on foreign keys, the main driver of performance will be the where clause on `code` I guess. Is this column indexed? – Gaël J Dec 27 '22 at 07:33
  • No, none column is indexed. I have checked, it's my operator LIKE the problem, how can I improve that ? – Kev74 Dec 27 '22 at 14:02
  • Add an index on the `code` column then. As you are using `LIKE` with a known start, the index will be used. – Gaël J Dec 27 '22 at 15:56
  • This is useless... The problem seems to occur with the operator OR. I've tried to Create an Index before with COUNTRIES(NAME,CODE) and keeps the same Query, nothing change... – Kev74 Dec 27 '22 at 20:44
  • 1
    Ah, didn't notice the LIKE was on column `name`. I meant index on column `name` then indeed. Because of the OR, it would probably be better to have 2 indexed then: one for code and one for name. – Gaël J Dec 27 '22 at 21:00
  • You can run `EXPLAIN` to see how your query is interpreted and whether indexes are used or not. – Gaël J Dec 27 '22 at 21:01
  • That function returns me only the plan (I used a H2 DataBase).. I notice I Can use unionAll instead of OR and my Query goes fast... But I would like to know if using indexes have effects on the Time execution – Kev74 Dec 27 '22 at 21:28
  • 2
    In general indexes won't slow down your SELECT query (they will tend to slow down INSERTs and UPDATEs). Beyond that, the only way to know if it will speed up your query is to experiment. – Levi Ramsey Dec 28 '22 at 02:21

0 Answers0