ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kai Grabfelder <nos...@kaigrabfelder.de>
Subject Re: Performance & SQL Formatting Issue
Date Tue, 03 Feb 2009 17:12:16 GMT
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