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 09:43:23 GMT
Christian Haul dijo:
> On 27.Jan.2003 -- 02:49 AM, Antonio Gallardo wrote:
>> Niclas Hedhman dijo:
>> > On Monday 27 January 2003 16:01, Torsten Curdt wrote:
>> >> Niclas Hedhman wrote:
>> >> > On Monday 27 January 2003 07:06, Torsten Curdt wrote:
>> >> >>>What do the database to try to find the 6th row?
>> >> >>>
>> >> >>>I think the answer is: scan the rest of the database after
>> finding
>> >> the ONLY 5 rows that already exist.
>> >> >>
>> >> >>No :) ...be sure - it doesn't
>> >> >
>> >> > Well, that depends on the WHERE clause. For instance;
>> >> >
>> >> > WHERE SQRT(  SQ( a - b ) + SQ( c - d ) + SQ( e - f ) ) < g ;
>> >> >
>> >> > definately go through every record, whether the fields are
>> indexed
>> >> or not.
>
> That's really bad! No way to refactor the schema or the query? Or use a
> differen DBMS? E.g. in INFORMIX you can create an index based on a user
> defined function...
>
>> 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.
>
> Again I believe an index would help a lot more. With bad luck your three
> rows are spread all over the table and you're going to have a nearly
> complete table scan anyways.

This is not bad luck. ;-)
I cannot find an easy example. My queries envolves 5 or 7 tables. I have a
status table. This table contains the date, ticket and status. This is
because sometimes you search by status.

I recently read that this is not a good idea indexing a field with small
posibles values in a big table. That is my case. I am using Postgres, but
in general this is true.

Niclas pointed a example. I know this is not a good code, but the idea is
there. The point is: I know there are only 5 row, but the LIMIT clausule
will be written as:

LIMIT 6.

That will force to search the entire table for a row that never exists.

>
> And if it does improve performance, why do you want to use the
> esql:limit tag instead of altering the SQL statement?

How can I alter the statement? I can write LIMIT 5, but the code will
rewrite it to LIMIT 6! This is the point. Sometimes we need to assure that
the code write LIMIT 5 not LIMIT 6.

This is why I suggested to just check if there is the <esql:more-results>
tag. If the tag is there, that means that the programmer is not sure if
there are or not more results.

Please think about this issue.

Best Regards,

Antonio Gallardo.

P.S: My english is very poor, sorry that I can not explain better this issue.



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


Mime
View raw message