persistence@glassfish.java.net

Re: JPQL: create ReadAllQuery for simple SELECT queries

From: Tom Ware <tom.ware_at_oracle.com>
Date: Thu, 30 Nov 2006 12:58:37 -0500

Hi Wonseok,

  First, a comment about ConformResultsInUnitOfWork:
ConformResultsInUnitOfWork is a setting that allows a query to take the
results that come from the database and compare them to what is
available in the UnitOfWorkCache and build a result list that conforms
with what is in the UnitOfWork cache, but removing items that no longer
fit the selection criteria and adding items that fit the selection
criteria in the cache, but not in the database. This setting is ignored
by ReportQuery.

  Currently TopLink's in-memory query framework (the framework used for
conforming) is capable of supporting most types of selection criteria,
but not all of them. As a result of this, we have chosen to use a
strategy of flushing to the database at certain times to ensure we get
the correct objects back in JPA. Because of this strategy of flushing,
is ok that ConformResultsInUnitOfWork is not enabled.

  Changing from ReportQuery to ReadAllQuery will likely have some small
performance improvement. The big benefit of this change is that, as you
have noticed, it will allow us to enable some hints that could possibly
give us a big performance boost in some cases. There are a number of
cache-related settings that can be used with ReadAllQuery that cannot be
used with ReportQuery. (CheckCacheOnly is a great example, but there
are also a number of other caching settings that can be found in
ObjectLevelReadQuery that we could enable.) Also, with a little work,
we could add a "conform" hint that would allow us to do more of the work
for querying in memory rather than interacting with the database.

  So, in answer to your question: This change is important because is
enables us to build some query hints that could provide some significant
performance benefits.

-Tom

Wonseok Kim wrote:

> Hi Michael, Tom
>
> I think this is a rather big change, so I'm basically curious that
> ReadAllQuery has what benefits over ReportQuery for simple query.
> Performance? I can guess, but how much difference will be there? Could
> you guys teach me in more detail?
>
> Also when I see the following diffs, the cache-usage value of
> ReadAllQuery/ReportQuery is not "ConformResultsInUnitOfWork" anymore,
> is it intended?
> http://fisheye5.cenqua.com/browse/glassfish/entity-persistence/src/java/oracle/toplink/essentials/internal/ejb/cmp3/base/EJBQueryImpl.java?r1=1.29&r2=1.30
> <http://fisheye5.cenqua.com/browse/glassfish/entity-persistence/src/java/oracle/toplink/essentials/internal/ejb/cmp3/base/EJBQueryImpl.java?r1=1.29&r2=1.30>
> http://fisheye5.cenqua.com/browse/glassfish/entity-persistence/src/java/oracle/toplink/essentials/internal/parsing/SelectNode.java?r1=1.20&r2=1.21
> <http://fisheye5.cenqua.com/browse/glassfish/entity-persistence/src/java/oracle/toplink/essentials/internal/parsing/SelectNode.java?r1=1.20&r2=1.21>
>
> With this change, I think, the cache-usage query hint which was
> removed from documents (due to issue 999) seems to be valid again for
> ReadAllQuery cases.
> I tested by setting "toplink.cache-usage" hint to "CheckCacheOnly" and
> it does not throw an exception anymore(ReportQuery did) and it doesn't
> send SQL to database, either. Wow! Should we revive the hint?
>
> Regards,
> -Wonseok
>
> On 11/29/06, *Michael Bouschen* <Michael.Bouschen_at_sun.com
> <mailto:Michael.Bouschen_at_sun.com>> wrote:
>
> Hi Tom,
>
> thanks for figuring out! So I will keep the code that checks for a
> fetch
> join and then generates a ReportQuery instance.
>
> Attached you find an updated version of my changes to fix 1519.
> The jar
> includes all the changes, but only one file has been changed
> compared to
> the previous version I sent around. I modified class VariableNode
> to use
> the API addJoinedAttribute(Expression) if the query instance is not a
> ReportQuery. Today this code will not be called, but it is ready to go
> as soon as we generate ReadAllQuery even in the fetch join case.
>
> Regards Michael
> > Hi Michael,
> >
> > I found the reason for the fetch join issue. The way we are
> > returning items for the fetch join is actually dictated by the
> > specification. (see section 4.4.5.3 <http://4.4.5.3>)
> >
> > What that means is that for the time being, we will likely have to
> > generate a ReportQuery on fetch join. I plan on entering an
> > Enhancement request to add a ReadAllQuery option that allows
> results
> > to be returned in the same way. At that time, we can update the
> JPQL
> > parser.
> >
> > I appologize for causing this extra work before it can actually
> be used.
> >
> > -Tom
> >
> >
> >
> > Michael Bouschen wrote:
> >
> >> Hi Tom,
> >>
> >> good point! I was not aware of the other API
> >> addJoinedAttribute(Expression) that allows fetch join support for
> >> ReadAllQuerys.
> >>
> >> I changed the query compiler accordingly, such that it supports
> >> generating a ReadAllQuery with fetch joins. But now I run into a
> >> problem with one of the tests in entity-persistence-tests:
> >> JUnitEJBQLSimpleTestSuite.simpleJoinFetchTest. The test runs a JPQL
> >> fetch join query and compares the result against a TopLink
> >> ReportQuery. The ReportQuery result includes duplicated entities
> >> where the corresponding ReadAllQuery does not include duplicates.
> >>
> >> Attached you find a small test application showing the
> difference. It
> >> has two entity classes: Customer and Order. The test creates one
> >> Customer with two Order instances and persist them. It then runs a
> >> ReportQuery and a ReadAllQuery both representing the following JPQL
> >> query "SELECT c FROM Customer c JOIN FETCH c.orders ". The
> generated
> >> SQL is equivalent (the identification variable names are swapped).
> >> But the ReportQuery returns the Customer twice, where the
> >> ReadAllQuery returns it only once. Here are the results:
> >> ReportQuery:
> >> - SQL
> >> SELECT DISTINCT t0.ID <http://t0.ID>, t0.NAME
> <http://t0.NAME>, t1.ID <http://t1.ID>, t1.TOTAL_PRICE,
> >> t1.CREATION_DATE , t1.CUST_ID
> >> FROM CUSTOMER_TABLE t0, ORDER_TABLE t1 WHERE (t1.CUST_ID =
> t0.ID <http://t0.ID>)
> >> - Result [Customer(id=1, name=Michael), Customer(id=1,
> name=Michael)]
> >>
> >> ReadAllQuery:
> >> - SQL
> >> SELECT DISTINCT t1.ID <http://t1.ID>, t1.NAME
> <http://t1.NAME>, t0.ID <http://t0.ID>, t0.TOTAL_PRICE,
> >> t0.CREATION_DATE, t0.CUST_ID
> >> FROM ORDER_TABLE t0, CUSTOMER_TABLE t1 WHERE ( t0.CUST_ID =
> t1.ID <http://t1.ID>)
> >> - Result: [Customer(id=1, name=Michael)]
> >>
> >> The test uses ddl-generation. Please adapt the persistence.xml
> under
> >> src/META-INF to your environment, if you want to run the test.
> >>
> >> Regards Michael
> >>
> >>
> >>> Hi Michael,
> >>>
> >>> 'Just one comment.
> >>>
> >>> Fetch joins could be an issue since they are supported in
> >>> ReadAllQuery, but with different API. See
> >>> ObjectLevelReadQuery.addJoinedAttribute(Expression). How
> difficult
> >>> would it be to allow fetch joins to work on ReadAllQueries?
> >>>
> >>> -Tom
> >>>
> >>> Michael Bouschen wrote:
> >>>
> >>>
> >>>> Hi Tom, hi Jielin,
> >>>>
> >>>> attached you find my changes to fix issue 1519 "JPQL: create a
> >>>> ReadAllQuery for simple queries":
> >>>> https://glassfish.dev.java.net/issues/show_bug.cgi?id=1519
> >>>>
> >>>> This change allows to map a simple SELECT query to a ReadAllQuery
> >>>> instance, where other SELECT queries are mapped to a
> ReportQuery. A
> >>>> query falls into the ReadAllQuery category, if
> >>>> - the SELECT clause consists of a single expression
> >>>> - the SELECT clause expression is an identification variable w/o
> >>>> projection (which means the query returns entities)
> >>>> - the identification variable is defined as range variable w/o
> >>>> FETCH JOINs and is the base of the generated TopLink query
> instance.
> >>>>
> >>>> Thanks!
> >>>>
> >>>> Regards Michael
> >>>>
> >>>>
> >>>>
> >>>>
> >>
> >>
> >>
> >
>
>
>
>
>