Hello Rick,
I've put together a test class and required entities as part of a .zip
attachment to this mail. I did not manage to re-create the problem of the
Blob column being retrieved in a separate select (as is still happening on
with my system) however I was able to get the same effect when retrieving an
entity relationship, which is also being done in N+1 selects (same issue as
with the column above). The issue does not occur when using a fetch call
instead of a join, as also shown in the test case.
Everything is performed on the 2.1.2-SNAPSHOT branch.
In the test case are two test methods. The first one will retrieve the
relationship by calling .join() on the query root. The second case retrieves
the relationship using .fetch().
In case of the .join() call the second entitiy is being retrieved one at the
time, resulting in N+1 queries. Which leads to about 10x worse performance
in my real world code.
Join<LogischRapport, Raptaalmetadata> raptaalmetadata =
reportRoot.join("raptaalmetadata");
*I get the following initial SQL:*
1847 test TRACE [main] openjpa.jdbc.SQL - <t 1195394880, conn 670181362>
executing prepstmnt 1790667451 SELECT t0.rapportnr, t0.rapporttype,
t0.author, t0.BATCH_NBR_HITS, t0.BATCH_RF_CODE, t0.BO_REPORT_TYPE_ID,
t0.BURSTING_MODE, t0.comments, t0.DATUM_CREATIE, t0.DATUM_LAATSTE_WIJZIGING,
t0.dca, t0.DEFAULT_PRIORITY_ID, t0.EXTERN_RAPPORT_FLAG, t0.geschrapt,
t0.LAATSTE_INSTANTIE_NUMMER, t0.OBJECT_NAME, t0.ONLINE_NBR_HITS,
t0.ONLINE_RF_CODE, t0.printdienstid, t0.RAPPORT_CONSULTATIE,
t0.REFRESH_PRINT_DISABLE_FLAG, t0.REPCOMMENT_MODIFIED_BY,
t0.REPCOMMENT_MODIFIED_DATE, t0.REPORT_COMMENTS, t0.REPORTING_UNIT_ID,
t0.TYPE_BURSTING, t0.vertrouwelijkheidscode FROM LogischRapport t0 INNER
JOIN Raptaalmetadata t1 ON t0.rapportnr = t1.rapportnr AND t0.rapporttype =
t1.rapporttype ORDER BY t0.rapportnr ASC, t0.rapporttype ASC
*Followed by several calls to get the two related enities:*
1886 test TRACE [main] openjpa.jdbc.SQL - <t 1195394880, conn 670181362>
executing prepstmnt 567222735 SELECT t0.isotaalcode, t0.rapportnr,
t0.rapporttype, t0.FUNCDESC_MODIFIED_BY, t0.FUNCDESC_MODIFIED_DATE,
t0.FUNCTIONAL_DESCRIPTION, t0.omschrijving, t0.ORATEXT_INDEX_KOLOM,
t0.ORATEXT_SYNC, t0.titel FROM Raptaalmetadata t0 WHERE t0.rapportnr = ? AND
t0.rapporttype = ? [params=?, ?]
1890 test TRACE [main] openjpa.jdbc.SQL - <t 1195394880, conn 670181362>
executing prepstmnt 1117405850 SELECT t0.isotaalcode, t0.rapportnr,
t0.rapporttype, t0.FUNCDESC_MODIFIED_BY, t0.FUNCDESC_MODIFIED_DATE,
t0.FUNCTIONAL_DESCRIPTION, t0.omschrijving, t0.ORATEXT_INDEX_KOLOM,
t0.ORATEXT_SYNC, t0.titel FROM Raptaalmetadata t0 WHERE t0.rapportnr = ? AND
t0.rapporttype = ? [params=?, ?]
*In case of the .fetch() call the second entitiy is being retrieved in one
statement:*
Fetch<LogischRapport, Raptaalmetadata> raptaalmetadata =
reportRoot.fetch("raptaalmetadata");
33 test TRACE [main] openjpa.jdbc.SQL - <t 1195394880, conn 723987239>
executing prepstmnt 120915765 SELECT t0.rapportnr, t0.rapporttype,
t0.author, t0.BATCH_NBR_HITS, t0.BATCH_RF_CODE, t0.BO_REPORT_TYPE_ID,
t0.BURSTING_MODE, t0.comments, t0.DATUM_CREATIE, t0.DATUM_LAATSTE_WIJZIGING,
t0.dca, t0.DEFAULT_PRIORITY_ID, t0.EXTERN_RAPPORT_FLAG, t0.geschrapt,
t0.LAATSTE_INSTANTIE_NUMMER, t0.OBJECT_NAME, t0.ONLINE_NBR_HITS,
t0.ONLINE_RF_CODE, t0.printdienstid, t0.RAPPORT_CONSULTATIE,
t0.REFRESH_PRINT_DISABLE_FLAG, t0.REPCOMMENT_MODIFIED_BY,
t0.REPCOMMENT_MODIFIED_DATE, t0.REPORT_COMMENTS, t0.REPORTING_UNIT_ID,
t0.TYPE_BURSTING, t0.vertrouwelijkheidscode, t1.rapportnr, t1.rapporttype,
t1.isotaalcode, t1.FUNCDESC_MODIFIED_BY, t1.FUNCDESC_MODIFIED_DATE,
t1.FUNCTIONAL_DESCRIPTION, t1.omschrijving, t1.ORATEXT_INDEX_KOLOM,
t1.ORATEXT_SYNC, t1.titel FROM LogischRapport t0 INNER JOIN Raptaalmetadata
t1 ON t0.rapportnr = t1.rapportnr AND t0.rapporttype = t1.rapporttype ORDER
BY t0.rapportnr ASC, t0.rapporttype ASC, t1.rapportnr ASC, t1.rapporttype
ASC
Maybe I'm just missing something on how the .join() statement is supposed to
work? We've switched to .fetch() in this case to get our results in a
performant way. But since fetch does not result in a PATH expression and
join does, I'd really like to know what's going on with joins.
Thanks!
Max test_cases_mxvs.zip
<http://openjpa.208410.n2.nabble.com/file/n7586169/test_cases_mxvs.zip>
--
View this message in context:
http://openjpa.208410.n2.nabble.com/OpenJPA-retrieving-one-column-with-a-seperate-select-tp7586156p7586169.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
