Extra select queries are run, one for each search result related to category bag
--------------------------------------------------------------------------------
Key: JUDDI-237
URL: https://issues.apache.org/jira/browse/JUDDI-237
Project: jUDDI
Issue Type: Improvement
Reporter: Jeff Faath
Assignee: Jeff Faath
Priority: Minor
Fix For: 3.0
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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
