db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: Extent tables unnecessarily added to query statement
Date Mon, 17 Mar 2003 17:47:45 GMT
hi ron,

imo i've fixed this one some time ago:

        TableAlias(ClassDescriptor cld, String alias, boolean 
lookForExtents)
        {
            this.cld = cld;
            this.table = cld.getFullTableName();
            this.alias = alias;

            // BRJ : build alias for extents, only one per Table
            if (lookForExtents)
            {
                List ext = 
cld.getRepository().getConcreteSubclassesOf(cld.getClassOfObject());
                ClassDescriptor cd;
                Set extSet = new HashSet();  // only one Alias per Table

                for (int i = 0; i < ext.size(); i++)
                {
                    cd = cld.getRepository().getDescriptorFor((Class) 
ext.get(i));
                    extSet.add(new TableAlias(cd, alias + "E" + i, 
false));                
                }
               
                extents.addAll(extSet);
            }
        }

hth
jakob

Ron Gallagher wrote:

>Problem synopsis...
>
>It appears that extent tables are being added to a sql statement unnecessarily.
>
>Here's my situation...
>
>I have two tables, INSPCTN (Inspection) and INSPCTN_TYP (InspectionType), that are in
a 1:n relationship.  There are two types of inspection types, standard and enhanced, so I've
defined two extents for the InspectionType class.  Here are what I believe are the relevant
extracts from the repository.xml file:
>
>  <class-descriptor class="com.rgi.Inspection" table="INSPCTN">
>    <field-descriptor column="INSPCTN_ID" id="1" jdbc-type="DECIMAL" name="inspectionId"
nullable="false" primarykey="true"/>
>    <field-descriptor column="INSPCTN_TYP_ID" id="2" jdbc-type="DECIMAL" name="inspectionTypeId"
nullable="false" primarykey="false"/>
>    <reference-descriptor auto-retrieve="false" class-ref="com.rgi.InspectionType"
name="inspectionTypeRef">
>      <foreignkey field-ref="inspectionTypeId"/>
>    </reference-descriptor>
>  </class-descriptor>
>
>  <class-descriptor class="com.rgi.InspectionType" table="INSPCTN_TYP">
>    <extent-class class-ref="com.rgi.StandardInspectionType"/>
>    <extent-class class-ref="com.rgi.EnhancedInspectionType"/>
>    <field-descriptor column="INSPCTN_TYP_ID" id="1" jdbc-type="DECIMAL" name="inspectionTypeId"
nullable="false" primarykey="true"/>
>    <field-descriptor column="INSPCTN_TYP_NAME" id="2" jdbc-type="VARCHAR" length="30"
name="inspectionTypeName" nullable="false" primarykey="false"/>
>  </class-descriptor>
>
>  <class-descriptor class="com.rgi.StandardInspectionType" table="INSPCTN_TYP">
>    <field-descriptor column="INSPCTN_TYP_ID" id="1" jdbc-type="DECIMAL" name="inspectionTypeId"
nullable="false" primarykey="true"/>
>    <field-descriptor column="INSPCTN_TYP_NAME" id="2" jdbc-type="VARCHAR" length="30"
name="inspectionTypeName" nullable="false" primarykey="false"/>
>  </class-descriptor>
>
>  <class-descriptor class="com.rgi.EnhancedInspectionType" table="INSPCTN_TYP">
>    <field-descriptor column="INSPCTN_TYP_ID" id="1" jdbc-type="DECIMAL" name="inspectionTypeId"
nullable="false" primarykey="true"/>
>    <field-descriptor column="INSPCTN_TYP_NAME" id="2" jdbc-type="VARCHAR" length="30"
name="inspectionTypeName" nullable="false" primarykey="false"/>
>  </class-descriptor>
>
>Here's my dilema...
>
>I want a list of Inspections where the name of the inspection type is a specific value.
 So, I build and execute a query...
>
>    public Collection getListForTypeName(String inspectionTypeName) {
>        PersistenceBroker broker = ...
>        Criteria criteria = new Criteria();
>        criteria.addEqualTo("inspectionTypeRef.inspectionTypeName",inspectionTypeName);
>        QueryByCriteria query = new QueryByCriteria(Inspection.class,criteria);
>        Collection result = broker.getCollectionByQuery(query);
>    }
>
>The sql that's generated looks like this:
>
>SELECT INSPCTN_ID, INSPCTN_TYP_ID
>FROM INSPCTN A0
>, INSPCTN_TYP A1
>, INSPCTN_TYP A1E0
>, INSPCTN_TYP A1E1
>WHERE A0.INSPCTN_TYP_ID=A1.INSPCTN_TYP_ID
>AND A0.INSPCTN_TYP_ID=A1E0.INSPCTN_TYP_ID(+)
>AND A0.INSPCTN_TYP_ID=A1E1.INSPCTN_TYP_ID(+)
>AND (A1.QUERY_DATA_TYP_NAME =  'XXX'  OR A1E0.QUERY_DATA_TYP_NAME =  'XXX'   OR A1E1.QUERY_DATA_TYP_NAME
=  'XXX' )
>
>The problem that I (and my dba's) have is the inclusion of the INSPCTN_TYP table in the
query three times.  In this example, it's not too severe.  There are only 2 extents for the
InspectionType class.  However, there are several situations in our application where we will
have a significant number of extents (20+) for a single table/base class.  It will be unacceptable
to our dbas to include the base table in the sql statement once for the base table and once
for each extent.
>
>Here's my proposed solution...
>
>org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement includes a nested class called
TableAlias.  The main constructor for this class (TableAlias(ClassDescriptor, String, boolean))
includes logic to populate a List with all extents for the specified ClassDescriptor.  I am
proposing that entries be added to this list only if the full table name for the extent class
does not match the full table name for the specified ClassDescriptor.  Something like this:
>
>    TableAlias(ClassDescriptor cld, String alias, boolean lookForExtents)
>    {
>        this.cld = cld;
>        this.table = cld.getFullTableName();
>        this.alias = alias;
>    
>        // BRJ : build alias for extents, only one per Table
>        if ( lookForExtents ) {
>            List ext = cld.getRepository().getConcreteSubclassesOf(cld.getClassOfObject());
>            ClassDescriptor cd;
>            Set extSet = new HashSet();  // only one Alias per Table
>      
>            for ( int i = 0; i < ext.size(); i++ ) {
>              cd = cld.getRepository().getDescriptorFor((Class) ext.get(i));
>              // Add a new extent entry only if the full table name of the extent class
>              // does not match the full table name of the 'base' class descriptor.
>              if (!cd.getFullTableName().equals(this.table)) {
>                  extSet.add(new TableAlias(cd, alias + "E" + extSet.size(), false));
>              }
>        }
>    
>        extents.addAll(extSet); 
>        }
>    }
>
>I grabbed the latest code from cvs this morning and ran the unit tests before and after
implementing this change.  There was no change in the results of the unit tests as a result
of this change.
>
>Please let me know what you think of my solution.
>
>Ron Gallagher
>Atlanta, GA
>rongallagher@bellsouth.net
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
>  
>


Mime
View raw message