aspose file tools*
The moose likes Object Relational Mapping and the fly likes Persistence in the Enterprise - efficiency Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Persistence in the Enterprise - efficiency" Watch "Persistence in the Enterprise - efficiency" New topic
Author

Persistence in the Enterprise - efficiency

Kelahcim Kela
Greenhorn

Joined: Aug 17, 2007
Posts: 29
Hello guys,

I have gone through the content of your book briefly and I can see that you focus on few, well known ORMs. In this context, I'd like to ask a question regarding sub-queries and left/right outer joins.

These queries, usually, take a huge amount of time for a Object-Relational mapping tools. I haven't used any of these (apart from JDBC) for quite some time thus I have a question. Which ORM would you suggest for such a queries? Which one makes a best deal when it comes to efficiency/elegance of code relation.

While presenting examples, do you base on simple queries that are fairly simple to implement or do you show examples that involve multiple tables during query execution?

Another question is. What would you suggest (which ORM environment) for tree based structures stored in database - I am focusing on the performance here.

Cheers and thanks in advance for the answers

Michal
Anil Vupputuri
Ranch Hand

Joined: Oct 31, 2000
Posts: 527
Apart from these. How good ORM is at reading/writing blob's?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Anil Vupputuri:
Apart from these. How good ORM is at reading/writing blob's?


Its not ORMs that do this. Its JDBC. So ORMs are as good as your driver is.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


These queries, usually, take a huge amount of time for a Object-Relational mapping tools.

I'm curious where you got this information? Is this your experience? As far as query execution goes, I can't think of things an ORM could be doing to produce a noticable perfomance dip; regardless of what issued the query execution time is not going to change. Or are you interested in ways to avoid having an ORM produce queries like this in the first place?


Another question is. What would you suggest (which ORM environment) for tree based structures stored in database - I am focusing on the performance here.

Not sure what an ORM can do here. Presumably your domain model will use some sort of tree object (a TreeModel, or an XML document for example). The ORM doesn't define how you implement this in your entity-relational model. So its picking neither the object your model ends up in or the table structure it gets its data from. Are you looking for ORMs that have a specific Tree type or something?
Kelahcim Kela
Greenhorn

Joined: Aug 17, 2007
Posts: 29
During Hibernate evaluation I have found that following relation:

Class A [1 - *] Class B

is retrieved following way:

1. Retrieve all objects of Class A that meet particular condition
2. For each object of type Class A find corresponding objects of type Class B

Class A and Class B where mapped to Table A and Table B respectively.

It might be that I have used incorrect form of mapping. Anyway, the efficiency was very low.

I hope I have clarified my case.

[ July 24, 2008: Message edited by: kelahcim kela ]
[ July 24, 2008: Message edited by: kelahcim kela ]
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
Originally posted by kelahcim kela:
During Hibernate evaluation I have found that following relation:

Class A [1 - *] Class B

is retrieved following way:

1. Retrieve all objects that meet particular condition
2. For each object from Class A find corresponding objects from Class B

Class A and Class B where mapped to Table A and Table B respectively.

It might be that I have used incorrect form of mapping. Anyway, the efficiency was very low.

I hope I have clarified my case.


Actually, yes, you were doing an incorrect (or at least inefficient) mapping. Hibernate has extensive facilities for making this kind of query much more efficient. In our book (which is not meant as a Hibernate reference, mind you) we do cover how Hibernate addresses this through the use of the join fetch option when you define the relationship, in addition to many other tuning options.


Kyle Brown, Author of Persistence in the Enterprise and Enterprise Java Programming with IBM Websphere, 2nd Edition
See my homepage at http://www.kyle-brown.com/ for other WebSphere information.
Kelahcim Kela
Greenhorn

Joined: Aug 17, 2007
Posts: 29
Originally posted by Kyle Brown:


Actually, yes, you were doing an incorrect (or at least inefficient) mapping. Hibernate has extensive facilities for making this kind of query much more efficient.


Ok. That might be the case. In fact I was basing on a simple mappings while trying to retrieve more, let's say, complicated data.

Thanks for the answer. I'll take a look a little bit deeper into Hibernate facilities.
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

how Hibernate addresses this through the use of the join fetch option


Indeed, efficiencies about when you take this type of approach.

In the end, Hibernate code just gets converted to JDBC over your driver, so when it comes to the underlying efficiencies of the system, that's where they lay.

-Cameron McKenzie
Roland Barcia
author
Ranch Hand

Joined: Apr 15, 2004
Posts: 181
Originally posted by kelahcim kela:
Hello guys,

I have gone through the content of your book briefly and I can see that you focus on few, well known ORMs. In this context, I'd like to ask a question regarding sub-queries and left/right outer joins.


So in the book, we do have a left outer join mapping for loading the customer (along with an open order[we had a constraint on one open order at a time], line items with product, if it exists).

With JDBC, IBatis, and pureQuery, the SQL looked like this:

SELECT c.CUSTOMER_ID,c.OPEN_ORDER,c.NAME,c.BUSINESS_VOLUME_DISCOUNT,c.BUSINESS_PARTNER,c.BUSINESS_DESCRIPTION,c.RESIDENTIAL_HOUSEHOLD_SIZE,c.RESIDENTIAL_FREQUENT_CUSTOMER,c.TYPE,o.ORDER_ID,o.STATUS,o.TOTAL,l.PRODUCT_ID,l.ORDER_ID,l.QUANTITY,l.AMOUNT,p.PRODUCT_ID,p.PRICE,p.DESCRIPTION FROM CUSTOMER c LEFT OUTER JOIN (ORDERS o LEFT OUTER JOIN (LINE_ITEM l JOIN PRODUCT p ON l.PRODUCT_ID = p.PRODUCT_ID)ON o.ORDER_ID = l.ORDER_ID) ON c.OPEN_ORDER = o.ORDER_ID WHERE c.CUSTOMER_ID = #value#

I found that using a query that I specified was the most efficient way.

For JPA, I used mapping to try to achieve the same, but found it difficult to get the exact SQL to match the above.

On the AbstractCustomer class, I had:


@OneToOne(fetch=FetchType.EAGER,cascade = {CascadeType.MERGE,CascadeType.REFRESH},optional=true )
@JoinColumn(name="OPEN_ORDER", referencedColumnName = "ORDER_ID")
protected Order openOrder;

On the Order class I had:
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID")
protected AbstractCustomer customer;

@OneToMany(cascade=CascadeType.REMOVE,fetch=FetchType.EAGER,mappedBy="order" )
protected Set<LineItem> lineitems;

One The LineItem, I had:

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumns({
@JoinColumn(name="PRODUCT_ID", referencedColumnName = "PRODUCT_ID")})
protected Product product;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({
@JoinColumn(name="ORDER_ID", referencedColumnName = "ORDER_ID")})
protected Order order;

Putting Eager on mappings I wanted to load when the customer got accessed and lazy where not. I got close to the SQL, but ORM still added extra joins back to the tables in some cases (Both JPA and Hibernate).

In general, I found the SQL approach the best for this type of query.

If you have this type of query less often (20% or less). Then using an ORM that allows you to drop down to native SQL is good. So JPA and Hibernate allowed for both. If you are doing this type of query more often in your app, an SQL based framework seems better.


These queries, usually, take a huge amount of time for a Object-Relational mapping tools. I haven't used any of these (apart from JDBC) for quite some time thus I have a question. Which ORM would you suggest for such a queries? Which one makes a best deal when it comes to efficiency/elegance of code relation.


While presenting examples, do you base on simple queries that are fairly simple to implement or do you show examples that involve multiple tables during query execution?



Another question is. What would you suggest (which ORM environment) for tree based structures stored in database - I am focusing on the performance here.

Cheers and thanks in advance for the answers

Michal


So I have a more detailed example I use for customers that did not make the book. I extended the Products and added the notion of Category. The Category is a tree structure and I was able to get what I wanted with JPA and dropping down to native queries.


Roland Barcia: IBM Distinguished Engineer, CTO Mobile for Lab Services
Roland Barcia
author
Ranch Hand

Joined: Apr 15, 2004
Posts: 181
Originally posted by Anil Vupputuri:
Apart from these. How good ORM is at reading/writing blob's?


Most ORM's have a Blob mapping today. I know Hibernate and JPA do, and they seem to do an ok job.
 
 
subject: Persistence in the Enterprise - efficiency