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.be...@gmail.com] Sent: Monday, February 02, 2009 12:42 PM To: user-java@ibatis.apache.org; li...@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 <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("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>
