ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Maves <Nathan.Ma...@Sun.COM>
Subject Re: Am I doing anything wrong? iBATIS has very poor performance on batch inserts compared to JDBC.
Date Thu, 16 Jun 2005 21:56:06 GMT
why are we cc'ing the old alias?


On Jun 16, 2005, at 2:05 PM, Clinton Begin 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