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 16:15:26 GMT
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