ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Am I doing anything wrong? iBATIS has very poor performance on batch inserts compared to JDBC.
Date Fri, 17 Jun 2005 19:29:20 GMT
Hey Vic,

>>When doing large inserts in loops; for performance it's best to have the
>>commint/rollback and start batch outside of the loop?

Yes, absolutely. Not only for performance, but otherwise it wouldn't be "a" 
transaction would it? :-)

>>Do we have to have transactions of can we just use start/execute batch?

Yes, you have to explicitly start both. Perhaps in the future we'll merge 
the concepts like....startBatchTransaction(), where commitTransaction() 
would execute the batch and commit.

>> (and what if I want the insert to commit, even if a few rows had errors)

Not supported transparently, but you could wrap your statements with 
try/catch blocks and decide whether to commit or not. 

cheers,
Clinton

On 6/17/05, netsql <netsql@roomity.com> wrote:
> 
> So for us SQL newbies, if we can put up w/ a bit more:
> 
> When doing large inserts in loops; for performance it's best to have the
> commint/rollback and start batch outside of the loop?
> 
> 
> Do we have to have transactions of can we just use start/execute batch?
> (and what if I want the insert to commit, even if a few rows had errors)
> 
> 
> 
> 
> I wonder if I should groovy my back end work...
> 
> 
> any comments wellcome,
> 
> .V
> 
> 
> 
> Clinton Begin wrote:
> >
> >
> > Sorry Bing, for my frustrated response. It's just that posting such
> > performance stats tend to cause concern for other users (like it did for
> > Ming Xue earlier). We have to be very careful about posting performance
> > stats and make sure to disclaim them. There are so many factors that
> > not two users will have the same experience.
> >
> > Now, on to your question...
> >
> > This code that you've posted uses an autocommit-like semantic. That
> > is, it behaves like JDBC autocommit (although iBATIS never truly uses
> > connections in autocommit mode. So in your code....
> >
> > sqlMap.startBatch();
> > Map args = CollectionUtil.createMap(2);
> > for(int i=0;i<p_resultList.size();i++)
> >
> > {
> > AdvancedSearchResultData resultData =
> > (AdvancedSearchResultData)p_resultList.get(i);
> > args.put("RESULT_DATA", resultData);
> > args.put("UserIdentifier", p_userID);
> > sqlMap.insert("insertIntoTempTable", args); //
> > <<<<<< GETCONNECTION
> > }
> > sqlMap.executeBatch();
> >
> >
> > The GETCONNECTION mark is where connections are being requested. To
> > fix this....
> >
> > try {
> > sqlMaps.startTransaction();
> > sqlMap.startBatch();
> > Map args = CollectionUtil.createMap(2);
> > for(int i=0;i<p_resultList.size();i++)
> >
> > {
> > AdvancedSearchResultData resultData =
> > (AdvancedSearchResultData)p_resultList.get(i);
> > args.put("RESULT_DATA", resultData);
> > args.put("UserIdentifier", p_userID);
> > sqlMap.insert("insertIntoTempTable", args); //
> > <<<<<< HOTSPOT
> > }
> > sqlMap.executeBatch();
> >
> >
> > sqlMap.commitTransaction();
> >
> > } finally {
> > sqlMap.endTransaction();
> > }
> >
> >
> > Hope that helps. Sorry again for my over-excited response. :-)
> >
> > Cheers,
> >
> > Clinton
> >
> >
> >
> 
>

Mime
View raw message