ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Performance & SQL Formatting Issue
Date Tue, 03 Feb 2009 20:14:38 GMT
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