Is PreparedStatement reused on insert?

[Kenji Nakamura <na...@gmail.com>]

Hi,

I'm evaluating iBatis 2.1.7, and encountered a performance problem.  
The benchmark is simply taking a sequential file with approximately  
16,000 lines, and populate them into HSQLDB (1.8.0.1) with 20 lines  
of batch. I see iBatis version is two to three times slower than  
plain vanilla JDBC version.
I put a profiler, and it indicates  
com.ibatis.sqlmap.engine.execution.SqlExecutor#excuteUpdate() creates  
an instance of PreparedStatement every time calling  
SqlMapClient#insert(), and it is actually created at line 77 of the  
method.
Is there a way to keep an instance of PreparedStatement for an Id and  
reuse it? At least creation of PreparedStatement isn't a cheap  
operation in HSQLDB, which takes up 51% of whole execution time in my  
benchmark.

Thanks in advance,

Kenji

Re: Is PreparedStatement reused on insert?

[Kenji Nakamura <na...@gmail.com>]

Jeff,

Thanks for pointing out the latest doc. I referred to 2.1.7's doc. I  
fixed a problem, and now confirmed PreparedStatement is created as  
many as calling executeBatch(). However, batch size 20 is too  
inefficient, so I bumped it up to much larger number. This may be  
trade off between the size of a transaction and cost of creation of  
PreparedStatement, but the number should be much bigger than I  
thought in case of HSQLDB.

Kenji


On Aug 2, 2006, at 12:32 PM, Jeff Butler wrote:

> If you use a batch, then the prepared statement will be reused.   
> The latest developer guide has lots of information about batches.
>
> Jeff Butler
>
>
>
> On 8/2/06, Kenji Nakamura <na...@gmail.com> wrote:
> Hi,
>
> I'm evaluating iBatis 2.1.7, and encountered a performance problem.
> The benchmark is simply taking a sequential file with approximately
> 16,000 lines, and populate them into HSQLDB (1.8.0.1) with 20 lines
> of batch. I see iBatis version is two to three times slower than
> plain vanilla JDBC version.
> I put a profiler, and it indicates
> com.ibatis.sqlmap.engine.execution.SqlExecutor#excuteUpdate() creates
> an instance of PreparedStatement every time calling
> SqlMapClient#insert(), and it is actually created at line 77 of the
> method.
> Is there a way to keep an instance of PreparedStatement for an Id and
> reuse it? At least creation of PreparedStatement isn't a cheap
> operation in HSQLDB, which takes up 51% of whole execution time in my
> benchmark.
>
> Thanks in advance,
>
> Kenji
>
>

Re: Is PreparedStatement reused on insert?

[Jeff Butler <je...@gmail.com>]

If you use a batch, then the prepared statement will be reused.  The latest
developer guide has lots of information about batches.

Jeff Butler



On 8/2/06, Kenji Nakamura <na...@gmail.com> wrote:
>
> Hi,
>
> I'm evaluating iBatis 2.1.7, and encountered a performance problem.
> The benchmark is simply taking a sequential file with approximately
> 16,000 lines, and populate them into HSQLDB (1.8.0.1) with 20 lines
> of batch. I see iBatis version is two to three times slower than
> plain vanilla JDBC version.
> I put a profiler, and it indicates
> com.ibatis.sqlmap.engine.execution.SqlExecutor#excuteUpdate() creates
> an instance of PreparedStatement every time calling
> SqlMapClient#insert(), and it is actually created at line 77 of the
> method.
> Is there a way to keep an instance of PreparedStatement for an Id and
> reuse it? At least creation of PreparedStatement isn't a cheap
> operation in HSQLDB, which takes up 51% of whole execution time in my
> benchmark.
>
> Thanks in advance,
>
> Kenji
>
>