hi alexey,
afaik prefetching work ok for 1:n relationships. what kind of problem did
you have with it ?
> Does every query, that appear in log file realy executed by database or
not
you'd better use p6spy to log sql-statements.
jakob
----- Original Message -----
From: "Alexey Drougov" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Friday, January 10, 2003 9:57 AM
Subject: Re: why 1:n relationship produces 2*(n+1) queries ?
> 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]>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
