The key here was the sheer number of rows being returned. Being that Diran
is actually a close friend of mine we worked out a solution to use the
fectchSize attribute on the select.
This is a very important attribute for large selects. the default Oracle
fetch size is actually 10 rows. So in the case of a result set with 30k
rows it was making 3k round trips to the DB server. Common performance tips
from Oracle ask you to set the fetch size between 25 and 50 percent of the
expected row size.
After setting Diran's query to use a fetchSize of 500 we had the query
running in under 10 seconds versus the original 4 minutes.
High Five! Great Success!
Nathan
On Tue, Oct 6, 2009 at 11:45 AM, Diran Ayandele
<adediran.ayand...@sun.com>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
>
>
