Mailing-List: contact cocoon-dev-help@xml.apache.org; run by ezmlm
Precedence: bulk
Reply-To: cocoon-dev@xml.apache.org
Date: Mon, 27 Jan 2003 10:18:00 +0100
To: cocoon-dev@xml.apache.org
Subject: Re: [ESQL] Improvement....
Message-ID: <20030127091800.GF21696@bremen.dvs1.informatik.tu-darmstadt.de>
Reply-To: haul@informatik.tu-darmstadt.de
References: <53015.10.0.0.1.1043515198.squirrel@ags01.agsoftware.dnsalias.com>
 <200301271125.14977.niclas@hedhman.org> <3E34E741.5070702@dff.st>
 <200301271604.03672.niclas@hedhman.org>
 <55866.10.0.0.1.1043657348.squirrel@ags01.agsoftware.dnsalias.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <55866.10.0.0.1.1043657348.squirrel@ags01.agsoftware.dnsalias.com>
Organization: Databases and Distributed Systems Group,
 Darmstadt University of Technology
User-Agent: Mutt/1.5.3i
From: Christian Haul <haul@dvs1.informatik.tu-darmstadt.de>

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.

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

Anyway, since you offered to produce a patch, we should consider it.

My 2�

	Chris.
-- 
C h r i s t i a n       H a u l
haul@informatik.tu-darmstadt.de
    fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

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