CLOB loading - query per row

[Simon Droscher <si...@elasticpath.com>]

Hi.

Just wondering if anyone had encountered a similar issue to us with CLOB
loading and/or can explain the behaviour:

When we have entities with CLOB fields and do a EntityManager.get() of one
of those objects, OpenJPA will load the CLOBs with the rest of the data as
part of a single query. But when we load the same object using a named
query, for example "SELECT t FROM Thing t WHERE t.code = ?1", OpenJPA
generates a separate SQL query for each CLOB row after it has loaded the
rest of the data for that object. We see this behaviour even when we are
only querying for a single field of the object (not even the CLOB field).
This is having a big impact on performance for us as it involves a lot more
roundtrips to the DB.

FYI we are running OpenJPA 1.2.1

 

--
View this message in context: http://openjpa.208410.n2.nabble.com/CLOB-loading-query-per-row-tp7260505p7260505.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: CLOB loading - query per row

[Jeremy Bauer <te...@gmail.com>]

Hi Simon,

What database are you using?  Each DB/JDBC driver handles retrieving
lob data differently (although, that is continually getting better and
more standardized with new rev's of JDBC).  Some DBs allow inlining,
sometimes they requires a separate fetch or vendor specific API.
Normally, this via DB/driver specific code is isolated in the DB
dictionaries.  It is odd that OpenJPA is behaving differently with one
path vs. another, though.  Any chance you could post a very simple
testcase?

-Jeremy

On Mon, Feb 6, 2012 at 6:19 PM, Simon Droscher
<si...@elasticpath.com> wrote:
> Hi.
>
> Just wondering if anyone had encountered a similar issue to us with CLOB
> loading and/or can explain the behaviour:
>
> When we have entities with CLOB fields and do a EntityManager.get() of one
> of those objects, OpenJPA will load the CLOBs with the rest of the data as
> part of a single query. But when we load the same object using a named
> query, for example "SELECT t FROM Thing t WHERE t.code = ?1", OpenJPA
> generates a separate SQL query for each CLOB row after it has loaded the
> rest of the data for that object. We see this behaviour even when we are
> only querying for a single field of the object (not even the CLOB field).
> This is having a big impact on performance for us as it involves a lot more
> roundtrips to the DB.
>
> FYI we are running OpenJPA 1.2.1
>
>
>
> --
> View this message in context: http://openjpa.208410.n2.nabble.com/CLOB-loading-query-per-row-tp7260505p7260505.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.