>>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 <[EMAIL PROTECTED]> 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
>
>
>
