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>
