got outofmemoryerror when looping resultset
hi, folks, I've working on this thing for days, still have no clue how to fix the huge memory usage.
i have something like the code below,
ResultSet rsSymbol, rsSearch, rsResult;
......
while(rsIndustry.next()){// while industry
querySymbol = "select distinct symbol from " + rsIndustry.getString("industry");
prepStmtSymbol = con.prepareStatement(querySymbol);
rsSymbol = prepStmtSymbol.executeQuery();
while(rsSymbol.next()){// while symbol
querySearch = "select `date`, symbol, close, volumn, adjustedClose from " + rsIndustry.getString("industry") + " where symbol = '" + rsSymbol.getString("symbol") + "' order by date desc";
prepStmtSearch = con.prepareStatement(querySearch);
rsSearch = prepStmtSearch.executeQuery();
while(rsSearch.next()){// while search
......
}// end of while search
}// end of for swim loop
}// end of loop while symbol
}// end of loop while industry
this code consumes huge memory. since the one resultset varible will be assigned a new value for each loop circle, I guess the unreferenced resultset variables are not garbage collected since there is only one connection and the connection is open throughout the code, so all related resources are not released until the connection is release. but I don't know how to deal with it. Please help.
Thanks in advance, guys
[1427 byte] By [
ronlva] at [2007-10-3 5:22:44]
These unclosed PreparedStatements and ResultSets may lead to serious problems with resource leaks. There are also other things that need improvement - you never use the fact that you use PreparedStatements. There are two bad things:
- the Strings you retrieve from the ResultSets may contain characters that can break the SQL syntax and need DB specific escaping.
- If you created the statements only once as parameterized statements you would gain performance since they will be precompiled and reused.
Sample rework:
ResultSet rsSymbol, rsSearch, rsResult;
......
querySymbol = "select distinct symbol from ?";
prepStmtSymbol = con.prepareStatement(querySymbol);
querySearch = "select `date`, symbol, close, volumn, adjustedClose from ? where symbol = ? order by date desc";
prepStmtSearch = con.prepareStatement(querySearch);
while(rsIndustry.next()){ // while industry
prepStmtSymbol.setString(1, rsIndustry.getString("industry"));
rsSymbol = prepStmtSymbol.executeQuery();
while(rsSymbol.next()){ // while symbol
prepStmtSearch.setString(1, rsIndustry.getString("industry"));
prepStmtSearch.setString(2, rsSymbol.getString("symbol") );
rsSearch = prepStmtSearch.executeQuery();
while(rsSearch.next()){ // while search
......
} // end of while search
rsSearch.close();
} // end of loop while symbol
rsSymbol.close();
} // end of loop while industry
prepStmtSymbol.close();
prepStmtSearch.close();
This may not solve the OOM error, but will surely save you a lot of DB resource troubles.
Mike
http://forum.java.sun.com/help.jspa?sec=formattingAnd make sure that you use the PreparedStatement correctly. using the parameters.Otherwise, I'd like to see the full loop. Are you closing the statements and result sets?
Ooops, my sample sucks. I missed the fact that there are table names in the queries that come from the ResultSet. But CNEPUP's suggestions are still valid, you should close the statements and resultsets.Mike
thanks, Mike and CeciNEstPasUnProgrammeur.
ok, below is my formated code again. Let me explain it more clearly.
first, I query table "industry", get column `table` saved in ResultSet rsIndustry.
Then I loop rsIndustry, in each loop, I query table "rsIndustry.getString('table')", query result is saved in ResultSet rsSymbol.
then I loop rsSymbol, in each loop, some useful functions are performed.
I only use one connection for the whole search() method (this will avoid the overhead of start new connections, right? correct me if I am wrong).
The reason I didn't close all the resultset after all the nested loops is that, I thought if you close an resultset, let's say rsSymbol here, you also close the connection to the DB. that way, the loop can't continue without creating new connections each time.
by doing sth like
rsSymbol = prepStmtSymbol.executeQuery();
I was hoping rsSymbol will get a new memory location and the old memory location will be garbage collected since it looks like it is not referenced by the program anymore, but appreantly, it's not the case here.
ok, folks, please shred some light on me, really appreciate it.
public void search(){
String queryIndustry = "select `table` from industry";
String querySymbol, querySearch, queryMatch, matchEndDateStr, endDateStr;
float startClose, startAdjustedClose, startAdjustedRate, adjustedRate;
try{
PreparedStatement prepStmtIndustry = con.prepareStatement(queryIndustry);
ResultSet rsIndustry = prepStmtIndustry.executeQuery();
PreparedStatement prepStmtMatchBatch = null;
ResultSet rsSymbol, rsSearch;
PreparedStatement prepStmtSymbol, prepStmtSearch;
while(rsIndustry.next()){// while industry
querySymbol = "select distinct symbol from " + rsIndustry.getString("table");
prepStmtSymbol = con.prepareStatement(querySymbol);
rsSymbol = prepStmtSymbol.executeQuery();
while(rsSymbol.next()){// while symbol
querySearch = "select `date`, symbol, close, volumn, adjustedClose from " + rsIndustry.getString("table") + " where symbol = '" + rsSymbol.getString("symbol") + "' order by date desc";
prepStmtSearch = con.prepareStatement(querySearch);
rsSearch = prepStmtSearch.executeQuery();
forloopswim:
for(i = 0; i < stockDataToBeMatched.size(); i++){// for swim
matchEndDateStr ="";
whileloopsearch:
while(rsSearch.next()){// while search
......
}// end of while search
}// end of for swim loop
}// end of loop while symbol
}// end of loop while industry
......
}catch(Exception e){
e.printStackTrace();
System.out.println(e);
}
}// end of search
ronlva at 2007-7-14 23:29:48 >
You better try to optimize your query(try to use join)..rather than looping through many result sets...You can simplify the logic in your query.. check with your DBA...Let us know more about your table structure and the final output of the three select's you are trying to
> hi, folks, I've working on this thing for days, still
> have no clue how to fix the huge memory usage.
>
> i have something like the code below,
>
> ResultSet rsSymbol, rsSearch, rsResult;
> ......
> while(rsIndustry.next()){// while
> industry
> querySymbol = "select distinct symbol
> from " + rsIndustry.getString("industry");
> prepStmtSymbol =
> con.prepareStatement(querySymbol);
> rsSymbol =
> prepStmtSymbol.executeQuery();
>
>while(rsSymbol.next()){// while symbol
> querySearch = "select `date`,
> symbol, close, volumn, adjustedClose from " +
> rsIndustry.getString("industry") + " where symbol =
> '" + rsSymbol.getString("symbol") + "' order by date
> desc";
> prepStmtSearch =
> con.prepareStatement(querySearch);
> rsSearch =
> prepStmtSearch.executeQuery();
> while(rsSearch.next()){
>// while search
> ......
> d of while search
> }// end of for swim loop
> // end of loop while symbol
> }// end of loop while industry
> consumes huge memory. since the one resultset varible
> will be assigned a new value for each loop circle, I
> guess the unreferenced resultset variables are not
> garbage collected since there is only one connection
> and the connection is open throughout the code, so
> all related resources are not released until the
> connection is release. but I don't know how to deal
> with it. Please help.
> Thanks in advance, guys
where are you closing your resources like resultsets and preparedstatements. And remember that you are supposed to close them in the finally block and nowhere else.
