Re: Performance & SQL Formatting Issue

Pascal Jacob Thu, 04 Jun 2009 13:36:52 -0700

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, andI 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 tofind the place where to put that "poolPreparedStatements" property(It's more than probable that I'm missing one or more Spring beandeclaration 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:jeffgbut...@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 <mggl...@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é.

Re: Performance & SQL Formatting Issue

Reply via email to