Nathan helped me find a solution, and we thought it might help others so
here it is:
Setting the fetchSize parameter on the select statement turned out to
reduce total time for this query from 3.5 minutes to around 11 seconds.
The fetchSize jdbc parameter governs the number of rows returned from
the db at a time and for Oracle, which I am using, the default is set to
10. One of the links below mentions that a good place to start for that
value is with 25% of the rows you expect to return. I used 500 because
the 30K is on the outside of the number of values I am expecting.
Here is my revised statement:
<select id="filter" resultClass="string"
parameterClass="com.sun.occ.vo.FilterEnum" cacheModel="filterCache"
fetchSize="500">
select distinct $columnName$ as value
from $tableName$
<isNotEmpty property="filterName">
where filter_name = #filterName#
</isNotEmpty>
order by 1
</select>
Here are some helpful articles:
http://java.sys-con.com/node/46653
http://download.oracle.com/docs/cd/B25221_04/web.1013/b13593/optimiz011.htm#BEEBHBBG
Thanks Nathan, you're a rock star!
Diran
Diran Ayandele wrote:
Hi,
I have a set of 30K rows, actually just strings, that I need to
return to my application. The query that generates them runs very
quickly in 3 seconds according to the log, but then building out the
results into a list of strings takes up to 3.5 minutes. Are there any
simple things I am missing to get this to run in a reasonable amount
of time? Could I return something else that would work better? I
tried it with a result map and my own bean, but it didn't go any
faster. No, I haven't migrated to iBatis 3 yet...
Here is the statement:
<select id="filter" resultClass="string"
parameterClass="com.sun.occ.vo.FilterEnum" remapResults="true"
cacheModel="filterCache">
select distinct $columnName$ from $tableName$
<isNotEmpty property="filterName">
where filter_name = '$filterName$'
</isNotEmpty>
order by $columnName$
</select>
Here is the calling method:
public List getFilter(FilterEnum fe) {
List filter = new ArrayList();
try {
log.debug("Starting query for filter: " + fe.name());
filter = occSqlMap.queryForList("filter", fe);
log.debug("Filter for " + fe.name() + " returned " +
filter.size() + " row(s)");
} catch (SQLException e) {
log.error("Unable to retrieve filter", e);
}
return filter;
}
Thanks in advance for any help!
Diran
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
