| Subject | RE: [firebird-support] The First to select |
|---|---|
| Author | Helen Borrie |
| Post date | 2006-02-13T04:31:34Z |
At 03:07 PM 13/02/2006, you wrote:
experiences with a single table ordered on the primary key doesn't
come close to this question for relevance.
SELECT FIRST m doesn't make sense *unless* it's from an ordered
set. The engine can't know which are the first m records of the full
set until it has the full set and then has sorted it. If joins are
involved, then the joins will happen first, since the content of set
won't be known until the join criteria have been satisfied.
SKIP n also implies an ordered set. All the rows have to be there,
in order for it to skip the first n and deliver m rows starting at n
+ 1 in the order.
Provided you use it appropriately (reasonable sets from tables with
good indexes), this syntax doesn't need to be unbearably slow.
Furthermore (though it's another red herring with respect to the
question asked), there is no sensible reason on earth to use SELECT
FIRST syntax in a stored procedure.
./heLen
> >Get ready to stand corrected. A simplistic answer based on your
> > Hi guys,
> > I'm wondering if anyone out there knows the inside workings of the
> > select FIRST(x) mechanism.
> > My question is, In a complex select First query with inner joined
> > tables, does firebird do the required calculation and comparing to
> > attain the first X num of records, then stop executing and return the
> > results OR does the required calculation and comparing for ALL records
> > but only returns the first X??
> > Any answers or comments are highly appreciated!!
> >
>
>Helen has always said that the latter is the true state, i.e. a full scan is
>performed before an order by clause starts filtering the return set but in
>my use of the FIRST clause even using Selectable SPs, I experience such an
>improved performance when comapred with any other full scan query that I
>suspect the former is in fact the true state.
>just a simple test of a very large table will prove the point to your self.
>SELECT FIRST 10 SKIP 1000000 from table order by PK is very fast an does not
>indicate that the server is getting bogged down on running thru a million
>records before starting to return the 10 records
>I stand corrected of course, but I'm very happy with FIRST performance.
experiences with a single table ordered on the primary key doesn't
come close to this question for relevance.
SELECT FIRST m doesn't make sense *unless* it's from an ordered
set. The engine can't know which are the first m records of the full
set until it has the full set and then has sorted it. If joins are
involved, then the joins will happen first, since the content of set
won't be known until the join criteria have been satisfied.
SKIP n also implies an ordered set. All the rows have to be there,
in order for it to skip the first n and deliver m rows starting at n
+ 1 in the order.
Provided you use it appropriately (reasonable sets from tables with
good indexes), this syntax doesn't need to be unbearably slow.
Furthermore (though it's another red herring with respect to the
question asked), there is no sensible reason on earth to use SELECT
FIRST syntax in a stored procedure.
./heLen