Hi, Paul, Thank you for sharing your experience. You've given me some very good hints. I'll continue to play around with this and maybe post more questions later.
Cheers, =David On Sun, 2009-04-26 at 22:39 -0700, Paul Copeland wrote: > 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 > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > >>> > >>> > >>> > > > > > > > > >
