Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 6835 invoked from network); 3 Feb 2009 22:49:45 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 3 Feb 2009 22:49:45 -0000 Received: (qmail 57665 invoked by uid 500); 3 Feb 2009 22:49:42 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 57645 invoked by uid 500); 3 Feb 2009 22:49:42 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 57636 invoked by uid 99); 3 Feb 2009 22:49:42 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Feb 2009 14:49:42 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of demablogia@gmail.com designates 209.85.220.18 as permitted sender) Received: from [209.85.220.18] (HELO mail-fx0-f18.google.com) (209.85.220.18) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Feb 2009 22:49:35 +0000 Received: by fxm11 with SMTP id 11so2680152fxm.0 for ; Tue, 03 Feb 2009 14:49:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type :content-transfer-encoding; bh=Bi25vvwLVfcQrgrdE01YJD+2asb0/HbZ6MyGuem4Bks=; b=r7JGNwbucG5/OTa9dqWVYbyq1RFzCTIcYhCVpP7dHoy28qvOIqnBCJvwbgPWlrNYCP Vo/nptK1h+S3dJuRMrpIDWBYdZYbcK1hGoLBSV3LoQ1HCopjS7468Cf9AnWb5BJpbhZl u5QHluAQw0GySosKVCPNqbMOvJ2JnsfjJgSt4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type:content-transfer-encoding; b=XtZQVFXgsK18l3doVutVIU+MBFIjSoWY6JIZhwIDf+Q/Uczx1Zs0IvarXzpqAcL58s KKMPdZKo8Gf9w/lEkOfpz33V86FV4IPK8WoRRW+19JFiCK7biZrP6gUxfr6Z9KH1aa7X e6uzA8NkwG8uaqL8vMAkuRW0cAKNb943Y784k= MIME-Version: 1.0 Received: by 10.181.141.18 with SMTP id t18mr599813bkn.205.1233701353507; Tue, 03 Feb 2009 14:49:13 -0800 (PST) In-Reply-To: References: <006701c98566$cae6ede0$60b4c9a0$@com> <16178eb10902021141p3146f89bg8274489cdeb07c0f@mail.gmail.com> <001a01c9861d$0def2600$29cd7200$@com> <49887AF0.40403@kaigrabfelder.de> <16178eb10902031214s68e7dbeau1998849ebe19b7bf@mail.gmail.com> Date: Tue, 3 Feb 2009 23:49:13 +0100 Message-ID: <55efbd800902031449v694b2cbbh9edb70249a239ee3@mail.gmail.com> Subject: Re: Performance & SQL Formatting Issue From: Chema To: user-java@ibatis.apache.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org I'm sorry to ask it again : about PerformanceTest.java by Jeff , all inserts inside loop are executed in same thread I guess first one is cached into statement cache, isn't it ? So, this test wouldn't be right because text formatting is performed only one time. If I'm wrong in my concept about working of statement cache, please, tell me Thanks and regards 2009/2/3 Jeff Butler : > I had some extra time, so I wrote a test against MySQL. I found no > meaningful difference between formatted and unformatted insert > statements. In both cases I inserted 10K records in a table, and both > took about 5 minutes to run on my laptop (less than 5 seconds variance > between the two tests). > > Test case attached if anyone else wants to try it. > > Jeff Butler > > > On Tue, Feb 3, 2009 at 2:14 PM, Clinton Begin wrote: >> At this point I think it would be very telling to run this against >> another DB... Beyond the whitespace replacement, iBATIS doesn't do >> anything with your SQL. >> >> Please try it with MySQL and/or H2 >> >> Clinton >> >> On Tue, Feb 3, 2009 at 10:12 AM, Kai Grabfelder wrote: >>> could you attach a stripped down maven/ant/eclipse whatever project with your testcase against an in memory >>> db? Or can you only reproduce the issue with a "real" db? >>> >>> Regards >>> >>> Kai >>> >>> --- Original Nachricht --- >>> Absender: M Goodell >>> Datum: 03.02.2009 17:32 >>>> I swapped out jar files: ibatis-2.3.4.726 for ibatis-2.3.4.731 and ran the >>>> same test suite. Again, this was inserting 10,222 records into a table. >>>> (See other post for details on the application) >>>> >>>> The results are as follows: >>>> >>>> Formatted SQL Un-Formatted SQL >>>> -------------------------------------------- >>>> 3 mins 20 sec 42 secs >>>> >>>> Again, I am more than willing to provide logs, application code whatever to >>>> assist in a resolution. >>>> >>>> Regards, >>>> >>>> M. Goodell >>>> >>>> -----Original Message----- >>>> From: Clinton Begin [mailto:clinton.begin@gmail.com] >>>> Sent: Monday, February 02, 2009 12:42 PM >>>> To: user-java@ibatis.apache.org; lists@pdc4u.com >>>> Subject: Re: Performance & SQL Formatting Issue >>>> >>>> This is a good summary. I've been watching the other thread. >>>> >>>> I think I might have found a possible candidate: >>>> >>>> public class SqlText implements SqlChild { >>>> //... >>>> public void setText(String text) { >>>> this.text = text.replace('\r', ' ').replace('\n', ' ').replace('\t', ' >>>> '); >>>> this.isWhiteSpace = text.trim().length() == 0; >>>> } >>>> //... >>>> } >>>> >>>> I'll have to wait to get home to check to see if this is called on >>>> each execution, instead of just once at SQL mapper build time. >>>> >>>> Cheers, >>>> Clinton >>>> >>>> >>>> On Mon, Feb 2, 2009 at 11:47 AM, M Goodell wrote: >>>>> Sorry for the re-post of this topic. I am using the work account for this >>>>> mailing list. >>>>> >>>>> >>>>> >>>>> Anyway, I believe I have discovered a performance issue either with iBATIS >>>>> itself or something internal to it. After a lot of testing I have narrowed >>>>> it down to how the SQL is formatted. The current application I have is >>>>> inserting 10,222 records into a table from a parsed CSV file. I am using >>>>> Spring and the method is wrapped in a transaction with: >>>>> >>>>> >>>>> >>>>> @Transactional >>>>> >>>>> void persistGatewayMerchantChangedFile(File file) throws >>>> ParseException, >>>>> IOException; >>>>> >>>>> >>>>> >>>>> This method is simple. It reads a line of the CSV file, parses it, >>>> populates >>>>> an object, inserts the data into the table. >>>>> >>>>> >>>>> >>>>> What I have found is this: >>>>> >>>>> >>>>> >>>>> 1.) If formatted SQL is used (See: >>>>> insertGatewayMerchantChangedItemFormattedSQL below) the performance is >>>>> awful. (Roughly 3 minutes) >>>>> >>>>> 2.) If non-formatted SQL is used (See: >>>>> insertGatewayMerchantChangedItemUnFormattedSQL below) the performance is >>>>> great! (Roughly 33 seconds) >>>>> >>>>> >>>>> >>>>> The *only* change made to obtain these results is swapping items A & B >>>>> within the code. >>>>> >>>>> >>>>> >>>>> A.) >>>>> >>>> getSqlMapClientTemplate().insert("insertGatewayMerchantChangedItemUnFormatte >>>> dSQL", >>>>> gatewayMerchantChangedItem); >>>>> >>>>> B.) >>>>> >>>> getSqlMapClientTemplate().insert("insertGatewayMerchantChangedItemFormattedS >>>> QL", >>>>> gatewayMerchantChangedItem); >>>>> >>>>> >>>>> >>>>> I would be very interested if anyone else is seeing this or would be >>>> willing >>>>> to test what I have shown here and see if there is in fact a performance >>>> hit >>>>> with formatted SQL statements opposed to non-formatted statements. >>>>> >>>>> >>>>> >>>>> I will gladly supply and required information i.e. log files, code etc to >>>>> help out and see what the issue is. >>>>> >>>>> >>>>> >>>>> Thank you in advance! >>>>> >>>>> >>>>> >>>>> M. Goodell >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>>> PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" >>>>> >>>>> "http://ibatis.apache.org/dtd/sql-map-2.dtd"> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> class="com.pdc4u.GatewayMerchantChangedItem"> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> column="current_check_status"/> >>>>> >>>>> >>>> column="current_funding_status"/> >>>>> >>>>> >>>> column="authorization_number"/> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> column="drivers_license_number"/> >>>>> >>>>> >>>> column="driver_license_state"/> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> parameterClass="com.pdc4u.GatewayMerchantChangedItem"> >>>>> >>>>> INSERT INTO global_e_gmcr (received_date, transaction_date, >>>>> batch_date, location, terminal, current_check_status, >>>>> current_funding_status, authorization_number, routing_number, >>>>> account_number, check_number, check_amount, deposit_date, transaction_id, >>>>> check_writer, drivers_license_number, driver_license_state, merchant, >>>>> custom_info_one, custom_info_two, custom_info_three, custom_info_four, >>>>> custom_info_five, cross_reference_id) VALUES (#receivedDate#, >>>>> #transactionDate#, #batchDate#, #location#, #terminal#, >>>>> #currentCheckStatus#, #currentFundingStatus#, #authorizationNumber#, >>>>> #routingNumber#, #accountNumber#, #checkNumber#, #checkAmount#, >>>>> #depositDate#, #transactionID#, #checkWriter#, #driversLicenseNumber#, >>>>> #driversLicenseState#, #merchant#, #customInfoOne#, #customInfoTwo#, >>>>> #customInfoThree#, #customInfoFour#, #customInfoFive#, >>>> #crossReferenceID#); >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> parameterClass="com.pdc4u.GatewayMerchantChangedItem"> >>>>> >>>>> INSERT INTO global_e_gmcr ( >>>>> >>>>> received_date, >>>>> >>>>> transaction_date, >>>>> >>>>> batch_date, >>>>> >>>>> location, >>>>> >>>>> terminal, >>>>> >>>>> current_check_status, >>>>> >>>>> current_funding_status, >>>>> >>>>> authorization_number, >>>>> >>>>> routing_number, >>>>> >>>>> account_number, >>>>> >>>>> check_number, >>>>> >>>>> check_amount, >>>>> >>>>> deposit_date, >>>>> >>>>> transaction_id, >>>>> >>>>> check_writer, >>>>> >>>>> drivers_license_number, >>>>> >>>>> driver_license_state, >>>>> >>>>> merchant, >>>>> >>>>> custom_info_one, >>>>> >>>>> custom_info_two, >>>>> >>>>> custom_info_three, >>>>> >>>>> custom_info_four, >>>>> >>>>> custom_info_five, >>>>> >>>>> cross_reference_id >>>>> >>>>> ) VALUES ( >>>>> >>>>> #receivedDate#, >>>>> >>>>> #transactionDate#, >>>>> >>>>> #batchDate#, >>>>> >>>>> #location#, >>>>> >>>>> #terminal#, >>>>> >>>>> #currentCheckStatus#, >>>>> >>>>> #currentFundingStatus#, >>>>> >>>>> #authorizationNumber#, >>>>> >>>>> #routingNumber#, >>>>> >>>>> #accountNumber#, >>>>> >>>>> #checkNumber#, >>>>> >>>>> #checkAmount#, >>>>> >>>>> #depositDate#, >>>>> >>>>> #transactionID#, >>>>> >>>>> #checkWriter#, >>>>> >>>>> #driversLicenseNumber#, >>>>> >>>>> #driversLicenseState#, >>>>> >>>>> #merchant#, >>>>> >>>>> #customInfoOne#, >>>>> >>>>> #customInfoTwo#, >>>>> >>>>> #customInfoThree#, >>>>> >>>>> #customInfoFour#, >>>>> >>>>> #customInfoFive#, >>>>> >>>>> #crossReferenceID#); >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >