ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bing Zou <xiguamaill...@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 17:42:04 GMT
            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