Announcement Announcement Module
Collapse
No announcement yet.
Business calling several DAO's: JDBC con never closes?!?! Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Business calling several DAO's: JDBC con never closes?!?!

    Hi,

    We are having some trouble with very slow processing time with out application whcih runs on Spring.

    Was wondering if anyone might be able to help with our configuration.

    Here is what we are trying to achieve:

    We have a vey large database. We ahve one table which stroes a list of Nodes on a workflow. When a user selects a node. We need to retrieve a list of Queses which work can be assigned to from that Node.

    Its not that simple though as there is a lot of processing to get this luist as they are not logically linked in the database.

    We are getting a list of "triggers" conected with this node, then filtering them based on whether the "conditions" associated with this node match other options submitted by the user. This si further complicated by each conditon needing us to hit other tables to get lists of values, the tables containing these values could be any one of 5 depending on toher things.

    Ok so you dont need to understand all of that but it describes how many hits to the DB there is. For ach Trigger we must query the DB for a list of Conditions and for each condition hit a specific table to get the values.

    So we end up with LOTS of calls to the DB to get all these things to let our business layer determine which queues associated whcih which triggers are relevent.



    We have OSIV set up so one session is created and bound to one user selection in our online app.
    However When I turn on DEBUG in log4j, I can see that every time a DAO call is made to any of 5 DAO's, there is a pause while it creates another jdbc connection. However it never releases the connection. So after a set number of calls it always hangs unless I set the maxActive of the dbcp setting to -1 (unlimited), but I'm assuming this isn't good!?!?!

    Also, since it creates a new connection each time, it seems to cause a pause in the processing which it opens the connection so our processing is sooooo long.

    To get a list of queues for one node can take up to 5 muinutes as there are so many calls to the DB.


    How do I set it up to use on JDBC connection to speed this up or at the very least, force Hibernate to release a connection once it retrieves the data and not trying to hold it until the end of the Transaction.

    It means we coudl ahve 100 connections open until the whole transaction conpletes. This can't be good.


    Thanks guys, anything in helpoing me understand this or a solution is very appreciated.

  • #2
    It looks like (one of) 2 things

    1) Incorrect datasource configuration
    2) Incorrent/no transaction configuration

    Spring relies on correct transaction configuration to retrieve/release connections. If you don't have proper transaction management in place each call will result in a new connection (and even worse the old one will not be released).

    So post some configuration (use the [ code][/code ] tags!!!!) and code so that we can help.

    Comment


    • #3
      Snippets of our applicationContext.xml

      Code:
      <?xml version="1.0" encoding="UTF-8"?>
      
      <beans xmlns="http://www.springframework.org/schema/beans"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns:context="http://www.springframework.org/schema/context"
          xmlns:cxf="http://cxf.apache.org/core"
          xmlns:jaxws="http://cxf.apache.org/jaxws"
          xsi:schemaLocation="http://www.springframework.org/schema/beans
              http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
              http://www.springframework.org/schema/context
              http://www.springframework.org/schema/context/spring-context-2.5.xsd
              http://cxf.apache.org/core
              http://cxf.apache.org/schemas/core.xsd
              http://cxf.apache.org/jaxws
              http://cxf.apache.org/schemas/jaxws.xsd"
          default-autowire="byName">
      
          <!-- Load CXF modules from cxf.jar -->
          <import resource="classpath:META-INF/cxf/cxf.xml" />
          <import resource="classpath:META-INF/cxf/cxf-extension-soap.xml" />
          <import resource="classpath:META-INF/cxf/cxf-servlet.xml" />
      
      	<!-- START: Database Config -->
      	
      	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
      		<property name="driverClassName" value="${jdbc.driver}" />
      		<property name="url" value="${jdbc.url}" />
      		<property name="username" value="${jdbc.user}" />
      		<property name="password" value="${jdbc.password}" />
      		<property name="maxActive" value="-1"/>
      		<property name="maxIdle" value="10"/>
      		<property name="maxWait" value="1"/>
      		<property name="maxOpenPreparedStatements" value="50"/>
      		<property name="poolPreparedStatements" value="true"/>
      	</bean>	
      	
          <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
      		<property name="configLocation">
      			<value>classpath:config/hibernate.cfg.xml</value>
      		</property>
      		<property name="dataSource">
      			<ref local="dataSource" />
      		</property>
      		<!-- 
      		<property name="hibernateProperties">
         			<ref bean="hibernateProperties" />
      		</property> 
      		-->		
      	</bean>	
      	
          <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
              <property name="sessionFactory"><ref local="sessionFactory"/></property>
          </bean>
          
         <bean id="txProxyTemplate" lazy-init="true" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
              <property name="transactionManager"><ref local="transactionManager"/></property>
              <property name="transactionAttributes">
                  <props>
                      <prop key="loadOrCreate">PROPAGATION_REQUIRED</prop>
                      <prop key="load*">PROPAGATION_SUPPORTS,readOnly,+Throwable</prop>
                      <prop key="get*">PROPAGATION_SUPPORTS,readOnly</prop>
                      <prop key="find*">PROPAGATION_SUPPORTS,readOnly</prop>
                      <prop key="save*">PROPAGATION_REQUIRED</prop>
                      <prop key="create*">PROPAGATION_REQUIRED</prop>
                      <prop key="delete*">PROPAGATION_REQUIRED</prop>
                  </props>
              </property>
          </bean>
      	
      	<!-- END: Database Config -->
      	
      	<!-- START: DataObject Beans -->
      		<!-- <bean id="Node" class="com.fmr.xpdt.application.dataobjects.Node" /> -->
      	<!-- END  : DataObject Beans -->
      	
      	<!-- START: DataAccessObject Layer Beans -->
      		 
          	<bean id="NodeListDAO" parent="txProxyTemplate">
            	  <property name="target">
                 	 <bean class="com.fmr.xpdt.persistence.dao.NodeListDAO">
                     	 <property name="sessionFactory">
                       	   <ref bean="sessionFactory"/>
                     	 </property>
                  	</bean>
             	 	</property>
         	 	</bean>
      		
          	
      
      ............ Other DAO's, Business, etc.....................
      	
      
      </beans>

      I realise our configuration is probably off but i don't quite understand how to configure it, we don't use annotations.

      Thanks!!!

      Comment


      • #4
        Originally posted by Gray View Post
        Hi,

        We are having some trouble with very slow processing time with out application whcih runs on Spring.

        Was wondering if anyone might be able to help with our configuration.

        Here is what we are trying to achieve:

        We have a vey large database. We ahve one table which stroes a list of Nodes on a workflow. When a user selects a node. We need to retrieve a list of Queses which work can be assigned to from that Node.

        Its not that simple though as there is a lot of processing to get this luist as they are not logically linked in the database.

        We are getting a list of "triggers" conected with this node, then filtering them based on whether the "conditions" associated with this node match other options submitted by the user. This si further complicated by each conditon needing us to hit other tables to get lists of values, the tables containing these values could be any one of 5 depending on toher things.

        Ok so you dont need to understand all of that but it describes how many hits to the DB there is. For ach Trigger we must query the DB for a list of Conditions and for each condition hit a specific table to get the values.

        So we end up with LOTS of calls to the DB to get all these things to let our business layer determine which queues associated whcih which triggers are relevent.
        Classic N + 1 problem. Use eager loading and batch fetching.


        Originally posted by Gray View Post
        We have OSIV set up so one session is created and bound to one user selection in our online app.
        However When I turn on DEBUG in log4j, I can see that every time a DAO call is made to any of 5 DAO's, there is a pause while it creates another jdbc connection. However it never releases the connection. So after a set number of calls it always hangs unless I set the maxActive of the dbcp setting to -1 (unlimited), but I'm assuming this isn't good!?!?!

        Also, since it creates a new connection each time, it seems to cause a pause in the processing which it opens the connection so our processing is sooooo long.

        To get a list of queues for one node can take up to 5 muinutes as there are so many calls to the DB.


        How do I set it up to use on JDBC connection to speed this up or at the very least, force Hibernate to release a connection once it retrieves the data and not trying to hold it until the end of the Transaction.

        It means we coudl ahve 100 connections open until the whole transaction conpletes. This can't be good.


        Thanks guys, anything in helpoing me understand this or a solution is very appreciated.
        Looks like you didn't configure transactions management. Look at the following reference chapters - 11. Transaction management and 14.2.7 Declarative transaction demarcation.

        Comment


        • #5
          Apologies, I tried to reply with the code but it didn't submit for me. I will try again in a while when at my desk.

          We are having some problems with collections howeever.

          We would technically load a set of Conditions with each Trigger but since the Conditions table has no unique primary key, hibernate doesn't seem to like this or can't handle it as the correct values are not returned no matter how we've tried to configure it.

          Also, Since the calls to check the Fields and the Values of the Conditions depend on the actual value to determine which table we will hit to compare them, I don't know how we can add these at eagerly.
          e.g. If A Condition has a field of 2000 or more we go to one table, or if its less we got to another (there are actually more conditions than this), so we are doing this in our business logic.

          Also, we only want to retrieve the list of values for any conditions where we can match its field with the list of fields returned so we save some calls to the DB by not making the call unless we have to.

          However, its still a lot of calls for one transaction so we aren't sure how to get around it unless we are loading several large tables into memory at once which can't be good either I assume.

          Thanks for the continued advice guys, much appreciated.

          I will look into configuring our tranaction manager better as this already has yielded an improvement in reponce of individual queries.

          Thanks.

          Comment


          • #6
            Originally posted by Gray View Post
            Apologies, I tried to reply with the code but it didn't submit for me. I will try again in a while when at my desk.

            We are having some problems with collections howeever.

            We would technically load a set of Conditions with each Trigger but since the Conditions table has no unique primary key, hibernate doesn't seem to like this or can't handle it as the correct values are not returned no matter how we've tried to configure it.

            Also, Since the calls to check the Fields and the Values of the Conditions depend on the actual value to determine which table we will hit to compare them, I don't know how we can add these at eagerly.
            e.g. If A Condition has a field of 2000 or more we go to one table, or if its less we got to another (there are actually more conditions than this), so we are doing this in our business logic.

            Also, we only want to retrieve the list of values for any conditions where we can match its field with the list of fields returned so we save some calls to the DB by not making the call unless we have to.

            However, its still a lot of calls for one transaction so we aren't sure how to get around it unless we are loading several large tables into memory at once which can't be good either I assume.

            Thanks for the continued advice guys, much appreciated.
            Well, I believe it can be improved at least via performing batch queries. It's still possible to write complex sql by hands if hibernate is not smart enough to generate the efficient one. E.g. SQL CASE statement can be used. Anyway, it's really better to consult dba about improving performance and database tuning.

            Also you can reduce number of database roundtrips via exploiting hibernate's second level cache.

            Comment

            Working...
            X