Marvin Gallagher wrote:
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?
Alan McDonald answers:
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.
Helen Borrie adds:
Get ready to stand corrected. A simplistic answer based on your 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.