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

[Tom Ware <tom.ware@xxxxxxxxxx>]

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