CODEWITHSUNDEEP
javasql
Arshad Rehmani
Arshad Rehmani

Reputation: 2185

avoid executing a DB query in loop

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,

  1. Same process_name can be found under different parent_ids.

  2. 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.

Upvotes: 0

Views: 1641

Answers (4)

Prakhar Londhe
Prakhar Londhe

Reputation: 1471

There are multiple approaches to solve this problem :

  1. As Svend Suggested, Since you already have process_name, You can get all the start and end times for that process name.

  2. 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=?";

Upvotes: 2

Christian
Christian

Reputation: 1260

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.

Upvotes: 0

Svend Hansen
Svend Hansen

Reputation: 3333

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?

Upvotes: 0

akhan001
akhan001

Reputation: 66

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.

Upvotes: 0

Related Questions