cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Antonio Gallardo" <agalla...@agsoftware.dnsalias.com>
Subject RE: [ESQL] Improvement....
Date Mon, 27 Jan 2003 18:28:21 GMT
Hunsberger, Peter dijo:
>>>> 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:
>>
>> LIMIT X
>>
>> 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:
>>
>> LIMIT X+1
>>
>> 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
> measure....
>
> 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...

Yes, I agree with you. If someone need to stop the searching with a LIMIT
clausule, then better it would harcore the LIMIT clausule directly inside
the SQL query. That way we does not need to change the current code.

Thanks.

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




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


Mime
View raw message