Java - DB2 Performance Improvements

Question

We have a SELECT statement which will take approx. 3 secs to execute. We are calling this DB2 query inside a nested While loop.

Ex:

While(hashmap1.hasNext()){ while(hashmap2.hasNext()){ SQL Query } }

Problem is, the outer While loop will execute approx. 1200 times and inner While loop will execute 200 times. Which means the SQL will be called 1200*200 = 240,000 times. Approx. each iteration of Outer While loop will take 150 secs. So, 1200 * 150 secs = 50 hrs.

We can afford only around 12-15hrs of time, before we kick off the next process.

Is there any way to do this process quickly? Any new technology which can help us in fetching these records faster from DB2.

Any help would be highly appreciated.

Note: We already looked into all possible ways to cut down the no.of iterations.

Can you give an outline of the SQL query? Is it only a read, is it an update? I imagine it is parameterized by the iteration variables? How many records being fetched after each query? Are the results of one query used in subsequent queries? If you return a large number of results for each query execution, do the results of separate query executions have a lot of overlap with one another? — Dilum Ranatunga
– Dilum Ranatunga, Commented May 23, 2011 at 18:13
What is hashmap1 and hashmap2? Can we see the SQL? Why can you not do one big SQL statement outside of the loop and then query for your data against the local collection? — Brad
– Brad, Commented May 23, 2011 at 18:14

duffymo · Accepted Answer · 2011-05-23 18:41:37Z

4

Sounds to me like you're trying to use the middle tier for something that the database itself is better suited for. It's a classic "N+1" query problem.

I'd rewrite this logic to execute entirely on the database as a properly indexed JOIN. That'll not only cut down on all that network back and forth, but it'll bring the database optimizer to bear and save you the expense of bringing all that data to the middle tier for processing.

edited May 23, 2011 at 18:41

answered May 23, 2011 at 18:12

duffymo

310k46 gold badges376 silver badges571 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stefan Kendall Over a year ago

I think you hit the nail on the head here.

Andrew Lazarus Over a year ago

As a specific way that this might happen: if your query looks like SELECT z FROM y WHERE y.a=first_hashmap_value AND y.b=second_hashmap_value you can create a monster IN clause. Or you could create temporary tables from the hashmap data (in a transaction this should be fast) and do a cross-join in the database.

Collectives™ on Stack Overflow

Java - DB2 Performance Improvements

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related