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
