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 Thu, 16 Jun 2005 20:06:56 GMT
Whoops, I can't type today. Corrected:

You should call start/commit/end transaction in iBATIS regardless of the 
outer transaction. iBATIS will not commit the transaction prematurely, but 
it will manage the resources (like connections) properly. Think of it like a 
global transaction.

Try it, and let us know how it turns out.

 Cheers,
Clinton



On 6/16/05, Clinton Begin <clinton.begin@gmail.com> wrote:
> 
> 
> Bing, 
> 
> Don't you should call start/commit/end transaction in iBATIS regardless of 
> the outer transaction. iBATIS will not commit the transaction prematurely, 
> but it will manage the resources (like connections properly). Think of it 
> like a global transaction.
> 
> Try it, and let us know how it turns out.
> 
> Cheers,
> Clinton
> 
> On 6/16/05, Bing Zou <xiguamaillist@gmail.com> wrote:
> > 
> > Sorry for this lengthy thread. I didn't mean to offend, iBATIS is a
> > great framework and there are a lot of companies including mine that
> > are using iBATIS in production. I know It's very unlikely that this is
> > an iBATIS bug. Most likely I didn't configure it or use it correctly. 
> > And that's why the title begin with "Am I doing anything wrong?..."
> > 
> > Anyway. To summarize, the context is, the appserver is weblogic,
> > iBATIS has been configured to use EXTERNAL transaction manager:
> > 
> > <transactionManager type="EXTERNAL">
> > <property name="SetAutoCommitAllowed" value="false"/>
> > <dataSource type="JNDI">
> > <property name="DataSource" value="MyOraclePool"/> 
> > </dataSource>
> > </transactionManager>
> > 
> > Now The "Transaction trans = getTransaction(session);" in
> > SqlMapExecutorDelegate.insert() returns null instead of the 'external'
> > transaction, which makes every insert() creates a new transaction. I 
> > know I can call sqlmap.startTransaction() explicitly before
> > sqlmap.startBatch() to make the bach work, but it doesn't make sense
> > since there already is a transaction around the EJB method which calls
> > the DAO method where the iBATIS query kicks off. This batch is part of 
> > a transaction but not all of it.
> > 
> > Is my understanding correct? Is the getTransaction(session) supposed
> > to return the external transaction if an external transaction manager
> > is used?
> > 
> > Thanks a lot. I deeply appreciate it. 
> > Bing
> > 
> > On 6/16/05, Clinton Begin <clinton.begin@gmail.com> 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
> > >
> > >
> > >
> > > On 6/16/05, Bing Zou < xiguamaillist@gmail.com> wrote:
> > > > 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);
> > > > }
> > > > sqlMap.executeBatch();
> > > >
> > > > Sorry I could not see why the code is requesting a new connection 
> > from
> > > > the datasource in every iterate. 
> > > >
> > > > Thanks.
> > > > Bing
> > > > On 6/16/05, Clinton Begin < clinton.begin@gmail.com> wrote:
> > > > > Here we go again with the "OH MY GOD, IBATIS is 100x SLOWER THAN

> > > JDBC!!!"
> > > > >
> > > > > If that were the case, NOBODY would use iBATIS, let alone the tens

> > of
> > > > > thousands of people that do.
> > > > >
> > > > > 90% of major performance problems with JDBC, iBATIS, Hibernate 
> > etc. are 
> > > due
> > > > > not to the framework, but moreso with how the database is 
> > accessed, how
> > > the
> > > > > transactions are demarcated and how often you hit the database.
> > > > >
> > > > > In this case, you're requesting a new connection from your 
> > datasource 
> > > EVERY
> > > > > time you iterate. So you should definitely have the entire block
> > > wrapped
> > > > > with
> > > > >
> > > startTransaction()/commitTransaction()/endTransaction(),
> > > > > even if you are using external transaction manager. iBATIS is 
> > smart
> > > enough
> > > > > to deal with that, but you still need to tell iBATIS where the TX

> > starts
> > > and
> > > > > ends so that it can reserve the connection appropriately. 
> > > > >
> > > > > FURTHERMORE: Because you're using a different connection each 
> > time,
> > > your
> > > > > BATCH is completely useless. The batch is flushed each time your
> > > automatic 
> > > > > transaction is committed.
> > > > >
> > > > > So the transaction demarcation should fix the majority of your 
> > problem.
> > > > >
> > > > > Aside from that, you're also comparing the performance of a 
> > HashMap 
> > > with a
> > > > > primitive, which will obviously be slower. I recommend you use a
> > > JavaBean
> > > > > in both the iBATIS test case as well as the JDBC test case, which

> > is
> > > more
> > > > > fair. 
> > > > >
> > > > > Good luck.
> > > > >
> > > > > Clinton
> > > > >
> > > > >
> > > > > On 6/16/05, Bing Zou < xiguamaillist@gmail.com > wrote:
> > > > > > FYI:
> > > > > > I debugged the code and found out that the SQLMapSessionImpl

> > object
> > > > > > returned by SqlMapClientImpl.getLocalSqlMapSession()
> > > has 
> > > > > > transaction=null and transactionState=null. The full tree of
> > > > > > SQLMapSessionImpl object is:
> > > > > >
> > > > > > sqlMapSession= SqlMapSessionImpl (id=9681) 
> > > > > > closed= false
> > > > > > delegate= SqlMapExecutorDelegate (id=9327)
> > > > > > cacheModels= HashMap (id=9329)
> > > > > > cacheModelsEnabled= false
> > > > > > dataExchangeFactory= DataExchangeFactory
> > > > > (id=9330)
> > > > > > enhancementEnabled= true
> > > > > > lazyLoadingEnabled= false
> > > > > > mappedStatements= HashMap (id=9331)
> > > > > > maxRequests= 512
> > > > > > maxSessions= 128
> > > > > > maxTransactions= 32
> > > > > > parameterMaps= HashMap (id=9332)
> > > > > > requestPool= ThrottledPool (id=9333)
> > > > > > resultMaps= HashMap (id=9334)
> > > > > > sessionPool= ThrottledPool (id=9335)
> > > > > > sqlExecutor= SqlExecutor (id=9336)
> > > > > > txManager= TransactionManager (id=9337)
> > > > > > typeHandlerFactory= TypeHandlerFactory
> > > > > (id=9338)
> > > > > > session= SessionScope (id=9416)
> > > > > > batch= null
> > > > > > commitRequired= false
> > > > > > id= 1451
> > > > > > inBatch= true
> > > > > > map= HashMap (id=9683)
> > > > > > requestStackDepth= 0
> > > > > > savedTransactionState= null
> > > > > > sqlMapClient= SqlMapClientImpl (id=9325)
> > > > > > delegate= SqlMapExecutorDelegate
> > > > > (id=9327)
> > > > > > localSqlMapSession= ThreadLocal
> > > > > (id=9328)
> > > > > > sqlMapExecutor= SqlMapClientImpl (id=9325)
> > > > > > sqlMapTxMgr= SqlMapClientImpl (id=9325)
> > > > > > transaction= null
> > > > > > transactionState= null
> > > > > >
> > > > > > There is a transactionState called "STATE_USER_PROVIDED", I

> > guess this
> > > > > > is the value of transactionState while using "EXTERNAL" 
> > transaction 
> > > > > > manager?
> > > > > >
> > > > > > Thanks.
> > > > > > Bing
> > > > > > On 6/16/05, Bing Zou < xiguamaillist@gmail.com > wrote:
> > > > > > > Just checked the iBATIS source code.
> > > > > > >
> > > > > > > The SqlMapExecutorDelegate.insert () method does have to
be in 
> > a
> > > > > transaction: 
> > > > > > > public Object insert(SessionScope session, String id, Object
> > > param)
> > > > > > > throws SQLException {
> > > > > > > Object generatedKey = null;
> > > > > > > 
> > > > > > > MappedStatement ms = getMappedStatement(id);
> > > > > > > Transaction trans = getTransaction(session);
> > > > > > > boolean autoStart = trans == null; // If no transaction

> > exists, 
> > > > > > > will automatically start a new transaction.
> > > > > > >
> > > > > > > try {
> > > > > > > trans = autoStartTransaction(session, autoStart, trans);

> > > > > > >
> > > > > > > SelectKeyStatement selectKeyStatement = null;
> > > > > > > if (ms instanceof InsertStatement) {
> > > > > > > selectKeyStatement = ((InsertStatement) 
> > > > > ms).getSelectKeyStatement();
> > > > > > > }
> > > > > > >
> > > > > > > if (selectKeyStatement != null &&
> > > !selectKeyStatement.isAfter()) { 
> > > > > > > generatedKey = executeSelectKey(session, trans, ms, param);
> > > > > > > }
> > > > > > >
> > > > > > > RequestScope request = popRequest(session, ms);
> > > > > > > try { 
> > > > > > > ms.executeUpdate(request, trans, param);
> > > > > > > } finally {
> > > > > > > pushRequest(request);
> > > > > > > }
> > > > > > >
> > > > > > > if (selectKeyStatement != null &&
> > > selectKeyStatement.isAfter()) {
> > > > > > > generatedKey = executeSelectKey(session, trans, ms, param);
> > > > > > > }
> > > > > > >
> > > > > > > autoCommitTransaction(session, autoStart);
> > > > > > > } finally { 
> > > > > > > autoEndTransaction(session, autoStart);
> > > > > > > }
> > > > > > >
> > > > > > > return generatedKey;
> > > > > > > } 
> > > > > > >
> > > > > > > Now the question is: why the Transaction trans =
> > > > > > > getTransaction(session); returns null if I use "EXTERNAL"
> > > transaction 
> > > > > > > manager?
> > > > > > >
> > > > > > > Is this a bug in iBATIS?
> > > > > > >
> > > > > > > Thanks.
> > > > > > > Bing
> > > > > > > On 6/16/05, Bing Zou < xiguamaillist@gmail.com> wrote:
> > > > > > > > Why iBATIS batch has to be within a transaction while
JDBC 
> > batch
> > > > > doesn't?
> > > > > > > > 
> > > > > > > > Thanks.
> > > > > > > > Bing
> > > > > > > >
> > > > > > > > On 6/16/05, Larry Meadors <larry.meadors@gmail.com
> wrote:
> > > > > > > > > no, that looks right..
> > > > > > > > >
> > > > > > > > > On 6/16/05, Bing Zou <xiguamaillist@gmail.com
> wrote:
> > > > > > > > > > The transactionManager configuration:
> > > > > > > > > > <transactionManager type="EXTERNAL">
> > > > > > > > > > <property name="SetAutoCommitAllowed"
value="false"/>
> > > > > > > > > > <dataSource type="JNDI">
> > > > > > > > > > <property name="DataSource" value="MyOraclePool"/>
> > > > > > > > > > </dataSource>
> > > > > > > > > > </transactionManager>
> > > > > > > > > >
> > > > > > > > > > Anything incorrect here?
> > > > > > > > > >
> > > > > > > > > > Thanks.
> > > > > > > > > > Bing 
> > > > > > > > > >
> > > > > > > > > > On 6/16/05, Larry Meadors <larry.meadors@gmail.com
> 
> > wrote:
> > > > > > > > > > > What transaction manager are you using?

> > > > > > > > > > >
> > > > > > > > > > > I think you may need ot set it to EXTERNAL
or JTA.
> > > > > > > > > > >
> > > > > > > > > > > Larry 
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > On 6/15/05, Bing Zou < xiguamaillist@gmail.com
> 
> > wrote:
> > > > > > > > > > > > Update:
> > > > > > > > > > > > 1. I wrote a stand alone test
class. If I put an 
> > explicit
> > > > > transaction
> > > > > > > > > > > > ( sqlmapclient.startTransaction())
around it, the 
> > batch
> > > process
> > > > > worked.
> > > > > > > > > > > > Otherwise, it did not. (every
'insert' hit the 
> > database 
> > > > > directly
> > > > > > > > > > > > instead of being cached.)
> > > > > > > > > > > >
> > > > > > > > > > > > 2. I tested a DAO method which
is called within a 
> > weblogic 
> > > > > > > > > > > > transaction, the batch process
didn't work. I had to 
> > call
> > > > > > > > > > > > sqlmapclient.startTransaction
explicitly before the
> > > batching 
> > > > > to make
> > > > > > > > > > > > it work. But it doesn't make sense
to have two
> > > transactions
> > > > > here. The
> > > > > > > > > > > > inserted data would only be used
by some other 
> > processes 
> > > later
> > > > > in the
> > > > > > > > > > > > same weblogic transaction.
> > > > > > > > > > > >
> > > > > > > > > > > > Question: 
> > > > > > > > > > > > It seems that the sqlmapclient
doesn't know that it 
> > is
> > > within
> > > > > a
> > > > > > > > > > > > weblogic transaction. Is it an
iBATIS defect or is 
> > there 
> > > > > anything that
> > > > > > > > > > > > I could do to solve this issue?
> > > > > > > > > > > >
> > > > > > > > > > > > Thanks. 
> > > > > > > > > > > > Bing
> > > > > > > > > > > > On 6/13/05, Larry Meadors <larry.meadors@gmail.com>

> > wrote: 
> > > > > > > > > > > > > Interesting. I have never
been a fan of batching, 
> > but
> > > did
> > > > > not expect that.
> > > > > > > > > > > > >
> > > > > > > > > > > > > You should probably use an
explicit parameter map 
> > with a
> > > > > bean to
> > > > > > > > > > > > > further improve performance.

> > > > > > > > > > > > >
> > > > > > > > > > > > > Larry
> > > > > > > > > > > > >
> > > > > > > > > > > > > On 6/13/05, Bing Zou <
xiguamaillist@gmail.com> 
> > wrote:
> > > > > > > > > > > > > > I just tried the same
test without batching. It 
> > turned
> > > out
> > > > > that it 
> > > > > > > > > > > > > > took less time (about
4 seconds) to run the 100
> > > inserts
> > > > > without
> > > > > > > > > > > > > > batching. :( Weird.

> > > > > > > > > > > > > >
> > > > > > > > > > > > > > On 6/13/05, Larry Meadors
< larry.meadors@gmail.com 
> > >
> > > > > wrote:
> > > > > > > > > > > > > > > Heh, I was rereading
this message last night 
> > and
> > > > > wondering the same thing.
> > > > > > > > > > > > > > > 
> > > > > > > > > > > > > > > Is there are threshold
at which things go bad? 
> > Do
> > > you
> > > > > have a similar
> > > > > > > > > > > > > > > issue after 100
iterations? 500? 
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Have you tried
it without batching? How about 
> > with
> > > an
> > > > > explicit 
> > > > > > > > > > > > > > > transaction around
it?
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Like i said in
my first response - I have 
> > never seen 
> > > > > this sort of
> > > > > > > > > > > > > > > behavior, and other
than WebLogic, I have 
> > worked
> > > with
> > > > > all of the same
> > > > > > > > > > > > > > > components. 
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Larry
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > 
> > > > > > > > > > > > > > > On 6/13/05, Bing
Zou < xiguamaillist@gmail.com
> > >
> > > wrote:
> > > > > > > > > > > > > > > > Any update
on this issue? 
> > > > > > > > > > > > > > > > Thanks.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > On 6/9/05,
Bing Zou < 
> > xiguamaillist@gmail.com>
> > > wrote:
> > > > > > > > > > > > > > > > > Database:
Oracle 9i
> > > > > > > > > > > > > > > > > Connection
Pooling: Weblogic 
> > > > > > > > > > > > > > > > > Driver:
Oracle type 4 thin driver
> > > > > > > > > > > > > > > > > Map:
HashMap.
> > > > > > > > > > > > > > > > > iBATIS
version: 2.0.7
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Thanks.
> > > > > > > > > > > > > > > > > 
> > > > > > > > > > > > > > > > > On 6/9/05,
Larry Meadors
> > > <larry.meadors@gmail.com >
> > > > > wrote:
> > > > > > > > > > > > > > > > > >
I have used iBATIS for a cpouple of 
> > years now, 
> > > and
> > > > > have not once seen
> > > > > > > > > > > > > > > > > >
this sort of performance issue.
> > > > > > > > > > > > > > > > > >

> > > > > > > > > > > > > > > > > >
What is the database? what is the 
> > connection
> > > > > pooling mechanism? What
> > > > > > > > > > > > > > > > > >
type is th Map that is getting returned? 
> > 
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > >
Give us some more context...
> > > > > > > > > > > > > > > > > >

> > > > > > > > > > > > > > > > > >
Larry
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > >

> > > > > > > > > > > > > > > > > >
On 6/9/05, Bing Zou <
> > xiguamaillist@gmail.com >
> > > > > wrote:
> > > > > > > > > > > > > > > > > >
> Am I doing anything wrong? Why iBATIS 
> > is so 
> > > slow
> > > > > compared to JDBC?
> > > > > > > > > > > > > > > > > >
> Thanks.
> > > > > > > > > > > > > > > > > >
> 
> > > > >
> > > 
> > ---------------------------------------------------------------------------------------------------------
> > > > > > > > > > > > > > > > > >
> Using iBATIS to insert 1000 records: 
> > (38375 
> > > ms)
> > > > > > > > > > > > > > > > > >
> 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); 
> > > > > > > > > > > > > > > > > >
> }
> > > > > > > > > > > > > > > > > >
> sqlMap.executeBatch();
> > > > > > > > > > > > > > > > > >
>
> > > > > > > > > > > > > > > > > >
> INSERT INTO TEMPTABLE (ORDER_NUMBER,
> > > > > RESOURCE_ID,
> > > > > > > > > > > > > > > > > >
> RESOURCE_TYPE_ID,USER_ID, 
> > ATTACHMENT_ID)
> > > > > > > > > > > > > > > > > >
> VALUES ( #RESULT_DATA.relevance#,
> > > > > > > > > > > > > > > > > >
>
> > > > > #RESULT_DATA.resourceIdentifier.ID#,
> > > > > > > > > > > > > > > > > >
> 4,
> > > > > > > > > > > > > > > > > >
>
> > > > > #UserIdentifier.ID#,
> > > > > > > > > > > > > > > > > >
> 
> > > > > #RESULT_DATA.attachmentIdentifier.ID# )
> > > > > > > > > > > > > > > > > >
>
> > > > > > > > > > > > > > > > > >
> 
> > > > >
> > > 
> > ---------------------------------------------------------------------------------------------------------
> > > > > > > > > > > > > > > > > >
> Using JDBC to insert the same 1000 
> > records 
> > > (391
> > > > > ms)
> > > > > > > > > > > > > > > > > >
> statement =
> > > > > connection.prepareStatement( ""INSERT INTO
> > > > > > > > > > > > > > > > > >
> TEMP_FTS(ORDER_NUMBER,
> > > > > RESOURCE_ID, 
> > > > > > > > > > > > > > > > > >
>
> > > > > RESOURCE_TYPE_ID,USER_ID,ATTACHMENT_ID) " +
> > > > > > > > > > > > > > > > > >
> "VALUES ( ?,?, 4, ?, ?)" );
> > > > > > > > > > > > > > > > > >
> for(int
> > > > > i=0;i<p_resultList.size();i++){
> > > > > > > > > > > > > > > > > >
>
> > > > > > > > > > > > > > > > > >
> AdvancedSearchResultData
> > > > > resultData =
> > > > > > > > > > > > > > > > > >
>
> > > > > (AdvancedSearchResultData)p_resultList.get(i);
> > > > > > > > > > > > > > > > > >
> statement.setLong( 1, i );
> > > > > > > > > > > > > > > > > >
> statement.setLong( 2,
> > > > > resultData.getResourceId() );
> > > > > > > > > > > > > > > > > >
> statement.setLong( 3,
> > > > > p_userID.getID() );
> > > > > > > > > > > > > > > > > >
> statement.setLong( 4,
> > > > > resultData.getAttachmentId() );
> > > > > > > > > > > > > > > > > >
> // attachment_id
> > > > > > > > > > > > > > > > > >
> statement.addBatch();
> > > > > > > > > > > > > > > > > >
> }
> > > > > > > > > > > > > > > > > >
> statement.executeBatch();
> > > > > > > > > > > > > > > > > >
>
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > 
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > 
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > 
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > 
> > 
>

Mime
View raw message