Hi Oliver,
if I used the p6spy I can see that Hibernate generates a huge sql statement
using joins, and OJB creates many smaller queries without joins.
OJB:
SELECT ID,id_street,FIRSTNAME,birthplace,birthday,NAME,citizenship FROM
PERSON WHERE ID = ?
SELECT ID,id_ZIPCODE,NAME FROM STREET WHERE ID = ?
SELECT CODE,ID,id_city FROM ZIPCODE WHERE ID = ?
SELECT ID,id_county,NAME FROM CITY WHERE ID = ?
SELECT ID,id_country,code,NAME FROM COUNTY WHERE ID = ?
SELECT iso3,ID,iso2,NAME FROM COUNTRY WHERE ID = ?
SELECT ID,id_county,NAME FROM CITY WHERE ID = ?
SELECT ID,id_country,code,NAME FROM COUNTY WHERE ID = ?
SELECT iso3,ID,iso2,NAME FROM COUNTRY WHERE ID = ?
SELECT iso3,ID,iso2,NAME FROM COUNTRY WHERE ID = ?
Hibernate:
select city1_.id as id0_, city1_.name as name0_, city1_.id_county as
id_county0_, county2_.id as id1_, county2_.name as name1_, county2_.code as
code1_, county2_.id_country as id_country1_, country3_.id as id2_,
country3_.name as name2_, country3_.iso2 as iso22_, country3_.iso3 as
iso32_, street4_.id as id3_, street4_.name as name3_, street4_.id_zipcode as
id_zipcode3_, zipcode5_.id as id4_, zipcode5_.code as code4_,
zipcode5_.id_city as id_city4_, country6_.id as id5_, country6_.name as
name5_, country6_.iso2 as iso25_, country6_.iso3 as iso35_, person0_.id as
id6_, person0_.name as name6_, person0_.firstName as firstName6_,
person0_.birthday as birthday6_, person0_.birthPlace as birthPlace6_,
person0_.id_street as id_street6_, person0_.citizenship as citizens7_6_,
person0_.name || ' ' || person0_.firstName as f0_6_ from Person person0_,
City city1_, County county2_, Country country3_, Street street4_, Zipcode
zipcode5_, Country country6_ where person0_.id=? and
person0_.birthPlace=city1_.id(+) and city1_.id_county=county2_.id(+) and
county2_.id_country=country3_.id(+) and person0_.id_street=street4_.id(+)
and street4_.id_zipcode=zipcode5_.id(+) and
person0_.citizenship=country6_.id(+)
wow
is this also configurable on OJB?
Maybe it is helpfull to describe a little bit my testing database structure:
- there are countries in COUNTRY table, a country has counties in COUNTY, a
county has cities in CITY, a city has zipcodes in ZIPCODE, a zipcode has
streets in STREET.
- there are 20 milion persons in PERSON table, having a birthplace(foreign
key to CITY), an address (foreign key to STREET), a citizenship (foreign key
to COUNTRY) plus some other details stored in the columns of the PERSON
table
Beeing a rookie in OJB it is possible that my mappings are not the best.
Thanks for your quick feedback.
Regards,
Florin
PS: I have another questions about lazy-loading and mappings. In my Country
class I defined a counties filed which stores the counties in that country.
<class-descriptor class="florinp.Country" table="COUNTRY">
<field-descriptor name="id" column="ID" jdbc-type="BIGINT"
primarykey="true" autoincrement="true"/>
<field-descriptor name="name" column="NAME" jdbc-type="VARCHAR"/>
<field-descriptor name="iso2" column="iso2" jdbc-type="CHAR"/>
<field-descriptor name="iso3" column="iso3" jdbc-type="CHAR"/>
<collection-descriptor name="counties"
element-class-ref="florinp.County" auto-retrieve="false"
orderby="id" sort="DESC">
<inverse-foreignkey field-ref="id_country"/>
</collection-descriptor>
</class-descriptor>
<class-descriptor class="florinp.County" table="COUNTY">
<field-descriptor name="id" column="ID" jdbc-type="BIGINT"
primarykey="true" autoincrement="true"/>
<field-descriptor name="name" column="NAME" jdbc-type="VARCHAR"/>
<field-descriptor name="code" column="code" jdbc-type="CHAR"/>
<field-descriptor name="id_country" column="id_country"
jdbc-type="BIGINT"/>
<reference-descriptor name="country"
class-ref="florinp.Country">
<foreignkey field-ref="id_country"/>
</reference-descriptor>
<collection-descriptor name="cities" element-class-ref="florinp.City"
auto-retrieve="false"
orderby="id" sort="DESC">
<inverse-foreignkey field-ref="id_county"/>
</collection-descriptor>
</class-descriptor>
1. If I set the attribute auto-retrieve="true" all the loads work slower. If
I set it to "false" then there is a speed up but when I try to access the
Collection of the counties I receive NullPointerException. How do I init the
collection? Is it mandatory to use proxy for County?
2. In the County class, initially I wanted to have no id_country field, the
country field contains also an id; But I was unable to map the country
object without introducing that field. How can I overcome this situation?
3. Once again thank you for your patience.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 5:51 PM
To: [EMAIL PROTECTED]
Subject: RE: Loading an object
Hello Florin,
> -----Original Message-----
> From: Florin Pop [mailto:[EMAIL PROTECTED]
> I have tried your suggested approach but curiously the same result.
[..]
I would expect OJB to generate roughly the same
SQL statement. Provided that the execution of the SQL
dominates (which is questionable as Thomas and Armin
pointed out), I would expect about the same
performance.
Could you please capture the executed SQL statements
of both tests? I really would like to see if
they differ. I cannot imagine what Hibernate
does so much differently.
(Please refer to
FAQ "How can I trace and/or profile SQL statements executed by OJB?"
in http://db.apache.org/ojb/faq.html. The instructions
should work for Hibernate, too.)
Olli
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
