[JUDDI-237] Extra select queries are run, one for each search result related to category bag

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Not A Problem
• Fix Version/s: 3.2
• Assignee: Jeff Faath
• Reporter: Jeff Faath
• Created: 23/May/09 21:59
• Updated: 03/Jul/13 02:32
• Resolved: 03/Jul/13 02:32

Description

Consider this scenario:

A find_service operation produces three results and runs this final JPA query to fetch and order the final results:

select distinct bs from BusinessService bs , ServiceName ServiceN_ where bs.entityKey = ServiceN_.businessService.entityKey and bs.entityKey in ( ?, ?, ?) order by ServiceN_.name asc, bs.modified desc

where the three parameters are the resulting keys. This generates the following raw SQL:

select distinct businessse0_.entity_key as entity1_114_, businessse0_1_.authorized_name as authorized2_114_, businessse0_1_.created as created114_, businessse0_1_.modified as modified114_, businessse0_1_.modified_including_children as modified5_114_, businessse0_1_.node_id as node6_114_, businessse0_.business_key as business2_121_ from juddiv3_business_service businessse0_ inner join juddiv3_uddi_entity businessse0_1_ on businessse0_.entity_key=businessse0_1_.entity_key, juddiv3_service_name servicenam1_ where businessse0_.entity_key=servicenam1_.entity_key and (businessse0_.entity_key in (? , ? , ?)) order by servicenam1_.name asc, businessse0_1_.modified desc

select servicecat0_.id as id111_0_, servicecat0_.entity_key as entity2_136_0_ from juddiv3_service_category_bag servicecat0_ inner join juddiv3_category_bag servicecat0_1_ on servicecat0_.id=servicecat0_1_.id where servicecat0_.entity_key=?

select servicecat0_.id as id111_0_, servicecat0_.entity_key as entity2_136_0_ from juddiv3_service_category_bag servicecat0_ inner join juddiv3_category_bag servicecat0_1_ on servicecat0_.id=servicecat0_1_.id where servicecat0_.entity_key=?

select servicecat0_.id as id111_0_, servicecat0_.entity_key as entity2_136_0_ from juddiv3_service_category_bag servicecat0_ inner join juddiv3_category_bag servicecat0_1_ on servicecat0_.id=servicecat0_1_.id where servicecat0_.entity_key=?

The first query is as expected to get the three services. But then, the three extra queries are called to retrieve the category bag, even though nothing related to the category bag was requested in the JPA query. This seems to imply that an extra select query will be called for every result (regardless of how many the user requests). This seems like an opportunity to optimize the search results.