I will develop a trimmed down test suite and post the source code here later this evening or tomorrow. I use Netbeans, I hope that's OK!
Do I simply attach it as a .zip file to my email? -----Original Message----- From: Kai Grabfelder [mailto:nos...@kaigrabfelder.de] Sent: Tuesday, February 03, 2009 10:12 AM To: user-java@ibatis.apache.org Subject: Re: Performance & SQL Formatting Issue could you attach a stripped down maven/ant/eclipse whatever project with your testcase against an in memory db? Or can you only reproduce the issue with a "real" db? Regards Kai --- Original Nachricht --- Absender: M Goodell Datum: 03.02.2009 17:32 > 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> > >
