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> | | View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3934956#3934956 Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3934956 ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642 _______________________________________________ JBoss-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/jboss-user
