Re: [eclipselink-users] query optimization for secondary keys

[David Bernstein <dbb.posting@xxxxxxxxx>]

Hi Tom.

Thanks for your quick response.

It's certainly possible that I've fallen victim to incorrect theorizing
about EclipseLink interacts with its cache and database. Here's my
situation:

I'm running EclipseLink pretty much out of the box from Glassfish 3.0.1 with
PostgreSQL 9.0.0 on Linux (production) and Windows 7 (development).  I'm
troubleshooting a (memory-based, it seems) performance issue, which I now
see might be largely over-eager entity fetching.  In the server log, I saw:

Internal Exception: java.lang.reflect.InvocationTargetException
Target Invocation Exception: java.lang.OutOfMemoryError: GC overhead limit
exceeded
Query: ReadAllQuery(name="findSnapshot" referenceClass=Snapshot sql="SELECT
snapshot_id, ref_value, ref_id, ref_sync_pt, ref_type FROM om_snapshot WHERE
(((ref_id = ?) AND (ref_type = ?)) AND (ref_sync_pt = ?))")
	at
org.eclipse.persistence.exceptions.QueryException.methodInvocationFailed(QueryException.java:786)

where the ref_id, ref_type, and ref_sync_pt columns of the om_snapshot table
are collectively defined to be unique.  Having seen "ReadAllQuery", I jumped
to the conclusion that the entire table was being sucked in for a linear
search of the objects to find one that matched the query criteria (sorry, it
was getting late). It now in the light of day seems also quite (more?)
plausible that it's merely reading in the entire row and/or all rows
returned from the SQL query.  If in fact EclipseLink is always going to the
database to implement a JPA named query and thus benefitting from the
database's indices, then what I posted about is not a problem.  Is that the
case?

-David


tware wrote:
> 
> Hi David,
> 
>    Perhaps you can give an example of the kind of query that might give
> you an 
> issue.
> 
>    The way EclipseLink is going to handle queries for multiple entities is
> that 
> unless specifically instructed not to go to the database, it will query
> the 
> database.  The query from the database will bring bring back information
> about 
> all the appropriate entities including the fields that have been
> identified as 
> identity fields in the entity. Those identity fields will be used for the
> cache 
> lookup.
> 
> -Tom
> 
> David Bernstein wrote:
>> Is there a generic way to have an EJB3 query take advantage of database
>> secondary composite keys?  If there's no generic way, then I'd accept an
>> EclipseLink-specific method, but it seems there ought to be a generic JPA
>> way to do this.
>>  
>> For example, suppose there were an SQL table
>>  
>> create table su (
>>   su_id int primary key,
>>   ri    int not null,
>>   rt    int not null,
>>   unique ( ri, rt )
>>   );
>>  
>> where I want su_id to be the primary key, but I also want efficient
>> access
>> via the composite key (ri,rt) using EJB3/EclipseLink.
>>  
>> I think I may have a performance problem because the database is
>> configured
>> for fast access via the unique composite secondary key, but the cache
>> would
>> be searched linearly (and the entire table may be sucked in?) when
>> querying
>> via the secondary key.
>>  
>> The @Table annotation has a uniqueConstraints element, but it appears to
>> me
>> that it’s only used for schema generation, not query optimization.
>>  
>> It seems really basic to me to be able to do this so I think I must be
>> missing something really obvious. My googling and manual reading haven't
>> helped much.  At the moment the only thing I can think of is to do a
>> native
>> named query, but that would seem to bypass the persistence provider's
>> cache. 
>> 
>> *Apologies in advance if anybody receives this message twice.*
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 

-- 
View this message in context: http://old.nabble.com/query-optimization-for-secondary-keys-tp29939281p29945357.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.