DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
Issue Details (XML | Word | Printable)

Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Andy Jefferson
Reporter: Martin Brauner
Votes: 0
Watchers: 1

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

Optimizing the fetch of collection fields (1:N relations) could significantly reduce number of sql statements

Created: 04/Feb/06 09:12 AM   Updated: 28/Nov/13 03:29 PM   Resolved: 27/Nov/13 10:06 AM
Component/s: Queries
Affects Version/s: None
Fix Version/s: 3.2.9

 Description  « Hide
When executing a query, the current JPOX implementation does not immediately fetch all objects B that are part of a collection field of the candidate class A of that query (so there is a A :1 --- :N B relationship), even though the user would expect this according to the given fetch plan (fetch groups and/or fetch depths). Instead, the B objects are fetched when the collection field is accessed during iteration of the A result objects. This means n+1 SQL statements which could be improved. See also thread "How to fetch objects including a 1:N relationship in 1 SQL join statement?" in the forum.

May Q be the generated SQL statement of a JDOQL query on a candidate class A and B the type of a collection field of A.

Case 1: direct join in 1 statement
Q' = select A.*, B.* from A, B where all (+) = and (WHERE clause of Q)
a) in lot of cases the most performant solution
a) would mean more than 1 candidate classes (here: A and B) to be fetched from the sql result set which probably means a lot of changes in the current implementation.
b) if not only 1 collection field of A should be fetched according to the fetch plan, but more, this could lead to a complex join over many tables with a lot of repetitive data being sent from the database in large rows and long query execution times. So this approach, good for 1 relation, can backfire with more relations.
c) a RANGE specified in Q cannot be handled in the database any longer in Q'
d) aggregations in the JDOQL query that result in a GROUP BY clause in Q will cause trouble for Q'
e) probably there can be situations in Q where also ORDER BY or DISTINCT clauses will be a problem

Case 2: separate direct joins
Q' = select B.* from A, B where all (+) = and (WHERE clause of Q)
Q' is executed after Q for each collection field of A according to fetch plan.
a) will eliminate cons a) + b) of case 1
a) cons c) to e) of case 1 remain
b) means 1+k (k = number of :N relations to navigate according fatch plan), but still far better than n+1

Case 3: separate subselect joins
Q' = select 'this'.* from B 'this', (Q) 'that' where all 'this'.fk fields = 'that'.pk fields
Q' is executed after Q for each collection field of A according to fetch plan.
a) would eliminate all cons of case 1
b) relativly easy to implement as SQL string of Q can directly be embedded into Q' (works even when recursively fetching collection fields of B)
a) can be inefficient in relation to a direct join. Don't know how good todays RDBMS query optimizers will handle these subselects. But of course
should still be better than running n statements against the database.

So, personally, I would say Case 3 could be a solution. Would be nice of you can improve JPOX with something like this.


Erik Bengtson added a comment - 04/Feb/06 05:55 PM
There are 2 issues that users of these solutions must be aware:

1- the fetched objects may already be in the cache. The cache is one the main mechanisms that improves the ORM engines performance.
2-may add more I/O for the above reason, and also for returning redundant data in the result set

Andy Jefferson made changes - 26/Sep/06 04:21 PM
Field Original Value New Value
Assignee JPOX Administrator [ admin ]
Andy Jefferson made changes - 29/Nov/07 11:34 AM
Project Core [ 10000 ] RDBMS [ 10111 ]
Key CORE-2672 RDBMS-38
Component/s Queries [ 10111 ]
Component/s JDO [ 10000 ]
Component/s Queries [ 10030 ]
Andy Jefferson made changes - 14/May/12 07:46 PM
Project JPOX RDBMS (ARCHIVED) [ 10111 ] DataNucleus Store RDBMS [ 10144 ]
Component/s Queries [ 10142 ]
Component/s Queries [ 10111 ]
Andy Jefferson made changes - 26/Nov/13 12:19 PM
Comment [ The description on this issue is unrealistic, and the only sensible implementation would be for a query that the user wants to fetch a collection field. In this case do the query as now, and then do a second SQL that fetches all elements for all objects found by the original query. This reduces 1+N queries to 2 queries. Obviously that still leaves the complexity of implementing this ... for a volunteer ]
Andy Jefferson added a comment - 26/Nov/13 12:27 PM
The "simplest" implementation of this would be to have a "bulk-fetch" mode for a SCO collection/map, as in case 3 above. In particular the SQL (for collections) would become
1. FK case
2. JoinTable case

Note that Case 2 would involve much more work, needing to adapt the query generation process to update an existing SQLStatement (for the element) applying all joins to it, so would not be worth the effort.

Note also that there is a Case 4 where instead of EXISTS you could use "OWNER_ID IN (.., .., ..)" but that would only work with single column owner PKs and would be limited by the size of the SQL (and number of IN terms).

Andy Jefferson added a comment - 26/Nov/13 08:48 PM
SVN trunk has case 3 support for JDOQL for fields of type Set (join table, or foreign key) and had basic testing. Still need to test for embedded join table case, add support for lists and maps, and then copy across to JPQL.

Andy Jefferson added a comment - 27/Nov/13 10:06 AM
SVN trunk has this for collection fields now, for JDOQL and JPQL

Andy Jefferson made changes - 27/Nov/13 10:06 AM
Status Open [ 1 ] Resolved [ 5 ]
Assignee Andy Jefferson [ andy ]
Fix Version/s 3.2.9 [ 12049 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 28/Nov/13 03:29 PM
Status Resolved [ 5 ] Closed [ 6 ]