At this point I think it would be very telling to run this against another DB... Beyond the whitespace replacement, iBATIS doesn't do anything with your SQL.
Please try it with MySQL and/or H2 Clinton On Tue, Feb 3, 2009 at 10:12 AM, Kai Grabfelder <nos...@kaigrabfelder.de> wrote: > 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> >> >> > >
