You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Ron Gallagher <rg...@bellsouth.net> on 2003/03/17 17:30:18 UTC
Extent tables unnecessarily added to query statement
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
Re: Extent tables unnecessarily added to query statement
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
>
>
>
>