You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by mxvs <mx...@gmail.com> on 2014/03/25 19:51:27 UTC
OpenJPA retrieving one column a seperate select
Hello, I've been using OpenJPA 2.1.2 (JPA 2.0) to retrieve data from a table
called LOGISCHRAPPORT which has about 28 columns. In some cases OpenJPA
decides to issue seperate select statements for one of the columns for no
particular reason, leading to a dramatic decrease in query performance.
Initially everything goes fine and all my columns are retrieved in a
performant, single SELECT statement by JPA.
As soon as I add a relationship to another entity called RAPTAALMETADATA
@OneToMany(fetch=FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumns({
@JoinColumn(name = "RAPPORTNR", referencedColumnName = "RAPPORTNR"),
@JoinColumn(name = "RAPPORTTYPE", referencedColumnName =
"RAPPORTTYPE") })
private List<Raptaalmetadata> raptaalmetadata;
---
Queried using Criteria API as follows:
---
Join<LogischRapport, Raptaalmetadata> metadata = reportRoot.join(
"raptaalmetadata");
JPA no longer includes one of my original columns called REPORT_COMMENTS
instead it is issuing separate select statements to retrieve the
REPORT_COMMENTS column for each instance of LOGISCHRAPPORT. All other
columns (including the ones coming from RAPTAALMETADATA are retrieved
properly as part of the intial SELECT.
REPORT_COMMENTS is of the HUGEBLOB type in Oracle and I've mapped in in my
Entity as follows:
@Lob
@Basic
@Column(name = "REPORT_COMMENTS")
private byte[] reportComments;
I now get tons of these:
SELECT t0.REPORT_COMMENTS
FROM dwhsd001.LogischRapport t0
WHERE t0.rapportnr = ? AND t0.rapporttype = ?
[params=(long) 1473, (String) RAP]
Additionally: as soon as I remove the fetch=FetchType.EAGER attribute from
the @OneToMany annotation described above I start seeing the exact same
behavior for the relationship as I've been getting for the REPORT_COMMENTS
column. This means I'm also getting separate SELECT statements for
retrieving the entity relationship on top of the seperate selects for the
column thereby further degrading performance.
In other words I'm then also getting tons of these:
SELECT t0.isotaalcode, t0.rapportnr, t0.rapporttype,
t0.FUNCDESC_MODIFIED_BY, t0.FUNCDESC_MODIFIED_DATE,
t0.FUNCTIONAL_DESCRIPTION, t0.omschrijving, t0.titel
FROM dwhsd001.Raptaalmetadata t0
WHERE t0.rapportnr = ? AND t0.rapporttype = ?
Its not a LAZY loading problem as I've specifically tested that case. I
don't see any other reason why OpenJPA decides to retrieve this one column
using separate statements.
Can anyone point out why I might be seeing this behavior and how I can avoid
it?
--
View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-retrieving-one-column-a-seperate-select-tp7586156.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: OpenJPA retrieving one column a seperate select
Posted by mxvs <mx...@gmail.com>.
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.
Re: OpenJPA retrieving one column a seperate select
Posted by Rick Curtis <cu...@gmail.com>.
Sorry but I've been completely underwater. I'll take a look as soon as I
get a few spare cycles.
On Tue, Apr 8, 2014 at 2:59 AM, mxvs <mx...@gmail.com> wrote:
> Hello Rick,
>
> Were you able to download my Unit Test and make sense of what might be
> going
> on?
>
> Thanks
> Max
>
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/OpenJPA-retrieving-one-column-with-a-seperate-select-tp7586156p7586194.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
--
*Rick Curtis*
Re: OpenJPA retrieving one column a seperate select
Posted by mxvs <mx...@gmail.com>.
Hello Rick,
Were you able to download my Unit Test and make sense of what might be going
on?
Thanks
Max
--
View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-retrieving-one-column-with-a-seperate-select-tp7586156p7586194.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: OpenJPA retrieving one column a seperate select
Posted by Rick Curtis <cu...@gmail.com>.
Can you put together a small unit test that recreates the issue? That will
help us understand what is going on.
Thanks,
Rick
On Tue, Mar 25, 2014 at 1:51 PM, mxvs <mx...@gmail.com> wrote:
> Hello, I've been using OpenJPA 2.1.2 (JPA 2.0) to retrieve data from a
> table
> called LOGISCHRAPPORT which has about 28 columns. In some cases OpenJPA
> decides to issue seperate select statements for one of the columns for no
> particular reason, leading to a dramatic decrease in query performance.
>
>
> Initially everything goes fine and all my columns are retrieved in a
> performant, single SELECT statement by JPA.
>
> As soon as I add a relationship to another entity called RAPTAALMETADATA
>
> @OneToMany(fetch=FetchType.EAGER, cascade = CascadeType.ALL)
> @JoinColumns({
> @JoinColumn(name = "RAPPORTNR", referencedColumnName =
> "RAPPORTNR"),
> @JoinColumn(name = "RAPPORTTYPE", referencedColumnName =
> "RAPPORTTYPE") })
> private List<Raptaalmetadata> raptaalmetadata;
> ---
> Queried using Criteria API as follows:
> ---
> Join<LogischRapport, Raptaalmetadata> metadata = reportRoot.join(
> "raptaalmetadata");
>
> JPA no longer includes one of my original columns called REPORT_COMMENTS
> instead it is issuing separate select statements to retrieve the
> REPORT_COMMENTS column for each instance of LOGISCHRAPPORT. All other
> columns (including the ones coming from RAPTAALMETADATA are retrieved
> properly as part of the intial SELECT.
>
> REPORT_COMMENTS is of the HUGEBLOB type in Oracle and I've mapped in in my
> Entity as follows:
>
> @Lob
> @Basic
> @Column(name = "REPORT_COMMENTS")
> private byte[] reportComments;
>
> I now get tons of these:
>
> SELECT t0.REPORT_COMMENTS
> FROM dwhsd001.LogischRapport t0
> WHERE t0.rapportnr = ? AND t0.rapporttype = ?
> [params=(long) 1473, (String) RAP]
>
> Additionally: as soon as I remove the fetch=FetchType.EAGER attribute from
> the @OneToMany annotation described above I start seeing the exact same
> behavior for the relationship as I've been getting for the REPORT_COMMENTS
> column. This means I'm also getting separate SELECT statements for
> retrieving the entity relationship on top of the seperate selects for the
> column thereby further degrading performance.
>
> In other words I'm then also getting tons of these:
>
> SELECT t0.isotaalcode, t0.rapportnr, t0.rapporttype,
> t0.FUNCDESC_MODIFIED_BY, t0.FUNCDESC_MODIFIED_DATE,
> t0.FUNCTIONAL_DESCRIPTION, t0.omschrijving, t0.titel
> FROM dwhsd001.Raptaalmetadata t0
> WHERE t0.rapportnr = ? AND t0.rapporttype = ?
>
> Its not a LAZY loading problem as I've specifically tested that case. I
> don't see any other reason why OpenJPA decides to retrieve this one column
> using separate statements.
>
> Can anyone point out why I might be seeing this behavior and how I can
> avoid
> it?
>
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/OpenJPA-retrieving-one-column-a-seperate-select-tp7586156.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
--
*Rick Curtis*