Persistence in the Enterprise - efficiency

[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.

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]

Apart from these. How good ORM is at reading/writing blob's?

[Paul Sturrock]

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.

[Paul Sturrock]

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]

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]

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.

[Kelahcim Kela]

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]

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]

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]

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.