Hi David -
No I did not say that at all. Just that from the information presented
there is no reason "necessarily" to expect different performance from
what you reported.
The SQL could be returning large numbers of rows (we don't know). Say
you have 100 "mesh2_term" objects in the findall collection argument and
each of these is linked to 100 rows in the "variants" table. Then you
are loading 10,100 objects using 100 queries. [this is just "for
example"] - Performance would depend on query optimization and the
indices involved. Batching is an optimization but probably not the
major contributor either way to the performance outcome.
Try executing 10 of these queries in a psql script - Does it take about
3 seconds to complete? Then 100 would take 30 seconds and JPA doesn't
have much to do with the performance either way.
- Paul
On 4/26/2009 9:22 PM, David Leangen wrote:
Right.
So, you're saying that it's not possible to do one batch transaction
with the DB when there are joins involved?
Thanks,
=David
On Thu, 2009-04-23 at 21:06 -0700, Paul Copeland wrote:
It makes sense that it would do a query per primary key (all in a single
transaction). There is a join of "variants" with the main table for
each such query indicating I guess eager fetching in the join.
Depending on the number of "variants" this could be a large number of
rows. That as much as can be guessed from the information provided here.
On 4/21/2009 1:21 AM, David Leangen wrote:
Tried setting this:
jdbcFetchPlan.setFetchBatchSize( -1 )
.setMaxFetchDepth( -1 )
.setEagerFetchMode( FetchMode.PARALLEL );
and this:
<property name="openjpa.jdbc.DBDictionary" value="batchLimit=-1"/>
but no difference...
My data store is PostgreSQL. Driver is org.postgresql.jdbc3 v 8.3.603.
Cheers,
=David
On Mon, 2009-04-20 at 18:43 +0900, David Leangen wrote:
Ok, now I see why!
findAll( Class, Collection )
actually executes one transaction for each PK in the collection!!
Here is an excerpt of the log.
8428 openjpa TRACE [$Proxy26:1240220275057:task1] openjpa.jdbc.SQL -
<t 27011334, conn 15221176> executing prepstmnt 30712384 SELECT
t0.value, t1.TERMVO_ID, t1.element FROM mesh2_terms t0 LEFT OUTER JOIN
mesh2_terms_variants t1 ON t0.id = t1.TERMVO_ID WHERE t0.id = ?
[params=(String) T360914]
138430 openjpa TRACE [$Proxy26:1240220275057:task1] openjpa.jdbc.SQL
- <t 27011334, conn 15221176> [2 ms] spent
Is there a way I can get everything done in one batch transaction rather
than hundreds or thousands of transactions?
Thank you!
=David
On Mon, 2009-04-20 at 13:49 +0900, David Leangen wrote:
Thank you, I'll give that a try.
On Fri, 2009-04-17 at 07:55 -0700, Paul Copeland wrote:
That sounds interesting. You might turn on verbose logging for SQL
operations (openjpa.jdbc.SQL) and see what queries are actually being
executed. The logging section of the OpenJPA manual explains this.
On 4/17/2009 3:41 AM, David Leangen wrote:
Hello,
I'm not at all an expert in databases, so I don't know if this is a JPA
thing or a DB thing.
In any case, I'm using Postgresql. Using the findAll( Class,
Collection ) method, I am doing a lookup by PK. Since there is an index
like so on the table:
"table_pkey" PRIMARY KEY, btree (id)
I would have expected the lookup to be very fast.
With a collection of about 100 ids, the lookup takes almost 30 seconds,
which does not seem right to me.
Any ideas where I should begin to look?
Thanks!
=David
