Hello !
Unfortunately adding crit.addPrefetchedRelationship("parameters"); don't
help, I tried it earlier.
Now I solve problem by fetching articles then attributes and then setting
attributes in articles manually:
... // fetch articles
Criteria criteria = new Criteria();
criteria.addLike("name", pattern);
Query query = QueryFactory.newQuery(Article.class, criteria);
Collection articles = broker.getCollectionByQuery(query);
...
// fetch attributes
...
Criteria attrCriteria = new Criteria();
ReportQueryByCriteria articleIdsQuery =
QueryFactory.newReportQuery(
Article.class, new String[]{"articleId"},
query.getCriteria(), true);
attrCriteria.addIn("objId", articleIdsQuery);
Query parQuery = QueryFactory.newQuery(Parameter.class,
parCriteria);
Collection parCollection = broker.getCollectionByQuery(parQuery);
...
// set attributes in articles
...
It works fast, however producing 6 queries:
(1) SELECT A0.ID,A0.LABEL FROM ARTICLE A0 WHERE A0.LABEL LIKE ?
(2) SELECT count(*) FROM ARTICLE A0 WHERE A0.LABEL LIKE ?
(3) SELECT DISTINCT A0.ID FROM ARTICLE A0 WHERE A0.LABEL LIKE ?
(4) SELECT A0.ATTRIBUTE,A0.ATTRIBUTEVALUE,A0.OBJ FROM OBJATTRIBUTE A0 WHERE
A0.OBJ IN
(SELECT DISTINCT A0.ID FROM ARTICLE A0 WHERE A0.LABEL LIKE ? )
(5) SELECT DISTINCT A0.ID FROM ARTICLE A0 WHERE A0.LABEL LIKE ?
(6) SELECT count(*) FROM OBJATTRIBUTE A0 WHERE A0.OBJ IN (SELECT DISTINCT
A0.ID FROM ARTICLE A0 WHERE A0.LABEL LIKE ? )
Why there are queries 3 and 5 ? How can I fix this ?
I use subquery to fetch attributes because using IN (articleId1,
articleId2,...) is much slower for large number of articles.
Does every query, that appear in log file realy executed by database or not
?
[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG: SQL:
SELECT DISTINCT A0.ID FROM ARTICLE A0 WHERE A0.LABEL LIKE ?
----- Original Message -----
From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 8:37 PM
Subject: Re: why 1:n relationship produces 2*(n+1) queries ?
> hi alexey,
>
> don't be upset !
> ojb is an or-mapping framework and thus executes more sqls than a manually
> coded solution.
>
> in your example ojb should execute 1 sql for articles and 1 sql for EACH
> attribute resulting in n + 1 sql for n attributes. the additional
count-sqls
> are afaik no longer executed.
> ojb even provides an optimized way to retrieve all attributes for all
> articles in ONE sql, this is called prefetched relationships.
>
> Criteria crit;
>
> crit.addPrefetchedRelationship("parameters"); // add this line to your
query
> ----- Original Message -----
> From: "Alexey Drougov" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, January 09, 2003 12:09 PM
> Subject: why 1:n relationship produces 2*(n+1) queries ?
>
>
> > Hello all,
> >
> > I have two object types: article and attribute for article
> >
> > repository.xml is as follows:
> > ...
> > <class-descriptor class="obj.Attribute" table="OBJATTRIBUTE" >
> > <field-descriptor id="1" name="objId" column="OBJ" jdbc-type="BIGINT"
> > primary-key="true"/>
> > <field-descriptor id="2" name="value" column="ATTRIBUTEVALUE"
> > jdbc-type="VARCHAR"/>
> > </class-descriptor>
> > ...
> > <class-descriptor class="obj.Article" table="ARTICLE">
> > <field-descriptor id="1" name="articleId" column="ID"
jdbc-type="BIGINT"
> > primary-key="true" />
> > <field-descriptor id="2" name="label" column="LABEL"
jdbc-type="VARCHAR"
> > />
> > <collection-descriptor name="parameters"
> > element-class-ref="obj.Attribute"
> > <inverse-foreignkey field-id-ref="1" />
> > </collection-descriptor>
> > </class-descriptor>
> > ...
> >
> > Java code is simple:
> > ...
> > Criteria criteria = new Criteria();
> > criteria.addLike("name", "%ab%");
> > Query query = QueryFactory.newQuery(Article.class, criteria);
> > Collection � = broker.getCollectionByQuery(query)
> > ...
> >
> >
> > Why this simple example derived from tutorial3 produces 2*(n+1) queries:
> >
> > // these for articles
> > [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> SQL:
> > SELECT A0.ID,A0.LABEL FROM ARTICLE A0 WHERE A0.LABEL LIKE ?
> >
> > // why to queriy count(*) when all rows are
> fetched
> > anyway ?
> > [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> SQL:
> > SELECT count(*) FROM ARTICLE A0 WHERE A0.LABEL LIKE ?
> >
> > // these for every article
> > [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> SQL:
> > SELECT A0.ATTRIBUTE,A0.ATTRIBUTEVALUE,A0.OBJ FROM OBJATTRIBUTE A0 WHERE
> > A0.OBJ = ?
> >
> > // why to queriy count(*) when all rows are
> fetched
> > anyway ?
> > [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> SQL:
> > SELECT count(*) FROM OBJATTRIBUTE A0 WHERE A0.OBJ = ?
> >
> > [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> SQL:
> > SELECT A0.ATTRIBUTE,A0.ATTRIBUTEVALUE,A0.OBJ FROM OBJATTRIBUTE A0 WHERE
> > A0.OBJ = ?
> > [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
> SQL:
> > SELECT count(*) FROM OBJATTRIBUTE A0 WHERE A0.OBJ = ?
> > ...
> >
> > perfomance is poor, I'm upset.
> >
> > How to make OJB make 2 queries: one for Articles, one for corresponding
> > Attributes ?
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
