Uregntly need help for thesis: Batch reading of collection with lazy OneToOne relation

[Mario Sandro Schwarz]

Hi everybody.

I have to do a run time comparison on Eclipselink and Hibernate for my Bachelor Thesis. I really have an urgent problem, I don't get BatchReading to work correctly.

I implemented use-case based test cases that call specific methods of a  SessionBean called "PartnerService". PartnerService acts as a Facade and manages the transactions (@TransactionAttributeType.REQUIRED). It calls methods on a DAO called "PartnerDAO". PartnerDAO requires an open transaction (@TransactionAttributeType.MANDATORY)

I have the following NamedQuery which retrieves me all partners with their addresses and their roles:

@NamedQuery(name = "getAllPartnersWithAdressesAndRoles", query = "SELECT p FROM Partner as p ", hints = {
@QueryHint(name = QueryHints.LEFT_FETCH, value = "p.roles"),
@QueryHint(name = QueryHints.LEFT_FETCH, value = "p.addresses"),
@QueryHint(name = QueryHints.JDBC_FETCH_SIZE, value = "1000") })

Now I also want to retrieve the bank account of the specific role "employee"  which is a unidirectional onetoone-relation. In the PartnerDAO-method I do the following:

public List<Partner> findAllPartners() {
  Query q = em.createNamedQuery("getAllPartnersWithAdressesAndRoles");
  // results in just 1 SELECTs
  List<Partner> l = q.getResultList();
  for (Partner partner : l) {
    for (Role role : partner.getRoles()) {
      // results in l.size() SELECTs
      BankAccount ba = ((Employee) role).getBankAccount();
    }
  }
}

My goal is to iterate over all roles and retrieve all BankAccounts in a single SELECT. I guess the problem is, that BankAccount has no ForeignKey of its Employee. With Hibernate it works though. Oh, by the way, I am not able to change the mapping to bidirectional anymore!

Any help is more than appeciated!
Thanks
Mario  


--
GMX FreeDSL Komplettanschluss mit DSL 6.000 Flatrate und Telefonanschluss
für nur 17,95 Euro/mtl.!* http://portal.gmx.net/de/go/dsl02
_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[tware]

EclipseLink allows multi-level fetch joins and batch reading.  Have you tried
any of these?

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Join_Fetch

(something like @QueryHint(name = QueryHints.LEFT_FETCH, value =
"p.roles.bankAccount")

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Batch
  (something like @QueryHint(name=QueryHints.BATCH, value="p.roles.bankAccount");

Mario Schwarz wrote:

> Hi everybody.
>
> I have to do a run time comparison on Eclipselink and Hibernate for my Bachelor Thesis. I really have an urgent problem, I don't get BatchReading to work correctly.
>
> I implemented use-case based test cases that call specific methods of a  SessionBean called "PartnerService". PartnerService acts as a Facade and manages the transactions (@TransactionAttributeType.REQUIRED). It calls methods on a DAO called "PartnerDAO". PartnerDAO requires an open transaction (@TransactionAttributeType.MANDATORY)
>
> I have the following NamedQuery which retrieves me all partners with their addresses and their roles:
>
> @NamedQuery(name = "getAllPartnersWithAdressesAndRoles", query = "SELECT p FROM Partner as p ", hints = {
> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.roles"),
> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.addresses"),
> @QueryHint(name = QueryHints.JDBC_FETCH_SIZE, value = "1000") })
>
> Now I also want to retrieve the bank account of the specific role "employee"  which is a unidirectional onetoone-relation. In the PartnerDAO-method I do the following:
>
> public List<Partner> findAllPartners() {
>   Query q = em.createNamedQuery("getAllPartnersWithAdressesAndRoles");
>   // results in just 1 SELECTs
>   List<Partner> l = q.getResultList();
>   for (Partner partner : l) {
>     for (Role role : partner.getRoles()) {
>       // results in l.size() SELECTs
>       BankAccount ba = ((Employee) role).getBankAccount();
>     }
>   }
> }
>
> My goal is to iterate over all roles and retrieve all BankAccounts in a single SELECT. I guess the problem is, that BankAccount has no ForeignKey of its Employee. With Hibernate it works though. Oh, by the way, I am not able to change the mapping to bidirectional anymore!
>
> Any help is more than appeciated!
> Thanks
> Mario  
>
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[Mario Sandro Schwarz]

Hello Tom,

unfortunately the problem is that not the role has the "bankaccount".
Just the specific role "employee" does.
I remember you replying to my last post telling me that there is a an
open enhancement request for this kind of thing (Downcasting in
queries). Thanks a lot for the try to help though.

Anyone got any other suggestions that might help?

Mario


Tom Ware schrieb:

> EclipseLink allows multi-level fetch joins and batch reading.  Have
> you tried any of these?
>
> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Join_Fetch 
>
>
> (something like @QueryHint(name = QueryHints.LEFT_FETCH, value =
> "p.roles.bankAccount")
>
> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Batch
>  (something like @QueryHint(name=QueryHints.BATCH,
> value="p.roles.bankAccount");
>
> Mario Schwarz wrote:
>> Hi everybody.
>>
>> I have to do a run time comparison on Eclipselink and Hibernate for
>> my Bachelor Thesis. I really have an urgent problem, I don't get
>> BatchReading to work correctly.
>>
>> I implemented use-case based test cases that call specific methods of
>> a  SessionBean called "PartnerService". PartnerService acts as a
>> Facade and manages the transactions
>> (@TransactionAttributeType.REQUIRED). It calls methods on a DAO
>> called "PartnerDAO". PartnerDAO requires an open transaction
>> (@TransactionAttributeType.MANDATORY)
>>
>> I have the following NamedQuery which retrieves me all partners with
>> their addresses and their roles:
>>
>> @NamedQuery(name = "getAllPartnersWithAdressesAndRoles", query =
>> "SELECT p FROM Partner as p ", hints = {
>> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.roles"),
>> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.addresses"),
>> @QueryHint(name = QueryHints.JDBC_FETCH_SIZE, value = "1000") })
>>
>> Now I also want to retrieve the bank account of the specific role
>> "employee"  which is a unidirectional onetoone-relation. In the
>> PartnerDAO-method I do the following:
>>
>> public List<Partner> findAllPartners() {
>>   Query q = em.createNamedQuery("getAllPartnersWithAdressesAndRoles");
>>   // results in just 1 SELECTs
>>   List<Partner> l = q.getResultList();
>>   for (Partner partner : l) {
>>     for (Role role : partner.getRoles()) {
>>       // results in l.size() SELECTs
>>       BankAccount ba = ((Employee) role).getBankAccount();
>>     }
>>   }
>> }
>>
>> My goal is to iterate over all roles and retrieve all BankAccounts in
>> a single SELECT. I guess the problem is, that BankAccount has no
>> ForeignKey of its Employee. With Hibernate it works though. Oh, by
>> the way, I am not able to change the mapping to bidirectional anymore!
>>
>> Any help is more than appeciated!
>> Thanks
>> Mario
>>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[tware]

First of all, I'd like to reiterate that this is a fairly uncommon thing to do
and that I am surprised that your performance-measuring app is doing something
that uncommon.

I am not sure how the code below works in the case where "role" is not and
Employee.  You'll get a ClassCastException from java regardless of the
persistence provider.  Are all your roles Employees?

 >>>   for (Partner partner : l) {
 >>>     for (Role role : partner.getRoles()) {
 >>>       // results in l.size() SELECTs
 >>>       BankAccount ba = ((Employee) role).getBankAccount();
 >>>     }
 >>>   }

In EclipseLink, there is the option of setting the mapping itself to batch read,
so one way you could make it work for this scenario is by using a
DescriptorCustomizer to make the Employee.roles mapping always use batch
reading.  It would be a matter of getting the query for that mapping and
enabling batch reading on it.

-Tom

Mario Sandro Schwarz wrote:

> Hello Tom,
>
> unfortunately the problem is that not the role has the "bankaccount".
> Just the specific role "employee" does.
> I remember you replying to my last post telling me that there is a an
> open enhancement request for this kind of thing (Downcasting in
> queries). Thanks a lot for the try to help though.
>
> Anyone got any other suggestions that might help?
>
> Mario
>
>
> Tom Ware schrieb:
>> EclipseLink allows multi-level fetch joins and batch reading.  Have
>> you tried any of these?
>>
>> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Join_Fetch 
>>
>>
>> (something like @QueryHint(name = QueryHints.LEFT_FETCH, value =
>> "p.roles.bankAccount")
>>
>> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Batch
>>  (something like @QueryHint(name=QueryHints.BATCH,
>> value="p.roles.bankAccount");
>>
>> Mario Schwarz wrote:
>>> Hi everybody.
>>>
>>> I have to do a run time comparison on Eclipselink and Hibernate for
>>> my Bachelor Thesis. I really have an urgent problem, I don't get
>>> BatchReading to work correctly.
>>>
>>> I implemented use-case based test cases that call specific methods of
>>> a  SessionBean called "PartnerService". PartnerService acts as a
>>> Facade and manages the transactions
>>> (@TransactionAttributeType.REQUIRED). It calls methods on a DAO
>>> called "PartnerDAO". PartnerDAO requires an open transaction
>>> (@TransactionAttributeType.MANDATORY)
>>>
>>> I have the following NamedQuery which retrieves me all partners with
>>> their addresses and their roles:
>>>
>>> @NamedQuery(name = "getAllPartnersWithAdressesAndRoles", query =
>>> "SELECT p FROM Partner as p ", hints = {
>>> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.roles"),
>>> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.addresses"),
>>> @QueryHint(name = QueryHints.JDBC_FETCH_SIZE, value = "1000") })
>>>
>>> Now I also want to retrieve the bank account of the specific role
>>> "employee"  which is a unidirectional onetoone-relation. In the
>>> PartnerDAO-method I do the following:
>>>
>>> public List<Partner> findAllPartners() {
>>>   Query q = em.createNamedQuery("getAllPartnersWithAdressesAndRoles");
>>>   // results in just 1 SELECTs
>>>   List<Partner> l = q.getResultList();
>>>   for (Partner partner : l) {
>>>     for (Role role : partner.getRoles()) {
>>>       // results in l.size() SELECTs
>>>       BankAccount ba = ((Employee) role).getBankAccount();
>>>     }
>>>   }
>>> }
>>>
>>> My goal is to iterate over all roles and retrieve all BankAccounts in
>>> a single SELECT. I guess the problem is, that BankAccount has no
>>> ForeignKey of its Employee. With Hibernate it works though. Oh, by
>>> the way, I am not able to change the mapping to bidirectional anymore!
>>>
>>> Any help is more than appeciated!
>>> Thanks
>>> Mario
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@...
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[Mario Sandro Schwarz]

Hi Tom,

I think I explained it the wrong way last time. The
performance-measuring is just a small part in which
provider-specifically performance-optimized use-cases (goal: at least
SQLs as possible) are measured and compared. The main focus of the
thesis lies on evaluating the persistence providers performance
optimization capabilities by means of typical use-cases. My bad!

You´re right. So far, a partner just might have the role "employee".
Sorry I missed that fact out.

I did already try it with a customizer based on the workaround from Doug
Clarke "Accessing un-mapped attributes using QueryKey "
(http://wiki.eclipse.org/EclipseLink/Development/JPA/QueryDownCast). The
role entity class was annotated with this Customizer:

package customizer;

import org.eclipse.persistence.config.DescriptorCustomizer;
import org.eclipse.persistence.descriptors.ClassDescriptor;
// The customizer adds the direct query keys
public class RoleCustomizer implements DescriptorCustomizer {
    public void customize(ClassDescriptor descriptor) throws Exception {
        descriptor.addDirectQueryKey("bankaccount", "BANKACCOUNT");
    }
}

But how am I supposed to use my NamedQuery with the ExpressionBuilder
like in the example then?
Like the following code snippet or would I have to modify the NamedQuery
and do something like .addBatchReadAttribute(batchReadRoles) and also
.addBatchReadAttribute(batchReadBankAccount)?

Query q = em.createNamedQuery("getAllPartnersWithAdressesAndRoles");
ExpressionBuilder eb = q.getExpressionBuilder();
Expression batchReadBankAccount = eb.get("bankaccount");
readAllQuery.addBatchReadAttribute(batchReadBankAccount);
q.setSelectionCriteria(eb.get("bankaccount").like("bankaccount"));
// Wrap in JPA Query
Query query = JpaHelper.createQuery(q, em);
// Execute Query
List<A> results = query.getResultList();

Thanks for your help!!!
Mario



Tom Ware schrieb:

> First of all, I'd like to reiterate that this is a fairly uncommon
> thing to do and that I am surprised that your performance-measuring
> app is doing something that uncommon.
>
> I am not sure how the code below works in the case where "role" is not
> and Employee.  You'll get a ClassCastException from java regardless of
> the persistence provider.  Are all your roles Employees?
>
> >>>   for (Partner partner : l) {
> >>>     for (Role role : partner.getRoles()) {
> >>>       // results in l.size() SELECTs
> >>>       BankAccount ba = ((Employee) role).getBankAccount();
> >>>     }
> >>>   }
>
> In EclipseLink, there is the option of setting the mapping itself to
> batch read, so one way you could make it work for this scenario is by
> using a DescriptorCustomizer to make the Employee.roles mapping always
> use batch reading.  It would be a matter of getting the query for that
> mapping and enabling batch reading on it.
>
> -Tom
>
> Mario Sandro Schwarz wrote:
>> Hello Tom,
>>
>> unfortunately the problem is that not the role has the "bankaccount".
>> Just the specific role "employee" does.
>> I remember you replying to my last post telling me that there is a an
>> open enhancement request for this kind of thing (Downcasting in
>> queries). Thanks a lot for the try to help though.
>>
>> Anyone got any other suggestions that might help?
>>
>> Mario
>>
>>
>> Tom Ware schrieb:
>>> EclipseLink allows multi-level fetch joins and batch reading.  Have
>>> you tried any of these?
>>>
>>> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Join_Fetch 
>>>
>>>
>>> (something like @QueryHint(name = QueryHints.LEFT_FETCH, value =
>>> "p.roles.bankAccount")
>>>
>>> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Batch 
>>>
>>>  (something like @QueryHint(name=QueryHints.BATCH,
>>> value="p.roles.bankAccount");
>>>
>>> Mario Schwarz wrote:
>>>> Hi everybody.
>>>>
>>>> I have to do a run time comparison on Eclipselink and Hibernate for
>>>> my Bachelor Thesis. I really have an urgent problem, I don't get
>>>> BatchReading to work correctly.
>>>>
>>>> I implemented use-case based test cases that call specific methods
>>>> of a  SessionBean called "PartnerService". PartnerService acts as a
>>>> Facade and manages the transactions
>>>> (@TransactionAttributeType.REQUIRED). It calls methods on a DAO
>>>> called "PartnerDAO". PartnerDAO requires an open transaction
>>>> (@TransactionAttributeType.MANDATORY)
>>>>
>>>> I have the following NamedQuery which retrieves me all partners
>>>> with their addresses and their roles:
>>>>
>>>> @NamedQuery(name = "getAllPartnersWithAdressesAndRoles", query =
>>>> "SELECT p FROM Partner as p ", hints = {
>>>> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.roles"),
>>>> @QueryHint(name = QueryHints.LEFT_FETCH, value = "p.addresses"),
>>>> @QueryHint(name = QueryHints.JDBC_FETCH_SIZE, value = "1000") })
>>>>
>>>> Now I also want to retrieve the bank account of the specific role
>>>> "employee"  which is a unidirectional onetoone-relation. In the
>>>> PartnerDAO-method I do the following:
>>>>
>>>> public List<Partner> findAllPartners() {
>>>>   Query q = em.createNamedQuery("getAllPartnersWithAdressesAndRoles");
>>>>   // results in just 1 SELECTs
>>>>   List<Partner> l = q.getResultList();
>>>>   for (Partner partner : l) {
>>>>     for (Role role : partner.getRoles()) {
>>>>       // results in l.size() SELECTs
>>>>       BankAccount ba = ((Employee) role).getBankAccount();
>>>>     }
>>>>   }
>>>> }
>>>>
>>>> My goal is to iterate over all roles and retrieve all BankAccounts
>>>> in a single SELECT. I guess the problem is, that BankAccount has no
>>>> ForeignKey of its Employee. With Hibernate it works though. Oh, by
>>>> the way, I am not able to change the mapping to bidirectional anymore!
>>>>
>>>> Any help is more than appeciated!
>>>> Thanks
>>>> Mario
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@...
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@...
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[Frank Schwarz]

Hi Mario,

the query key has to be defined on Partner pointing to Employee instead of Role. Please have a look at http://code.google.com/p/oopex/source/browse/trunk/modules/eclipselink1-jpa/src/queries-downcasts/java/oopex/eclipselink1/jpa/queries/DowncastsMain.java#139. I never tried this for defining Join-Reads/Batch-Reads, though.

@Tom: Using downcasts in queries is, from my point of view, something quite natural if you design your model in a truly object oriented way. Reiterating that this feature nobody is needing, does not make disappear. The other feature, Mario is looking for, might be called "fetch-plans/plans" - as in the old JDO days ;-). See http://wiki.eclipse.org/EclipseLink/Development/JPA/NestedFetchGroups, last item in the open issues list. Also this feature(-request) I see frequently in projects when there is a CRUD-style rich client.

-- Frank

Meet me at JFS2009 http://www.java-forum-stuttgart.de/

Mario Sandro Schwarz wrote:

I did already try it with a customizer based on the workaround from Doug
Clarke "Accessing un-mapped attributes using QueryKey "
(http://wiki.eclipse.org/EclipseLink/Development/JPA/QueryDownCast). The
role entity class was annotated with this Customizer:

[tware]

Hi Frank,

   I am not arguing that downcasting is not a useful feature.  In fact, when our
JPA 2.0 release is done, I suspect it will be fairly high on the list of the
features we consider implementing.

   What I am suggesting is that for a performance benchmark to merit including a
feature like this the benchmark would have to be a fairly far reaching benchmark
- likely a bigger benchmark than one would write as part of a bachelor's thesis.
  Mario has addressed this question in his previous email.

-Tom

Frank Schwarz wrote:

> Hi Mario,
>
> the query key has to be defined on Partner pointing to Employee instead of
> Role. Please have a look at
> http://code.google.com/p/oopex/source/browse/trunk/modules/eclipselink1-jpa/src/queries-downcasts/java/oopex/eclipselink1/jpa/queries/DowncastsMain.java#139.
> I never tried this for defining Join-Reads/Batch-Reads, though.
>
> @Tom: Using downcasts in queries is, from my point of view, something quite
> natural if you design your model in a truly object oriented way. Reiterating
> that this feature nobody is needing, does not make disappear. The other
> feature, Mario is looking for, might be called "fetch-plans/plans" - as in
> the old JDO days ;-). See
> http://wiki.eclipse.org/EclipseLink/Development/JPA/NestedFetchGroups, last
> item in the open issues list. Also this feature(-request) I see frequently
> in projects when there is a CRUD-style rich client.
>
> -- Frank
>
> Meet me at JFS2009 http://www.java-forum-stuttgart.de/
>
>
> Mario Sandro Schwarz wrote:
>> I did already try it with a customizer based on the workaround from Doug
>> Clarke "Accessing un-mapped attributes using QueryKey "
>> (http://wiki.eclipse.org/EclipseLink/Development/JPA/QueryDownCast). The
>> role entity class was annotated with this Customizer:
>>
>>
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users