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

Problem with inefficient query

lduperval Apr 4, 2006 8:51 PM

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>

1. Re: Problem with inefficient query

clairecostello Apr 5, 2006 6:48 AM (in response to lduperval)

Hi there,

Can't give you a direct answer here as it is something I am currently investigating myself but check out the loading strategies for JBoss SQL queries in the Server Guide. I'm using 4.0.2 so there could be some differences here for 3.2.5.

HTH,
Claire
Actions
2. Re: Problem with inefficient query

lafr Apr 7, 2006 4:59 PM (in response to lduperval)

At least with JBoss 4.0.4 the read-ahead page-size in $JBOS_HOME/server/xxx/conf/standardjbosscmp-jdbc.xml is 1000.
The query contains 1000 or'ed key in the where clause and a resultset of 1000 entries is constructed.
For me it was much better to decrease the value to ~20 to get best results. The number will probably depend on the number CPU's available and the RDBMS you use.
Actions

Go to original post