Performance problems getting worse over time

I have a program that uses Spring, Hibernate and MySql. When running a process that inserts or updates a number of entities, we are experiencing a gradual slowdown on object loading. At the start, the time to load an object is very quick, but by the time we've processed a few hundred items the times are getting into a few seconds, and after a couple thousand it's taking 30 seconds. I can execute the same query against the database and get the results in << 1s (that's while the slow code is running and the query is executed against the same database the code is loading from). This information leads me to believe that the problem lies somewhere in my code and isn't a database problem.

Is there anything in general that would account for this type of problem? e.g. not flushing the hibernate template frequently enough, doing it too often, transaction configuration, etc.

I've included some extracts from my configuration files (I tried to pull out the parts that I thought were relevant).

Thanks.

Colin

Code:

---

        <bean id="transactionProxyTemplate" abstract="true" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
                <property name="transactionManager" ref="transactionManager" />
                <property name="transactionAttributes">
                        <props>
                                <prop key="*">PROPAGATION_REQUIRED</prop>
                        </props>
                </property>
        </bean>

---

Code:

---

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd" >
<beans>
        <bean id="widgetDao" parent="transactionProxyTemplate">
                <property name="target">
                        <bean class="com.travelgator.entityupdate.daoimpl.widgetDaoImpl" singleton="false">
                                <property name="hibernateTemplate" ref="hibernateTemplate" />
                        </bean>
                </property>
        </bean>

        <bean id="workflowDao" parent="transactionProxyTemplate">
                <property name="target">
                        <bean class="com.travelgator.entityupdate.daoimpl.WorkflowDaoImpl" singleton="false">
                                <property name="hibernateTemplate" ref="hibernateTemplate" />
                        </bean>
                </property>
        </bean>

        <bean id="widgetProductSax" class="com.travelgator.entityupdate.feeds.widget.widgetProductsElementHandler" singleton="false">
                <property name="dao" ref="widgetDao" />
                <property name="workflowDao" ref="workflowDao" />
                <property name="bookingAgentId" value="${widget.agent.id}" />
        </bean>

        <bean id="widgetProductXmlScriptProcessor" class="com.travelgator.entityupdate.scriptprocessor.XMLScriptProcessor" singleton="false">
                <property name="entityHandlers">
                        <map>
                                <entry key="attraction">
                                        <ref bean="widgetProductSax" />
                                </entry>
                        </map>
                </property>
        </bean>

        <bean id="widgetProduct" parent="transactionProxyTemplate">
                <property name="target">
                        <bean class="com.travelgator.entityupdate.daoimpl.widgetXmlDaoImpl">
                                <property name="hibernateTemplate" ref="hibernateTemplate" />
                                <property name="errorLoggerFactory" ref="errorLogger" />
                                <property name="xmlScriptFile" value="widgetproducts.xml" />
                        </bean>
                </property>
        </bean>

        <bean id="widgetProducts" parent="transactionProxyTemplate">
                <property name="target">
                        <bean class="com.travelgator.entityupdate.feeds.widget.widgetProducts">
                                <property name="hibernateTemplate" ref="hibernateTemplate" />
                            <property name="geographyNameUtil" ref="geographyNameUtil" />
                                <property name="widgetDao" ref="widgetProduct" />
                                <property name="xmlScriptFile" value="widgetproducts.xml" />
                                <property name="feedSchema" value="${stagingdb.schema}" />
                                <property name="productionSchema" value="${websitedb.schema}" />
                                <property name="bookingAgentId" value="${widget.agent.id}" />
                                <property name="processor" ref="widgetProductXmlScriptProcessor" />
                            <property name="scriptsLocation" value="${scripts.location}" />
                            <property name="scriptsCopyLocation" value="${scripts.copyLocation}" />
                        </bean>
                </property>
        </bean>

        <bean id="widgetLoader" parent="transactionProxyTemplate">
                <property name="target">
                        <bean parent="transactionProxyTemplate">
                                <property name="target">
                                        <bean class="com.travelgator.feedload.widget.widgetUSDLoader">
                                                <property name="hibernateTemplate" ref="hibernateTemplateFeeds" />
                                        </bean>
                                </property>
                        </bean>
                </property>
        </bean>

        <bean id="widgetXmlReader" class="com.travelgator.feedload.XMLReader">
                <property name="widgetLoader" ref="widgetLoader" />
                <property name="handler" ref="widgetLoader" />
        </bean>

        <bean id="widgetStageFeed" parent="transactionProxyTemplate">
                <property name="target">
                        <bean class="com.travelgator.feedload.StageFeedImpl">
                                <property name="xmlReader" ref="widgetXmlReader" />
                                <property name="hibernateTemplate" ref="hibernateTemplate" />
                        </bean>
                </property>
        </bean>

        <bean id="widgetFeedDownload" class="com.travelgator.feedload.FeedDownloader">
                <property name="feedConfigFile" value="resources/conf/feedloadconfig.xml" />
                <property name="flParser" ref="flParser" />
                <property name="httpFileDownload" ref="httpFileDownload" />
                <property name="ftpFileDownload" ref="ftpFileDownload" />
                <property name="localFileDownload" ref="localFileDownload" />
                <property name="stageFeed" ref="widgetStageFeed" />
        </bean>
</beans>

---

I don't think it has to do with Spring, but more with Hibernate configuration.
Do you use second level cache ? Eager fetching ?
What queries are performed in that process ?
Can you post the code you try to run ?

Also, set show_sql to true to see what happens behind the scenes with the actual database queries.

I agree that it's probably the configuration of Hibernate, but I want to cover all possibilities. I won't be able to post the code until later today since it is located on another machine I won't have access to until this evening.

The query that is being executed (HQL) is something like:

Code:

---

from a where a.id in (select b.id from b where b.a.id and b.otherId = ?)

---

So a simple select with a subquery restriction. The 30 seconds covers, quite literally, executing the above HQL and then doing a 'return aList.get(0);'

I did turn on the SQL generation previously, and that was where I got the query that I ran using MySql Query Browser in order to discover that the database doesn't appear to be having any problems.

Eager fetching is not being used, but I think we are using the 2nd level cache (I'd need to confirm that however).

Thanks for taking a look.

Is this all within one transaction? Is each insert within a different transaction? Might be worth clearing the Session and see if it makes a difference.

Thanks for that tip. I took a look at how I was handling flushing the session and it turns out that somewhere along the line I managed to remove the call to flush from one section of the code, and just never got around to putting it into another section where it belonged. Turns out that trying to do 5000 inserts and updates in a single batch isn't such a good idea.

Colin

Quote:

---

Originally Posted by colin_young

Thanks for that tip. I took a look at how I was handling flushing the session and it turns out that somewhere along the line I managed to remove the call to flush from one section of the code, and just never got around to putting it into another section where it belonged. Turns out that trying to do 5000 inserts and updates in a single batch isn't such a good idea.

Colin

---

Really, it is much better idea then make them one at a time.

I have dealt with one (Hibernate based) application that has executed ~150000 inserts. When each insert has its own flush it has taken ~6hours to complete. I a single batch ~10 minutes.

Regards,
Oleksandr

Well, really I'm doing 50 at a time (the exact number is of course a property that I can set in the configuration file). It was originally doing 1 at a time and trying to fix that was how I managed to get myself into trouble in the first place.

Using a batch size of 50, my updates went from > 12 hours as a single batch to just under 2 hours (we're not just doing inserts/updates -- we need to do some lookups for each record also). I'm sure with some more work I could get those times down, but for now the performance is acceptable.

I imagine the exact performance will have a lot to do with the size of the objects the code is handling (we have some CLOB fields with quite a bit of data in them), in addition to any other queries that might be executed as part of the processing.

I guess it's just a question of doing some profiling and seeing how it performs. It's hard to tell given the differing requirements.