0

I have some code that runs a db call to get a list. Then loops through that list and executes a query from a different database for every record in that list. Something like the following...

public List<Cars> getCars ()
CarDAO carDAO = new CarDAO();
List<Car> cars = carDao.getCars();
for (Car car: cars){
     DefectDAO defectDAO = new DefectDAO();
     defectDAO.getDefects(car.getId())
}

DefectDAO

public List<HashMap<String, Object>> getDefects (String carId)
     // run jdbc query over db2 database A
     ....
}

CarDAO

public List<Cars> getCars ()
     // run jdbc query over db2 database B
     ....
}

I'm using java 1.5 and db2 9.7. My db2 databases are not federated.

I have read it's not good practice to perform a programmatic join in java but I think in this scenario it would help improve the performance.

Rather than manually looping though each list and doing the join programmatically can anyone suggest a library I could use? I'm thinking a library might be better because it's likely to make my code neater and hopefully work as efficiently as possible.

I'm also open to other suggestions / feedback.

thanks

Richie
  • 4,989
  • 24
  • 90
  • 177
  • I'm betting that it's network latency that's killing you. There's nothing you can do about that. Profile the code to measure and see where the time is being spent. Don't guess or assume. – duffymo Jan 25 '16 at 00:40
  • 2
    Instead of running the `getDefects()` query once for each car in `cars`, you would be better off performance-wise querying the defects of all cars in the query you use in `getCars()`, by doing an SQL `JOIN` there. A good rule of thumb with SQL is that row-by-row processing is slow-by-slow (a.k.a. the ["N+1 query problem"](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue)). The less queries you execute, the better the performance. – Mick Mnemonic Jan 25 '16 at 00:47
  • I did some profiling and I don't think it's network latency. Because the db that I am running getDefects on is on the same server as the app server. And when I removed the call to getCars() it reduced my code runtime from 16895 to 1189 milliseconds. A big improvement. – Richie Jan 25 '16 at 00:58
  • I was more looking along the lines of something like JoinRowSetImpl. Which I got to work today but unfortunately I can't get to left join. Apparently only inner join is enabled. – Richie Jan 25 '16 at 06:01

0 Answers0