Return-Path: 
 <user-java-return-16179-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 31056 invoked from network); 4 Feb 2009 22:57:23 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 4 Feb 2009 22:57:23 -0000
Received: (qmail 34689 invoked by uid 500); 4 Feb 2009 22:57:19 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 34666 invoked by uid 500); 4 Feb 2009 22:57:19 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 34637 invoked by uid 99); 4 Feb 2009 22:57:19 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 04 Feb 2009 14:57:19 -0800
X-ASF-Spam-Status: No, hits=2.1 required=10.0
	tests=DNS_FROM_RFC_BOGUSMX,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: local policy)
Received: from [66.133.100.91] (HELO mothra.pdc4u.com) (66.133.100.91)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 04 Feb 2009 22:57:12 +0000
Reply-To: <lists@pdc4u.com>
From: "M Goodell" <lists@pdc4u.com>
To: <user-java@ibatis.apache.org>
References: <002a01c98639$cdb4dd10$691e9730$@com>
	 <CGEJILBIFLPKJNINDBEEKEKHDCAA.mgglist@comcast.net>
 <c593b8750902041122w7cb85dc0h3050c96250ff86b3@mail.gmail.com>
In-Reply-To: <c593b8750902041122w7cb85dc0h3050c96250ff86b3@mail.gmail.com>
Subject: RE: Performance & SQL Formatting Issue
Date: Wed, 4 Feb 2009 15:56:47 -0700
Message-ID: <004901c9871b$dbf7d9d0$93e78d70$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Language: en-us
X-Virus-Checked: Checked by ClamAV on apache.org

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
>