ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "M Goodell" <li...@pdc4u.com>
Subject RE: Performance & SQL Formatting Issue
Date Tue, 03 Feb 2009 16:32:49 GMT
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