Armin,
I'm using 1.0.4.
I am not doing a QueryByIdentity. I am doing a QueryByCriteria. So when
I do:
QueryByCriteria query = QueryFactory.newQuery(Article.class,
criteria, true);
broker.getCollectionByQuery(query);
my expectation would be that the SQL generated would be:
-- Which Identities match my criteria?
select distinct Artikel_Nr from Artikel where ...
-- for cache misses:
select Artikel_Nr, Artikelname, Lieferanten_Nr, ... from Artikel
where Artikel_Nr = ?
But instead, here's what happens:
-- Which Identities match my criteria?
select distinct Artikel_Nr, Artikelname, Lieferanten_Nr, ... from
Artikel where ...
-- for cache misses:
select Artikel_Nr, Artikelname, Lieferanten_Nr, ... from Artikel
where Artikel_Nr = ?
When all of the irrelevant columns are included in the first query, that
query slows down dramatically. So what I'm wondering is why the first
query includes all of these columns that it doesn't need, rather than just
retrieving the PKs?
thanks,
-steve
Steve Clark
ECOS Development Group
[EMAIL PROTECTED]
(970)226-9291
Armin Waibel <[EMAIL PROTECTED]>
06/26/2006 11:55 AM
Please respond to
"OJB Users List" <[email protected]>
To
OJB Users List <[email protected]>
cc
Subject
Re: Why do queries for Identity retrieve all columns?
Hi Steve,
which version of OJB do you use?
Do you use
Identity xyz = broker.serviceIdentity().buildIdentity(Article.class, new
Integer(7));
Collection col = broker.getObjectByIdentity(xyz)
or a QueryByIdentity object?
regards,
Armin
[EMAIL PROTECTED] wrote:
> I have always wondered about this, but now I am encountering a serious
> performance problem which results from this policy.
>
> When executing a Query, OJB generates two different rounds of SQL. The
> first round is used just to determine the Identity of any matching
> objects. These Identities are then checked against the cache, and the
> database is queried again to materialize any missing objects.
>
> Both the first round and the second retrieve all of the known columns,
> even though the first one only needs the PKs.
>
> My situation: I have a table with about 20 columns, including some large
> text columns. I'm doing a query which ends up joining to a dozen other
> tables, and I have to use 'distinct'. Before applying 'distinct', I
have
> millions of rows in my result set; afterwards, I have about 700. If I
> query just for the PK, the query takes a few seconds to return the 700
PK
> values. If I query for all of the columns, it takes 3 minutes to
> distinct-ify the millions of duplicate rows down to the 700 actually
> distinct ones.
>
> Does anybody have a solution? Why does the initial query have to
retrieve
> all of the columns instead of just the PK(s)?
>
> thanks,
> -steve
>
> Steve Clark
> ECOS Development Group
> [EMAIL PROTECTED]
> (970)226-9291
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
