cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hunsberger, Peter" <Peter.Hunsber...@stjude.org>
Subject RE: [ESQL] Improvement....
Date Tue, 28 Jan 2003 15:50:37 GMT
Just sent a blank reply by mistake, sorry about that.

>> Torsten,
>>
>> You're chasing a non-existent problem.  There is never a real life 
>> case that will have both good performance for N records and bad 
>> performance for
>> N+1 records.  The only way you can guarantee having good performance 
>> N+for N
>> records is if you can build an index.  If you can build an index then 
>> the search will always terminate after looking at N+1 records.
>
> I give you a "real world" case;
> 
> In natural language;
> Give me the first COLOR (3D point) which resides not further than dE units

> from COLOR [L1, a1, b1].
> 
> In SQL database, I can only place the L, a, b columns, individually, and
the 
> spherical search would be;
> 
> WHERE SQRT( SQ( L - L1 ) + SQ( a - a1) + SQ( a - a2 ) ) < dE
> 
> where L1, a1, b1 and dE are parameters given at the invocation of the
Select 
> statement.
>
> <exclusion>
> I could go into a long discussion how this could be optimized, both in SQL

> (multi-levelled subqueries and regeneration of indexes) and internal to
the 
> DB engine (3D indecies), but I won't.
> </exclusion>
>
> If I have a well populated database, evenly and randomly spread out, I
will on 
> the average have a 100% penalty on LIMIT N+1, as the search will go
through 
> roughly the same number of records to find the "next one", which I don't
care 
> about.

Niclas,

First, as you point out this particular case can be optimized. Moreover, I
believe you could add counting columns or other indexable columns that would
resolve the issue?

However, the real issue here is how should you be limiting the query? IIRC
the LIMIT +1 logic was added fairly recently in order to solve a bug that
someone else encountered.  Kludging up this logic to fix a different problem
doesn't make sense.  In your case the SQL has business logic embedded in it
and as such it makes sense to completely qualify this logic and add the
LIMIT clause (or a HAVING) directly to the SQL statement.  There can't be a
need to dynamically change the limit (?) and as such you don't need the
ability to sub the limit in from the ESQL as a parameter...???




---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org


Mime
View raw message