I'm sorry to ask it again : about PerformanceTest.java by Jeff , all inserts inside loop are executed in same thread
I guess first one is cached into statement cache, isn't it ? So, this test wouldn't be right because text formatting is performed only one time. If I'm wrong in my concept about working of statement cache, please, tell me Thanks and regards 2009/2/3 Jeff Butler <jeffgbut...@gmail.com>: > I had some extra time, so I wrote a test against MySQL. I found no > meaningful difference between formatted and unformatted insert > statements. In both cases I inserted 10K records in a table, and both > took about 5 minutes to run on my laptop (less than 5 seconds variance > between the two tests). > > Test case attached if anyone else wants to try it. > > Jeff Butler > > > On Tue, Feb 3, 2009 at 2:14 PM, Clinton Begin <clinton.be...@gmail.com> wrote: >> 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> >>>> >>>> >>> >>> >> >
