You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@juddi.apache.org by "Jeff Faath (JIRA)" <ju...@ws.apache.org> on 2009/05/24 00:01:45 UTC
[jira] Created: (JUDDI-237) Extra select queries are run, one for
each search result related to category bag
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.
[jira] Updated: (JUDDI-237) Extra select queries are run, one for
each search result related to category bag
Posted by "Jeff Faath (JIRA)" <ju...@ws.apache.org>.
[ https://issues.apache.org/jira/browse/JUDDI-237?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jeff Faath updated JUDDI-237:
-----------------------------
Fix Version/s: (was: 3.0)
3.1
Waiting to see if there are performance problems. Moving to later release.
> 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.1
>
>
> 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.