2 Replies Latest reply on Apr 7, 2006 4:59 PM by Frank Langelage

    Problem with inefficient query

    Laurent Duperval Newbie

      Hi,

      I am experiencing an issue with my code where JBoss (or something else) is generating very inefficient queries to the database in order to fetch information.

      Specifically, I have a table with about 40K entries in it. When I try to fetch information form the table (for example, if I select about 2000 of them), it can take five minutes to get the result.

      What I see in the server logs are tens and tens of lines that look like this:

      [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Location] load relation SQL: SELECT location_uuid, location_state_uuid FROM Location WHERE (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?) OR (location_uuid=?)
      


      I'm thinking that might be an inefficient way of dong the query.

      If I do a query in SQL Server (my database) to fetch the 2000 elements, it takes a couple of seconds only. With my setup (CMP, JBoss 3.2.5) the same request takes more than 5 minutes and sends the CPU to the stratosphere.

      I am thinking that

      - either CMP is not the proper way to do this
      - or I am building my deployment descriptors incorrectly and I will need to tweak them to get the performance I need.

      I'm hoping it's option 2.

      I'm using JBoss 3.2.5. Can anyone offer some strategies to bring my performance to an acceptable level, may be 1.5x or 2x the time it takes to do a direct JDBC query?

      Also, while I'm at it, I have a relationship like this:

      Site (*) -- (1) Location

      I have about 60 entries of type Site and the 40K+ of type Location. When I try to retrieve the 60 entries of type Site, it can take two to three minutes, probably because of all the fetches dont on the Location table, but I am not sure about this one.

      This is is my jbosscmp-jdbc.xml file (well, part of it, anyway, it's very large). THere are no other parts related to those two entities.

      Any help is greatly accepted. I'm reading up on read-ahead and commit types to see if I can use one of those two approaches to help but so far, neither of them seems to be what I need.

      Thanks,

      L

       <entity>
       <ejb-name>Location</ejb-name>
      
      
       <!-- Your datasource here -->
      
      
       <table-name>Location</table-name>
       <cmp-field>
       <field-name>uniqueId</field-name>
       <column-name>location_uuid</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_code</field-name>
       <column-name>location_code</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_aid</field-name>
       <column-name>location_aid</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_name</field-name>
       <column-name>location_name</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_addr_1</field-name>
       <column-name>location_addr_1</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_addr_2</field-name>
       <column-name>location_addr_2</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_addr_3</field-name>
       <column-name>location_addr_3</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_city</field-name>
       <column-name>location_city</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_postal_code</field-name>
       <column-name>location_postal_code</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_phone</field-name>
       <column-name>location_phone</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_phone_extension</field-name>
       <column-name>location_phone_extension</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_fax</field-name>
       <column-name>location_fax</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_email</field-name>
       <column-name>location_email</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_latitude</field-name>
       <column-name>location_latitude</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_longitude</field-name>
       <column-name>location_longitude</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>location_radius</field-name>
       <column-name>location_radius</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>editTypeUniqueId</field-name>
       <column-name>location_edit_type_uuid</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>locTypeUniqueId</field-name>
       <column-name>location_loc_type_uuid</column-name>
       </cmp-field>
       <query>
       <query-method>
       <method-name>findByLocationCode</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.location_code LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByStrings</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.location_code LIKE ?1 AND obj.location_name LIKE ?2 AND obj.location_city LIKE ?3 AND obj.state.uniqueId LIKE ?4 AND obj.locTypeUniqueId LIKE ?5
       ]]></jboss-ql>
       </query>
      
      
      
      
      
      
       <query>
       <query-method>
       <method-name>findByEditType</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.editTypeUniqueId LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByLocType</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.locTypeUniqueId LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByState</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.state.uniqueId LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByCode</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.location_code LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByName</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.location_name LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByCodeAndName</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.location_code LIKE ?1 AND obj.location_name LIKE ?2
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByNameAndType</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.location_name LIKE ?1 AND obj.locTypeUniqueId LIKE ?2
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByLocationCodeAndType</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM locationBean obj
       WHERE obj.location_code LIKE ?1 AND obj.locTypeUniqueId LIKE ?2
       ]]></jboss-ql>
       </query>
      
      
      
      
       <!-- additional queries here -->
      
      
       </entity>
      
       <entity>
       <ejb-name>Site</ejb-name>
      
      
       <!-- Your datasource here -->
      
      
       <table-name>Site</table-name>
       <cmp-field>
       <field-name>uniqueId</field-name>
       <column-name>site_uuid</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>site_name</field-name>
       <column-name>site_name</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>site_aid</field-name>
       <column-name>site_aid</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>locationUniqueId</field-name>
       <column-name>site_location_uuid</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>timezoneUniqueId</field-name>
       <column-name>site_time_zone_uuid</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>editTypeUniqueId</field-name>
       <column-name>site_edit_type_uuid</column-name>
       </cmp-field>
       <query>
       <query-method>
       <method-name>findByUniqueName</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM siteBean obj
       WHERE obj.site_name LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
      
      
      
      
      
      
      
      
       <query>
       <query-method>
       <method-name>findByLocation</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM siteBean obj
       WHERE obj.locationUniqueId LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByTimezone</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM siteBean obj
       WHERE obj.timezoneUniqueId LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
       <query>
       <query-method>
       <method-name>findByEditType</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <jboss-ql><![CDATA[
       SELECT OBJECT(obj)
       FROM siteBean obj
       WHERE obj.editTypeUniqueId LIKE ?1
       ]]></jboss-ql>
       </query>
      
      
      
      
       <!-- additional queries here -->
      
      
       </entity>