You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-java@ibatis.apache.org by Carsten Zeitz <ca...@gmx.de> on 2006/10/16 22:46:02 UTC

Problem inserting many rows

Hi,

these are my first steps using iBATIS. I'd like to use it in an application to write incoming messages to a MySQL database. I just make a simple test to check the performance. I use a loop to insert 2000 message objects. The result: It inserts 10, then waits, insert another 10, waits, ... and after 32 message it hangs up.

Now where is my problem ???

Here my configuration:

<sqlMapConfig>

 	<properties resource="etc/database.properties"/>

  	<transactionManager type="JDBC">
    	<dataSource type="SIMPLE">
	      	<property value="${driver}" name="JDBC.Driver"/>
	      	<property value="${url}" name="JDBC.ConnectionURL"/>
	  	<property value="${username}" name="JDBC.Username"/>
	    	<property value="${password}" name="JDBC.Password"/>
		</dataSource>
  	</transactionManager>

  	<sqlMap resource="etc/Message.xml"/>

</sqlMapConfig>

<sqlMap namespace="Message">
  	<typeAlias alias="Message" type="Message"/>

  	<insert id="insertMessage" parameterClass="Message">
    		INSERT INTO messages ( timeStamp, ip, text )
    		VALUES ( #timeStamp#, #ip#, #text# )
  	</insert>

</sqlMap>


Reader reader = Resources.getResourceAsReader("etc/ibatis-config.xml");
SqlMapClient sqlmap = SqlMapClientBuilder.buildSqlMapClient(reader);
			
Message tMessage = new Message();

for( int i = 0; i < 2000; i++ )
{
	sqlmap.startTransaction();
	sqlmap.insert("insertMessage", tMessage );
	System.out.println("Insert " + i);
	sqlmap.commitTransaction();
}


I hope you can help me!

Thanks a lot,
Carsten


-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

Re: Problem inserting many rows

Posted by Carsten Zeitz <ca...@gmx.de>.

Hi,

I found the problem myself. The iBATIS transaction manager uses a throttle for the opened transactions. This throttle is limited to 32. Each beginTransaction() increments a counter, an if the limit is reached, no more transactions can be startet. A simple commitTransaction() does not decrement the counter. The endTransaction() finally solves this problem.

Carsten


-------- Original-Nachricht --------
Datum: Mon, 16 Oct 2006 22:46:02 +0200
Von: "Carsten Zeitz" <ca...@gmx.de>
An: user-java@ibatis.apache.org
Betreff: Problem inserting many rows

> Hi,
> 
> these are my first steps using iBATIS. I'd like to use it in an
> application to write incoming messages to a MySQL database. I just make a simple test
> to check the performance. I use a loop to insert 2000 message objects. The
> result: It inserts 10, then waits, insert another 10, waits, ... and after
> 32 message it hangs up.
> 
> Now where is my problem ???
> 
> Here my configuration:
> 
> <sqlMapConfig>
> 
>  	<properties resource="etc/database.properties"/>
> 
>   	<transactionManager type="JDBC">
>     	<dataSource type="SIMPLE">
> 	      	<property value="${driver}" name="JDBC.Driver"/>
> 	      	<property value="${url}" name="JDBC.ConnectionURL"/>
> 	  	<property value="${username}" name="JDBC.Username"/>
> 	    	<property value="${password}" name="JDBC.Password"/>
> 		</dataSource>
>   	</transactionManager>
> 
>   	<sqlMap resource="etc/Message.xml"/>
> 
> </sqlMapConfig>
> 
> <sqlMap namespace="Message">
>   	<typeAlias alias="Message" type="Message"/>
> 
>   	<insert id="insertMessage" parameterClass="Message">
>     		INSERT INTO messages ( timeStamp, ip, text )
>     		VALUES ( #timeStamp#, #ip#, #text# )
>   	</insert>
> 
> </sqlMap>
> 
> 
> Reader reader = Resources.getResourceAsReader("etc/ibatis-config.xml");
> SqlMapClient sqlmap = SqlMapClientBuilder.buildSqlMapClient(reader);
> 			
> Message tMessage = new Message();
> 
> for( int i = 0; i < 2000; i++ )
> {
> 	sqlmap.startTransaction();
> 	sqlmap.insert("insertMessage", tMessage );
> 	System.out.println("Insert " + i);
> 	sqlmap.commitTransaction();
> }
> 
> 
> I hope you can help me!
> 
> Thanks a lot,
> Carsten
> 
> 
> -- 
> Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
> Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

RE: Problem inserting many rows

Posted by Poitras Christian <Ch...@ircm.qc.ca>.

Try inserting with batches (possibly one transaction per batch).

I think MySQL takes a lot of time creating and commiting transactions, so inserting one object at the time is slow...

Christian 

-----Original Message-----
From: Carsten Zeitz [mailto:carsten.zeitz@gmx.de] 
Sent: Monday, 16 October 2006 16:46
To: user-java@ibatis.apache.org
Subject: Problem inserting many rows

Hi,

these are my first steps using iBATIS. I'd like to use it in an application to write incoming messages to a MySQL database. I just make a simple test to check the performance. I use a loop to insert 2000 message objects. The result: It inserts 10, then waits, insert another 10, waits, ... and after 32 message it hangs up.

Now where is my problem ???

Here my configuration:

<sqlMapConfig>

 	<properties resource="etc/database.properties"/>

  	<transactionManager type="JDBC">
    	<dataSource type="SIMPLE">
	      	<property value="${driver}" name="JDBC.Driver"/>
	      	<property value="${url}" name="JDBC.ConnectionURL"/>
	  	<property value="${username}" name="JDBC.Username"/>
	    	<property value="${password}" name="JDBC.Password"/>
		</dataSource>
  	</transactionManager>

  	<sqlMap resource="etc/Message.xml"/>

</sqlMapConfig>

<sqlMap namespace="Message">
  	<typeAlias alias="Message" type="Message"/>

  	<insert id="insertMessage" parameterClass="Message">
    		INSERT INTO messages ( timeStamp, ip, text )
    		VALUES ( #timeStamp#, #ip#, #text# )
  	</insert>

</sqlMap>


Reader reader = Resources.getResourceAsReader("etc/ibatis-config.xml");
SqlMapClient sqlmap = SqlMapClientBuilder.buildSqlMapClient(reader);
			
Message tMessage = new Message();

for( int i = 0; i < 2000; i++ )
{
	sqlmap.startTransaction();
	sqlmap.insert("insertMessage", tMessage );
	System.out.println("Insert " + i);
	sqlmap.commitTransaction();
}


I hope you can help me!

Thanks a lot,
Carsten


--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer