openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul Copeland <t...@jotobjects.com>
Subject Re: Performance issues
Date Mon, 27 Apr 2009 05:39:39 GMT
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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>   
>>>>>>>           
>>>>>>>               
>>>
>>>   
>>>       
>
>
>
>   


Mime
View raw message