ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Butler <jeffgbut...@gmail.com>
Subject Re: Performance & SQL Formatting Issue
Date Tue, 03 Feb 2009 22:14:49 GMT
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 <clinton.begin@gmail.com> 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 <nospam@kaigrabfelder.de> 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 <lists@pdc4u.com> 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
>>>>
>>>>
>>>>
>>>> <?xml version="1.0" encoding="UTF-8"?>
>>>>
>>>>
>>>>
>>>> <!DOCTYPE sqlMap
>>>>
>>>>     PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>>>>
>>>>     "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>>>>
>>>>
>>>>
>>>> <sqlMap>
>>>>
>>>>
>>>>
>>>>     <resultMap id="gatewayMerchantChangedItemResultMap"
>>>> class="com.pdc4u.GatewayMerchantChangedItem">
>>>>
>>>>         <result property="receivedDate" column="received_date"/>
>>>>
>>>>         <result property="transactionDate" column="transaction_date"/>
>>>>
>>>>         <result property="batchDate" column="batch_date"/>
>>>>
>>>>         <result property="location" column="location"/>
>>>>
>>>>         <result property="terminal" column="terminal"/>
>>>>
>>>>         <result property="currentCheckStatus"
>>>> column="current_check_status"/>
>>>>
>>>>         <result property="currentFundingStatus"
>>>> column="current_funding_status"/>
>>>>
>>>>         <result property="authorizationNumber"
>>>> column="authorization_number"/>
>>>>
>>>>         <result property="routingNumber" column="routing_number"/>
>>>>
>>>>         <result property="accountNumber" column="account_number"/>
>>>>
>>>>         <result property="checkNumber" column="check_number"/>
>>>>
>>>>         <result property="checkAmount" column="check_amount"/>
>>>>
>>>>         <result property="depositDate" column="deposit_date"/>
>>>>
>>>>         <result property="transactionID" column="transaction_id"/>
>>>>
>>>>         <result property="checkWriter" column="check_writer"/>
>>>>
>>>>         <result property="driversLicenseNumber"
>>>> column="drivers_license_number"/>
>>>>
>>>>         <result property="driversLicenseState"
>>>> column="driver_license_state"/>
>>>>
>>>>         <result property="merchant" column="merchant"/>
>>>>
>>>>         <result property="customInfoOne" column="custom_info_one"/>
>>>>
>>>>         <result property="customInfoTwo" column="custom_info_two"/>
>>>>
>>>>         <result property="customInfoThree" column="custom_info_three"/>
>>>>
>>>>         <result property="customInfoFour" column="custom_info_four"/>
>>>>
>>>>         <result property="customInfoFive" column="custom_info_five"/>
>>>>
>>>>         <result property="crossReferenceID" column="cross_reference_id"/>
>>>>
>>>>     </resultMap>
>>>>
>>>>
>>>>
>>>>     <insert id="insertGatewayMerchantChangedItemUnFormattedSQL"
>>>> 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#);
>>>>
>>>>     </insert>
>>>>
>>>>
>>>>
>>>>     <insert id="insertGatewayMerchantChangedItemFormattedSQL"
>>>> 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#);
>>>>
>>>>     </insert>
>>>>
>>>>
>>>>
>>>> </sqlMap>
>>>
>>>
>>
>>
>

Mime
View raw message