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: [EMAIL PROTECTED] > For additional commands, email: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
