avoid executing a DB query in loop

Question

I have a table as below,

process_name |  startTime             | endTime               | parent_id
-------------------------------------------------------------------------
chrome       |  2019-03-06 00:48:27   | 2019-03-06 00:48:58   | 111
chrome       |  2019-03-07 00:48:27   | 2019-03-07 00:48:58   | 112
firefox      |  2019-03-08 00:48:27   | 2019-03-08 00:48:58   | 113
IE           |  2019-03-09 00:48:27   | 2019-03-09 00:48:58   | 114
firefox      |  2019-03-10 00:48:27   | 2019-03-10 00:48:58   | 115
chrome       |  2019-03-11 00:48:27   | 2019-03-11 00:48:58   | 116

Some points from the table,

Same process_name can be found under different parent_ids.
I already know the process_name and have an ArrayList of parent_ids where it exists.

For a given process_name, I need to find startTime and endTime for all of the parent_ids present in my ArrayList.

All I can think of is using a for loop and execute the below query.

select startTime, endTime from myTable where parent_id = ? and process_name=?

Can I avoid executing this query in a loop and follow some other better approach?

I use Derby at this moment but the DB may get changed to Oracle.

Is parent_id stored in a table? If so you can perform a JOIN. — kometen
– kometen, Commented Mar 21, 2019 at 8:00

Svend Hansen · Accepted Answer · 2019-03-21 09:36:53Z

2

There are multiple approaches to solve this problem :

As Svend Suggested, Since you already have process_name, You can get all the start and end times for that process name.
Another approach is to prepare a string of parent_ids first, and then query it to the database using a single query:

Code:

String s = "(";
for(int i = 0; i < list.length; i++)
{
    if(i<list.length-1) s+=list[i] + ",";
    else s+= ""+ list[i] + ")";
}

String query = " select startTime, endTime from myTable where parent_id IN " + s + " and process_name=?";

edited Mar 21, 2019 at 9:36

Svend Hansen

3,3433 gold badges35 silver badges54 bronze badges

answered Mar 21, 2019 at 8:26

Prakhar Londhe

1,4711 gold badge13 silver badges27 bronze badges

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

3 Comments

Zaynul Abadin Tuhin Over a year ago

op wants to avoid loop

morgano Over a year ago

@ZaynulAbadinTuhin, OP doesn't meant to say that he doesn't want to do a loop at all, he just doesn't want to do a loop of SQL queries

Svend Hansen Over a year ago

I guess this would be better using Christian's ? formatting, as it would be less injection attack prone... though that shouldn't matter too much in this specific case, as we're probably in control of what's in list...

akhan001 · Accepted Answer · 2019-03-21 08:06:13Z

0

It is not good practice to execute a query in a loop. Instead, why don't you fetch all the records belonging to a given process at once. Maybe using GROUPBY clause for the process_name along with aggregate functions on other columns [if you need to perform anything upon those other columns and avoid doing via code by yourself] Once you have all the records in hand, then the startTime and endTime can be used for your further processing.

Hope that helps.

edited Mar 21, 2019 at 8:06

answered Mar 21, 2019 at 7:58

akhan001

665 bronze badges

Comments

Svend Hansen · Accepted Answer · 2019-03-21 08:16:41Z

0

Wouldn't it be possible to just do:

select parent_id, startTime, endTime from myTable where process_name=?

And get the values for all parent_id values, and then filter those on your ArrayList afterwards?

edited Mar 21, 2019 at 8:16

answered Mar 21, 2019 at 8:01

Svend Hansen

3,3433 gold badges35 silver badges54 bronze badges

Comments

Christian · Accepted Answer · 2019-03-21 09:13:05Z

0

As a slight variation of the solution of @prakhar-londhe you might also use parameters in the in clause as in

String query = " select startTime, endTime from myTable where parent_id IN (?, ?, ?) and process_name = ?";

and then add a parameter for each parent_id. Of course you'd still have to assemble the query (to put a ? inside the parentheses for each parent_id) as well as the parameter list in a loop.

answered Mar 21, 2019 at 9:13

Christian

1,2589 silver badges12 bronze badges

Collectives™ on Stack Overflow

avoid executing a DB query in loop

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related