jpql performance

[Kosta]

I have encountered a case where the performance disparity between JPQL
and SQL queries seems unreasonable when fetching a large number of
results (5000+). Have a look at the log below:

JPQL Query:
SELECT led FROM LintEntryDetails led WHERE led.parentJob.id
= :parentJob AND led.entry = :file

13:35:04,250 DEBUG ~ select lintentryd0_.id as id219_,
lintentryd0_.issue_category as issue2_219_, lintentryd0_.comments as
comments219_, lintentryd0_.entry as entry219_, lintentryd0_.issue as
issue219_, lintentryd0_.itemID as itemID219_, lintentryd0_.LINTJOB_ID
as LINTJOB10_219_, lintentryd0_.severity as severity219_,
lintentryd0_."string" as string8_219_, lintentryd0_.substring as
substring219_ from LintEntryDetails lintentryd0_ where
lintentryd0_.LINTJOB_ID=? and lintentryd0_.entry=?
13:35:22,015 INFO ~ jpql query over, fetched 7267 results

Total time ~18 seconds

13:35:22,015 DEBUG ~ SELECT * FROM LINTENTRYDETAILS WHERE LINTJOB_ID=1
AND LINTENTRYDETAILS.ENTRY='myfile.txt'
13:35:22,718 INFO ~ Native query over, fetched 7267 results

Total time ~700ms

This is the underlying code for the JPQL:
List<LintEntryDetails> entryDetails =
LintEntryDetails.find(query.toString())
.bind("parentJob", parentJobId)
.bind("file", filename)
.fetch();

And for the native query:
Query nativeQuery = JPA.em().createNativeQuery("SELECT * FROM
LINTENTRYDETAILS WHERE LINTJOB_ID=1 AND
LINTENTRYDETAILS.ENTRY='myfile.txt'");
List results = nativeQuery.getResultList();

The actual LintEntryDetails model contains only simple fields and one
@ManyToOne field that I explicitly set the FetchType to LAZY, and you
can see from the resulting hibernate query that nothing is being
joined, so that can't be the source of abysmal performance.

My question is, then, is this normal, and is the overhead introduced
by mapping the retrieved fields onto the domain models? If so, can one
justify using JPA/Hibernate queries for anything exceeding a handful
of rows? Thanks in advance for any insights you might have!

[Kosta]

Sorry, forgot to include the actual JPQL query construct, it's very
simple and equivalent to the native query:

JpqlSelect query = new JpqlSelect();
query.select("led");
query.from("LintEntryDetails led");
query.where("led.parentJob.id = :parentJob");
query.andWhere("led.entry = :file");

[Guillaume Bort]

Well the JPQL query also construct the 7000 java objects. So I think
it will always be slower. But yes in your case I don't understand why
it is so long... Can you build a very simple app that reproduce this
problem?

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>
>

--
Guillaume Bort, http://guillaume.bort.fr

For anything work-related, use g...@zenexity.fr; for everything else,
write guillau...@gmail.com

[Kosta]

Ok, I've done some further testing. With the test case above I was
querying a remote server on the network that's running Oracle 10g. In
the meanwhile I've tried running the same queries on the same dataset
using both in-memory hsqldb and a locally installed instance of MySQL.
In both cases the JPA query performed effectively as fast as the
native one.

That means the problem is either down to the fact that the database is
located on a remote server, or there is some Oracle specific
inefficiency that I've encountered. I will do some further tests by
querying a remote MySQL database, and if all else fails I'll install
Oracle locally to see if I can reproduce the same behaviour.

> Guillaume Bort,http://guillaume.bort.fr

>
> For anything work-related, use g...@zenexity.fr; for everything else,

> write guillaume.b...@gmail.com

[Kosta]

OK, so basically it has something to do with how JPA executes .fetch()
The query itself executes in a fraction of a second, but retrieval of
rows takes forever. If I limit the amount of results retrieved by
running .fetch(100), it runs under a second. This didn't seem to be an
issue with a remote MySQL database.

The confusing thing is that, looking at fetch() in JPASupport class,
it actually does run getResultList() internally so performance should
be the same in theory. Guillame, since you are familiar with the
internals of the play dba classes, would you have a hunch as to what
could be causing such a long retrieval of results? If not, I can try
and come up with a small self-contained test case.

Thanks!

[Kosta]

Ok, I've found the source of the problem.

When a String is annotated with @Lob, Oracle creates the column as a
CLOB, which translates to Hibernate's SerializableClob. I assume that
means for each row Hibernate needs to serialize that CLOB into a
string, so for ~7000 rows it will make ~7000 requests back to the DB
to serialize that field.

The reason why the native query appeared to perform so much faster was
due to the fact that I did not perform the required serialization of
CLOBs. Once I added that routine to the native query, the performance
was on par.

This is clearly a Hibernate issue then, not a Play issue, so I will
try and see if there are any helpful pointers there on how to improve
the performance of CLOB serialization.

If someone else encounters this issue hopefully this will save them a
couple of hours :)