cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hunsberger, Peter" <>
Subject RE: [ESQL] Improvement....
Date Mon, 27 Jan 2003 18:03:13 GMT
>>> This is the example I tried to explain:
>>> I have a table that store the status of some tickets. You always know 
>>> how
>> many status there can be. > If you said:
>>> 1-Open
>>> 2-Close
>>> 3-Invalid
>>> Then if you want to show the history, you will ask for LIMIT 3, but 
>>> the
>> database will try to find
>>> the 4th row after finding the only 3 that can exist.
>>> This is why I told this is a performance issue. Not an error.
>> I sure hope you've normalized your database so that "status" is stored 
>> in a separate table from the rest of the history? If so, you should 
>> have a proper primary key in the history table that you can index on 
>> and the search will stop after looking at 4 records; the three that 
>> match and first one that doesn't match (at which point it will know 
>> the search is done.)
> I think this is not a issue of how I build my database or not. This is
more general. Maybe this
> improvement will does not help nothing in some Database constructions and
give too much in
> performance.

No, this is always a database design issue.

> My point of view is: "If a user dont want to know if there are or not more
results. We must almost > build the correct SQL clausule". In this case the
correct clausule is:
> and not LIMIT X+1

In a properly designed database there should effectively be no difference
between the two clauses.

> Of course if you need to know if there are more rows, then the correct SQL
clausule is:
> This is without thinking if you use Oracle, PotgreSQL or any other DB
Engine. I think that the
> improvement will always helps.
> At the end, how much time take for a processor decide to use X or X+1 vrs.
how much time will have
> to search the X+1 row in a big table? I think the second one will be
always slower regarless if you
> index or not. Then lets help the database Engine with better SQL
clausules. Tha will helps to build
> a faster Database interface for Cocoon. ;-)

In a properly designed database the improvement will be too small to

Now, you might argue that we should not try and force the user to have a
properly designed database.  I agree that it would be nice, however, it is
an impossible task: if you optimize for one bad design I can always find a
counter example where your optimization introduces worse performance.  In
this particular case there is however another line of attack, and that it to
look at having Cocoon use two different ways to stop the search.  Each of
these would be independent of each other and not interact.  You would choose
one or the other for any given query...

To unsubscribe, e-mail:
For additional commands, email:

View raw message