Then, does this mean that prepared statement cache isn't working right ?
I don't know how cache works but I understand that every non-dynamic
statement is build as a prepared statement and cached.
So, XML file shouldn't be parsed if a same statement id is called
several times. By this reason, it doesn't have any sense to invoke a
method for formatting text when statement cache is used.
2009/2/2 Clinton Begin <clinton.be...@gmail.com>:
> 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 <li...@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("insertGatewayMerchantChangedItemUnFormattedSQL",
>> gatewayMerchantChangedItem);
>>
>> B.)
>> getSqlMapClientTemplate().insert("insertGatewayMerchantChangedItemFormattedSQL",
>> 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>
>
