ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "M Goodell" <li...@pdc4u.com>
Subject Performance & SQL Formatting Issue
Date Mon, 02 Feb 2009 18:47:59 GMT
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