The statement cache operates only within a session/transaction scope,
not between threads or users. So it would entirely depend on the
overall approach.
Clinton
On Mon, Feb 2, 2009 at 1:06 PM, Chema <demablo...@gmail.com> wrote:
> 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>
>>
>
