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 > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> > > > > > > > > >