BorlandTalk.com

Leonardo M. Ramé · Guest

I'm facing a problem when trying to optimize an ORM library I wrote for internal use at our company aparently resolved by Hibernate thanks to its "join fetch" feature.

An example of the problem is this:

Person object contains a collection of Sales, a typical one to many relationship currently resolved by two queries, one to retrive Person object and a second one to retrieve its Sales collection. It's fast, but involves two queries turning somewhat inefficient if the database must deal with large recordsets.

According to this site http://www.realsolve.co.uk/site/tech/orm-performance.php Hibernate resolves the problem by creating a query similar to this:

Select person.*, s.* from person p
inner join sales s on s.idperson=p.idperson

And fill the data from person table to the Person object and Person.Sales with sales data extracted from the same resultset.

Creating a similar mapping isn't very difficult with our ORM, the problem is how to handle relations between objects that aren't mapped to tables but queries. To clarify my point, nothing more clear than an example:

We use a similar to Hibernate XML definition of mapped classes. In my example, the mapping should be something like this:

Mapping of classes Person and Sales:

<class name="TPerson">
<table name="persons"/>
<primary key="idperson"/>
<many-to-one class="sales" key="idperson"/>
</class>

<class name="TSales">
<table name="sales"/>
<primary key="idsale"/>
</class>

TPerson and TSales classes:

TSale = class(TCollectionItem)
private
FIdSale: Integer;
FIdPerson: Integer;
FAmount: Double;
published
property IdSale: Integer read ...
...
end;

TSales = class(TCollection)
end;

TPerson = class(TPersistent)
private
FIdPerson: Integer;
FName: string;
FSales: TSales;
published
property IdPerson: Integer read ...write ...
property Sales: TSales read ... write ... // <-- note the TSales instance
...
end;

Well, the problem for us is, in some circunstances there's not such Sales table, but a standard sql query in charge of retriving tabular information our ORM maps to a TSales object. The modified map of TSales should be like this:

<class name="TSales">
<table name="sales"/>
<query>
select sum(amount) as amount, idperson, date from sales
group by sales.date
<query>
<primary key="idsale"/>
</class>

Note that I used a query to retrieve the Sum of "amount" column (or property in TSales class) grouped by date.

If the ORM tryes to create the SQL for the one-to-many relationship, something like this will be created:

Select person.*, s.* from person p
inner join
(select sum(amount) as amount, idperson, date from sales
inner join sales s on s.idperson=p.idperson) s on s.idperson=p.idperson

Of course, no one sql engine will accept that statement.

Any of you knows a solution for my problem?

Thanks in advance,
Leonardo M. Ramé
http://leonardorame.blogspot.com

Joanna Carter [TeamB] · Guest

Leonardo M. Ramé a écrit :

Leonardo M. Ramé · Guest

Joanna, thanks for your response. Maybe my example is not good enough to
explain my point. What I try to do is to replicate Hibernate's "fetch
join" feature.

Using your example, suppose you have to retrive a SalesOrder including
its Person data and show it on screen. Actually, to resolve such cases,
we use a new class, lets call it "ExtendedSalesOrder" wich includes the
person data, then we map it to a query like this:

select SalesOrder.*, Person.*
from SalesOrders
inner join Person on SalesOrders.PersonId = Person.PersonId

The benefit of a "fetch join" is that this type of queries could be
created automatically by the ORM.

Joao Morais · Guest

Leonardo M. Ramé wrote:

Joanna Carter [TeamB] · Guest

Leonardo M. Ramé a écrit :

Joanna Carter [TeamB] · Guest

Joao Morais a écrit :

Peter Morris [Droopy eyes · Guest

Joao Morais · Guest

Joanna Carter [TeamB] wrote:

Leonardo M. Ramé · Guest

Joanna Carter [TeamB] escribió:

"Our base business class has the idea of lazy-fetch objects which
contain a property bag which is observed by the OPF. When any property
that holds an object is first retrieved, the property object for the
object property is created, the ID is assigned and the property name is
added to the lazy object list. But the object is only retrieved when one
of its properties is first read. This is similar to your idea of a proxy
object and can, as you point out, save a lot of database fetches. "

This sounds interensting, can you point me to url with some examples of
this methodology?

Joanna Carter [TeamB] · Guest

Peter Morris [Droopy eyes software] a écrit :

Joanna Carter [TeamB] · Guest

Leonardo M. Ramé a écrit :

Peter Morris [Droopy eyes · Guest

Yes, that did not occur to me at first. A hierarchy selection will return 1
row per object, whereas this topic will return 1 row per Sale and also
repeat the same Person information for each sale.