ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pascal Jacob <za...@wanadoo.fr>
Subject Re: Performance & SQL Formatting Issue
Date Thu, 04 Jun 2009 20:27:50 GMT
OK, I found it, it's in the "dataSource" bean declaration when using a 
"org.apache.commons.dbcp.BasicDataSource" implementation.

I was using a 
"org.springframework.jdbc.datasource.SimpleDriverDataSource" 
implementation, that's why I couldn't find the "poolPreparedStatements" 
property.

On a side track, I had horrible (slow) performance of batch insert, and 
I solve my problem by turning off the "defaultAutoComit" property.

Pascal J.

Pascal Jacob wrote:
> Please, could you publish your updated Spring.xml file. I 'm unable to 
> find the place where to put that "poolPreparedStatements" property 
> (It's more than probable that I'm missing one or more Spring bean 
> declaration in my own context file ;) ).
>
> ZC.
>
> M Goodell wrote:
>> Found the issue.
>>
>> Within the spring.xml file prepared statement pooling needs to be enabled on
>> the data source.
>>
>> <property name="poolPreparedStatements" value="true"/>
>>
>> The default is false. Once set to "true" I am able to roughly achieve the
>> same performance level Mr. Butler has in his tests.
>>
>> 2009-02-04 15:45:17,375 main springibatis.Main INFO - Inserting a total of
>> [20000] records into table.
>> 2009-02-04 15:45:20,015 main springibatis.PersonServiceDAO INFO - Elapsed
>> milli-seconds [FORMATTED]: 2234
>> 2009-02-04 15:45:21,609 main springibatis.PersonServiceDAO INFO - Elapsed
>> milli-seconds [UN-FORMATTED]: 1594
>>
>> Order of execution swapped:
>>
>> 2009-02-04 15:45:51,968 main springibatis.Main INFO - Inserting a total of
>> [20000] records into table.
>> 2009-02-04 15:45:54,421 main springibatis.PersonServiceDAO INFO - Elapsed
>> milli-seconds [UN-FORMATTED]: 2031
>> 2009-02-04 15:45:56,140 main springibatis.PersonServiceDAO INFO - Elapsed
>> milli-seconds [FORMATTED]: 1719
>>
>> -----Original Message-----
>> From: Jeff Butler [mailto:jeffgbutler@gmail.com] 
>> Sent: Wednesday, February 04, 2009 12:22 PM
>> To: user-java@ibatis.apache.org
>> Subject: Re: Performance & SQL Formatting Issue
>>
>> I duplicated this code but removed all the external dependencies
>> (Spring, DBCP, etc.).  I just stripped it down to plain iBATIS and
>> MySQL.
>>
>> My results show this:
>>
>> 20000 Unformatted Inserts: 3 seconds Avg.
>> 20000 Formatted Inserts: 2.5 seconds Avg.
>>
>> HOWEVER, if I reverse the order in which the methods are called, I see this:
>>
>> 20000 Formatted Inserts: 3 seconds Avg.
>> 20000 Unformatted Inserts: 2.5 seconds Avg.
>>
>> So I conclude that the 0.5 second difference has nothing to do with
>> the SQL format, but is attributable to basic startup stuff.  In other
>> words, whichever method is called first is somewhat slower regardless
>> of the format of the SQL.  This is not surprising.  The difference is
>> not exponential, but fairly constant.  If I increase the inserts to
>> 50000 or 100000, the first method is still about 0.5 seconds slower.
>> Again, not surprising.
>>
>> There may be a legitimate issue here, but I'm not convinced it has
>> anything to do with iBATIS or SQL format (sorry).
>>
>> Jeff Butler
>>
>>
>> On Wed, Feb 4, 2009 at 12:29 AM, M. Goodell <mgglist@comcast.net> wrote:
>>   
>>> Attached is a simple application showing perfomance data based on
>>> formatted/unformatted SQL insert statements. IDE=NetBeans 6.5
>>>
>>> 1. You will need to edit the spring.xml file and change the database
>>>     
>> config
>>   
>>> accordingly.
>>> 2. See included: people.sql for SQL DDL
>>> 3. See log4j.properties to adjust log output verbosity.
>>> 4. Dependencies (exact jars I used):
>>>        commons-dbcp-1.2.2.jar
>>>        commons-logging-1.1.1.jar
>>>        commons-logging.jar
>>>        commons-logging-api-1.1.1.jar
>>>        commons-pool-1.4.jar
>>>        ibatis-2.3.4.726.jar
>>>        log4j-1.2.15.jar
>>>        mysql-connector-java-5.1.7-bin.jar
>>>        spring.jar (2.5.6)
>>>
>>> 5. Database:
>>>        MySQL 5.1.30-community
>>>
>>> My results with this application on my system are:
>>>
>>> 2009-02-03 23:15:29,222 main springibatis.Main INFO - Inserting [20000]
>>> records into table.
>>> 2009-02-03 23:15:39,519 main springibatis.Main INFO - Milli-seconds using
>>> [formatted] SQL:10297
>>> 2009-02-03 23:15:41,879 main springibatis.Main INFO - Milli-seconds using
>>> [un-formatted] SQL:2360
>>> 2009-02-03 23:15:41,879 main springibatis.Main INFO - Difference in
>>> milli-seconds: 7937
>>>
>>> If there is more information needed I am glad to provide it.
>>>
>>> A *BIG* Thank You to everyone who has taken the time to look at this. If
>>> it's something I am doing / not doing please keep the name calling to a
>>> minumum. =)
>>>
>>> Thanks!
>>>
>>> M. Goodell
>>>
>>>     
>>
>> ---------------------------------------------------------------------------------------
>> Orange vous informe que cet  e-mail a ete controle par l'anti-virus mail. 
>> Aucun virus connu a ce jour par nos services n'a ete detecte.
>>
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus signature database
3830 (20090205) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>>
>>
>>   
>
> ---------------------------------------------------------------------------------------
> Orange vous informe que cet e-mail a été contrôlé par l'anti-virus mail.
> Aucun virus connu à ce jour par nos services n'a été détecté.
>
>

Mime
View raw message