Batching Stored Procedures

[Trevor Brosnan <tr...@gmail.com>]

Is it possible in iBatis to batch stored procedure invocations?

Many JDBC Drivers support the batching of callable statements (e.g. recent 
DB2 Drivers, Sybase jConnect etc). This feature is essential for the domain 
I work in where we will be doing some intensive insert/update operations – 
perhaps 5-10% of the data access operations contained in our applications 
will need to be batch operations for performance reasons.

However, from some preliminary testing, I have been unable to get iBatis to 
execute stored procedures in a batch. I plan to use Spring Framework's 
support for iBatis in front of the iBatis API, but even without this layer 
of abstraction I have been unable to execute stored procedures in a batch.

For example, the following code will execute a batch if the mapped statement 
'InsertBusinessDomain' is a direct SQL string specified via an <insert> 
element. However, if it is a <procedure>, then the procedure is executed 
each time we call sqlMapClient.insert() – and sqlMapClient.executeBatch() 
returns 0:

*String resource = "sqlMapConfig.traditional.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlMapClient sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);

sqlMapClient.startTransaction();

sqlMapClient.startBatch();

for(Iterator itr = coll.iterator(); itr.hasNext();){
sqlMapClient.insert("InsertBusinessDomain",itr.next());
}

int results = sqlMapClient.executeBatch();

sqlMapClient.commitTransaction();
sqlMapClient.endTransaction();*

Stored procedure access is mandated, thus we cannot use direct SQL. The 
stored procedure itself is stripped down to a single insert statement. 

I am using P6Spy (configured to log to a Log4j SocketAppender) in 
conjunction with the SQL Profiler tool to verify exactly what SQL is being 
executed by the JDBC Driver. 

Any clarification or assistance with this issue is greatly appreciated.

Thanks in advance,
Trevor

Re: Batching Stored Procedures

[Trevor Brosnan <tr...@gmail.com>]

Anyone from the development team have a definitive answer to this, before I 
delve deep into the code to find the answer or possibly make the changes so 
that iBatis can correctly batch stored procedure invocations?
 Thanks in advance,
Trevor

 On 5/1/05, Trevor Brosnan <tr...@gmail.com> wrote: 
> 
> Is it possible in iBatis to batch stored procedure invocations?
> 
> Many JDBC Drivers support the batching of callable statements (e.g. recent 
> DB2 Drivers, Sybase jConnect etc). This feature is essential for the domain 
> I work in where we will be doing some intensive insert/update operations â€" 
> perhaps 5-10% of the data access operations contained in our applications 
> will need to be batch operations for performance reasons. 
> 
> However, from some preliminary testing, I have been unable to get iBatis 
> to execute stored procedures in a batch. I plan to use Spring Framework's 
> support for iBatis in front of the iBatis API, but even without this layer 
> of abstraction I have been unable to execute stored procedures in a batch. 
> 
> For example, the following code will execute a batch if the mapped 
> statement 'InsertBusinessDomain' is a direct SQL string specified via an 
> <insert> element. However, if it is a <procedure>, then the procedure is 
> executed each time we call sqlMapClient.insert() â€" and 
> sqlMapClient.executeBatch() returns 0:
> 
> *String resource = "sqlMapConfig.traditional.xml";
> Reader reader = Resources.getResourceAsReader(resource);
> SqlMapClient sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
> 
> sqlMapClient.startTransaction();
> 
> sqlMapClient.startBatch();
> 
> for(Iterator itr = coll.iterator(); itr.hasNext();){
> sqlMapClient.insert("InsertBusinessDomain", itr.next());
> }
> 
> int results = sqlMapClient.executeBatch();
> 
> sqlMapClient.commitTransaction();
> sqlMapClient.endTransaction();*
> 
> Stored procedure access is mandated, thus we cannot use direct SQL. The 
> stored procedure itself is stripped down to a single insert statement. 
> 
> I am using P6Spy (configured to log to a Log4j SocketAppender) in 
> conjunction with the SQL Profiler tool to verify exactly what SQL is being 
> executed by the JDBC Driver. 
> 
> Any clarification or assistance with this issue is greatly appreciated. 
> 
> Thanks in advance,
> Trevor
>