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 >
