Support bulk-fetch using JOIN

[andyjefferson]

If we have a JDOQL query like
SELECT FROM Person WHERE this.firstName == :value

then this becomes
SELECT P.* FROM PERSON P WHERE P.FIRST_NAME = ?

If a Person has a Set

then if the addresses field is in the fetch plan we already support a bulk-fetch mode "EXISTS" giving SQL of
SELECT A.* FROM ADDRESS A WHERE EXISTS (SELECT P.ID FROM PERSON P WHERE P.FIRST_NAME = ? AND A.PERSON_ID = P.ID)

We could potentially have a bulk-fetch mode "JOIN" as
SELECT A.* FROM PERSON P, ADDRESS A WHERE A.PERSON_ID = P.ID AND P.FIRST_NAME = ?

The reason why this is more complicated than the EXISTS case is that for EXISTS we can make use of the backing store getIteratorStatement for the basic statement, and then put the original query in an EXISTS clause. Here we need to start from the basic query (but clearing the select) and then adding the join to the element, while catering for all different combinations of set/list/collection whether with embedded elements or not, and whether via FK or JoinTable.

[Hexiaoqiao]

any plan to optimize the generated SQL?

[andyjefferson]

No. That is dependent on contributions, this being open source and all. As per the "unresourced" tag on this issue

[shawnweeks]

Another option that is supported on several databases is to use an "IN" clause instead of "EXISTS" which would be implicitly converted to a join without any of the risks associated with inadvertent many to many relationships. That would probably be a lot easier to implement than the join as the SQL is closely related to exists. I normally work on Hadoop Projects but since I'm looking at using some of this I'll start getting familiar with the code base and see if I can help.

[shawnweeks]

Based on some testing against PostgreSQL 10.3, MaraiDB 10.2 and Oracle 12.2 this optimization is already happening with "EXISTS" and "IN". I can post the test scripts for other folks to look at but assuming you can use a relatively modern release of your database software you're already getting the benefit of using a join.

[andyjefferson]

Thx for your input, interesting to hear.

A comparison of the 3 "bulk"/"batch" options (EXISTS, IN, JOIN) for EclipseLink JPA is present on this link https://java-persistence-performance.blogspot.co.uk/2010/08/batch-fetching-optimizing-object-graph.html

[aleferri]

Note that you can force DN to generate queries and subqueries with IN clause already, even if it cost at least one more query, in practice i have implemented something like this in my generic endpoint: i start scanning relation tables and return the top populated table and the number of rows, if it is big enough then i first search ids only and then use the knowledge of found ids to complement the filter, for the main query this is more work to do, but the subqueries on big tables are 20x faster, from 12 secs to 600 ms, so it is worth the trouble:
if (estimateBulkFetchRelationsCost(entityName) > 5000) { List<Integer> ids= findOnlyIds(query); addFilterToQuery(query, "##optimizedList##.contains(id)", ids, query); .... }

[nscuro]

The behavior of effectively selecting the entire join table when no query filters are provided is very unfortunate, since it affects all list operations that rely on pagination to restrict result set sizes.

I created a reproducer here: https://github.com/nscuro/datanucleus-test-jdo/tree/issue-171

The JDOQL query ends up generating the following SQL for bulk-fetching Tags:

SELECT 'mydomain.model.Tag' AS DN_TYPE
     , A1.ID,A1."NAME"
     , A0.ID_EID
  FROM PERSON_TAGS A0
 INNER JOIN TAG A1
    ON A0.ID_OID = A1.ID
 WHERE EXISTS (
   SELECT 'mydomain.model.Person' AS DN_TYPE
        , A0_SUB.ID AS DN_APPID
     FROM PERSON A0_SUB
    WHERE A0.ID_EID = A0_SUB.ID
 )

The EXISTS subquery doesn't really do anything other than waste resources in this case.

Unfortunately this also happens even when the candidate query is limited using .setRange. In fact, the bulk-fetch query is executed even if the candidate query returns no results at all, as demonstrated in the reproducer.

The core problem IMO is that results of the candidate query are not available to bulk-fetch queries. Because even if the candidate query has filters on it, you will always end up fetching too many records, since the EXISTS subquery cannot take range into account.

The proposed join strategy will have the same problem, since it also merely repeats filters of the candidate query.

Ideally, what we'd want is something like this:

SELECT 'mydomain.model.Tag' AS DN_TYPE
     , A1.ID,A1."NAME"
     , A0.ID_EID
  FROM PERSON_TAGS A0
 INNER JOIN TAG A1
    ON A0.ID_OID = A1.ID
 WHERE A0.ID_EID IN (:candidateIds)

This would meaningfully limit the result set size, while completely eliminating the need of fiddling with applying filters from the candidate query.

I am not sure how realistic that is to implement. But as it stands right now, disabling bulk-fetch entirely is the only option when join tables become very large.