ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Diran Ayandele <Adediran.Ayand...@Sun.COM>
Subject Re: Performance retrieving list of strings.
Date Thu, 08 Oct 2009 14:36:31 GMT
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-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


Mime
View raw message