Data mapping optimization: X database queries vs. Y Java if statements

Currently we retrieve a List of objects from the Spring SimpleJdbcTemplate. The first query is a select * to retrieve all records that we will want to be our "keys" . This then leads to a foreach loop over the collection which in turn involves N queries back to the database where N is the size of the List. Would it be more efficient to do these N queries or do 2 large select * statements and map them through a for loop that does an if statement? See code below:

N Queries  

List keyList = Select * from keyT开发者_如何学Pythonable;
for(Object o: keyList)  
   {  
      List valueList = Select * from valueTable;  
      //Map valueTable to keyTable via rowmapper  
    }  




Y Java if statements  
    List keyList = Select * from keyTable;  
    List valueList = Select * from valueTable;  
    for(Object o: keyList)  
       {   
           for(Object z : valueList)  
             {    
                  if(z.key == o.key)  //set the value  
             }   
        } 

I'm not a java programmer, but wouldn't it be simpler to have the database perform the join, since most modern database engines are designed and built to optimize the join process? Guessing at the syntax, you'd have the query

Select * from keyTable o
 inner join valueTable z
  on o.key = z.key

somehow within your code, perhaps like so:

List keyValueList = select * from keyTable o inner join valueTable z on o.key = z.key;

Your options are, in order:

• Let the database do the join. This is by far the best option, and there is hardly ever a reason to do otherwise:

    select k.key, v.value from keyTable k, valueTable v where k.key=v.key

• If for whatever reason you need to do the join at the application level, there is an index on valueTable.key, and you expect few results, do a nested loops join:

    List keyList = Select key from keyTable;
    for (Object k: keyList) {  
        List valueList = Select value from valueTable where key = k;
        for (Object v: valueList) {
            // Your code here
        }
    }

• For the cases not covered by the nested loops join, do a hash join:

    HashMap keyHash = select key, k.* from keyTable k;
    List valueList = select * from valueType v;
    for (Object k: valueList) {
        if (keyHash.containsKey(k)) {
            // Your code here
        }
    }

• Last of all, you can try a sort-merge join

you should use a sql join or subquery to retrieve all your data in a single sql statement. this will allow the database to utilize whatever optimizations mechanisms (indexing and execution plans, caching, etc) that might be available.

using sql join:

select key,value from keyTable k, valueTable v where k.key=v.key

using subquery (only if a key has a unique value):

select key, (select value from valueTable where key=k.key) from keyTable k