How to Set Fetch Size For ResultSet In IBatis for performance increase for large data retrieving (more than 6000 records)

[Sankar Reddy]

Hi everyone,

     I am facing some performance issue with IBatis procedure for retrieving
large data (having more than 6000 records)from database. i am unable to Set
Fetch Size For ResultSet with Ibatis, <br>
 
 I tested with simple JDBC and datasource with Set Fetch Size For ResultSet
it improved performance very good (<2Sec), this same with ibatis taken
>60Sec.<br>

IBatis code:
 

<parameterMap id="getBillingAccountsMap" class="map">
                        <parameter property="p_cust_grp_id" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
                        <parameter property="p_status" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT"/>
                        <parameter property="p_error_code" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT"/>
                        <parameter property="p_error_message" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT"/>
                        <parameter property="p_out_bac" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" resultMap="getBACsResultMap"/>
                        <parameter property="p_out_customer" 
jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" resultMap="getCustomerResultMap"/>
                </parameterMap>
                
                
<procedure id="getBillingAccounts" parameterMap="getBillingAccountsMap"
fetchSize="100">
                                {call 
get_billing_accnts_by_customer(?,?,?,?,?,?)}
</procedure> 

Plain JDBC code to improve performance (very good performance)

        String proc3StoredProcedure = "{ call
get_billing_accnts_by_customer(?,?,?,?,?,?) }";
                        CallableStatement cs = 
conn.prepareCall(proc3StoredProcedure);
                        //cs.setFetchSize(500);
                        cs.setString(1, "CUG5300006939");
                        cs.registerOutParameter(2, java.sql.Types.VARCHAR);
                        cs.registerOutParameter(3, java.sql.Types.VARCHAR);
                        cs.registerOutParameter(4, java.sql.Types.VARCHAR);
                        cs.registerOutParameter(5, OracleTypes.CURSOR);
                        cs.registerOutParameter(6, OracleTypes.CURSOR);
                        cs.execute(); 
                        getBACsResultMap = (ResultSet)cs.getObject(5);
                        getBACsResultMap.setFetchSize(100);
                         Date startTime = new Date();
                                while (getBACsResultMap.next ())   {
                                        
                                        bacs = new BACsDTO();
                                        
bacs.setBillingAccountNumber(getBACsResultMap.getString(1));
                                        
bacs.setType(getBACsResultMap.getString(2));
                                        
bacs.setTaxInclusive(getBACsResultMap.getString(3));
                                        
bacs.setBillingAccountStatus(getBACsResultMap.getString(4));
                                        
bacs.setBillingAccountSubStatus(getBACsResultMap.getString(5));
                                        
bacs.setBillingAccountSystem(getBACsResultMap.getString(6));
                                        
bacs.setBillingAccountInstance(getBACsResultMap.getString(7));
                                        bacsList.add(bacs);
}

getBACsResultMap.setFetchSize(100); this line given very good performance
improvement (getBACsResultMap is ResultSet Type)

Please let me know how to set this ResultSet Fetch Size in IBatis.

it will really  help full to me

Thanks in advance

Regards,
Sankar Reddy

-- 
View this message in context: 
http://www.nabble.com/How-to-Set-Fetch-Size-For-ResultSet-In-IBatis-for-performance-increase-for-large-data-retrieving-%28more-than-6000-records%29-tp24303277p24303277.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


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