[Hibernate] Join query on two tables results in lots of queries

[Andres Dübi]

Hi there 

I have one table of CDRs (MMS_CDRS) and another table (MMS_DESTINATIONS) which 
contains the destinations in the CDRs.
The field CDRID in the table MMS_DESTINATIONS has a foreign key, the field CDRID in 
the table MMS_CDRS.

I do a query e.g.:
from o in class com.hp.mms.bo.MMSCdrBO o.timeOfEvent between :dateFrom and :dateTo

my problem is that hibernate first queries only the table MMS_CDRS and then queries 
the table MMS_DESTINATIONS for each row in the resultset from the previus query.
This is extremely slow - how can I motivate Hibernate to do one query which lets the 
DBMS (which is Oracle) to do the join?
Below you find my config and so on.


Thanks a lot for any answers - I could find an answer on this in the pdf doc (which is 
btw extremely good!)


Regards
Andres Dübi



***** SQL Creation commands *****

CREATE TABLE &&SCHEMA_OWNER..MMS_CDRS (
  CDRID               NUMBER (14),
  SESSIONID           NUMBER (10),
  MESSAGEID           NUMBER (10),
  TIMEOFEVENT         DATE,
  RECEIVETIME         DATE,
  EXPIRATIONTIME      DATE,
  ORIGINATOR          VARCHAR2 (255),
  EVENTTYPE           NUMBER (2),
  EVENTDISPOSITION    NUMBER (2),
  MSGSIZE             NUMBER (10),
  COMPONENTID         VARCHAR2 (255),
  ORIGINATORTYPE      NUMBER (2),
  ORIGINATORDEVICE    VARCHAR2 (7))
  TABLESPACE &&TBSDATA;

CREATE TABLE &&SCHEMA_OWNER..MMS_DESTINATIONS (
  DESTINATIONID       NUMBER (14),
  CDRID               NUMBER(14),
  DESTINATION         VARCHAR2 (255),
  RECIPIENTTYPE       NUMBER (2),
  RECIPIENTDEVICE     VARCHAR2 (7))
  TABLESPACE &&TBSDATA;



***** Hibernate mappings *****

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM 
"http://hibernate.sourceforge.net/hibernate-mapping.dtd";>
<hibernate-mapping>
  <class name="com.hp.mms.bo.MMSCdrBO" table="mms_cdrs" discriminator-value="V">
    <id name="cdrId" column="cdrId" type="long" unsaved-value="null">
      <generator class="sequence">
        <param>mms_cdrs_sequence</param>
      </generator>
    </id>
    <set role="destinations"
         table="mms_destinations"
         lazy="false"
         cascade="all">
      <key column="cdrId"/>
      <one-to-many class="com.hp.mms.bo.MMSCdrDestinationBO"/>
    </set>
    <property name="sessionId" type="long"/>
    <property name="messageId" type="long"/>
    <property name="timeOfEvent" type="timestamp"/>
    <property name="receiveTime" type="timestamp"/>
    <property name="expirationTime" type="timestamp"/>
    <property name="originator" type="string"/>
    <property name="eventType" type="short"/>
    <property name="eventDisposition" type="short"/>
    <property name="msgSize" type="long"/>
    <property name="componentId" type="string"/>
  </class>

  <class name="com.hp.mms.bo.MMSCdrDestinationBO" table="mms_destinations" 
discriminator-value="V">
    <id name="destinationId" column="destinationId" type="long" unsaved-value="null">
      <generator class="sequence">
        <param>mms_destinations_sequence</param>
      </generator>
    </id>
    <property name="cdrId" type="long"/>
    <property name="destination" type="string"/>
  </class>

</hibernate-mapping>



***** My java code which does the query *****

String queryString = "from o in class com.hp.mms.bo.MMSCdrBO o.timeOfEvent between 
:dateFrom and :dateTo";
Query query = sess.createQuery(queryString);
//setting of the params snipped
Collection cdrs = query.list();



***** The queries done by Hibernate *****

SELECT o.cdrId as cdrId, o.sessionId as sessionId, o.messageId as messageId, 
o.timeOfEvent as timeOfEvent, o.receiveTime as receiveTime, o.expirationTime as 
expirationTime, o.originator as originator, o.eventType as eventType, 
o.eventDisposition as eventDisposition, o.msgSize as msgSize, o.componentId as 
componentId FROM mms_cdrs o WHERE (o.timeOfEvent between ? and ? )


SELECT mms_d0.destinationId, mms_d0.destinationId AS destinationId, mms_d0.cdrId as 
cdrId, mms_d0.destination as destination FROM mms_destinations mms_d0 WHERE 
mms_d0.cdrId = ?
(lots of those)
SELECT mms_d0.destinationId, mms_d0.destinationId AS destinationId, mms_d0.cdrId as 
cdrId, mms_d0.destination as destination FROM mms_destinations mms_d0 WHERE 
mms_d0.cdrId = ?


-------------------------------------------------------
This SF.NET email is sponsored by:
SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See!
http://www.vasoftware.com
_______________________________________________
hibernate-devel mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/hibernate-devel