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

[David Bernstein <dbb.posting@xxxxxxxxx>]

Thanks for the explanation!


tware wrote:
> 
> Hi David,
> 
>    ReadAllQuery is the class that implements reads of multiple objects.
> (as 
> opposed to ReadObjectQuery which reads a single object)
> 
>    The SQL in the exception you see is the SQL that your "findSnapshot"
> query 
> ran.  It will use the data that comes back from that SQL to retrieve data. 
> By 
> default, it will look at the @Id fields in each row and attempt a cache
> lookup 
> based on those Ids and if there is no data in the cache, it will build a
> new 
> object, put it in the cache and return it.
> 
>    The size of the data returned to the query will depend on the
> parameters 
> passed to the query. If you are having issues with GC or memory usage
> based on 
> that query, it might be a good idea to see how much data that SQL will
> return 
> for you.
> 
> -Toom
> 
> David Bernstein wrote:
>> 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
>>>
>>>
>> 
> _______________________________________________
> 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-tp29939281p29945527.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.