At 09:43 AM 8/02/2007, you wrote:

>What is the best and least costly way to check for the existence of at
>least one record in the result set of a SELECT?
>
>I just want to know if or if not a query will return anything. I don't
>care about the contents. My query involves a NATURAL scan and I want
>to stop the search engine as early as possible.
>
>Is a SELECT FIRST 1 the solution? Or is there something else which is
>better?

If the searched record is near the start of the table there won't be
much difference. SELECT FIRST was not designed for use as an
existence check but for output. Its purpose is to siphon off a set
number of records from an ordered set. For an existence test you
don't care which record is found first, you care only whether the
record exists or not. So you don't want the overhead of having
output structures created: you only want to search and read.

Use
exists (select 1 from atable where...)

and the result will return true as soon as a matching record is
read. However, the speed of return in an existence test, like any
other operation that performs a search, is influenced by how easy it
is to find the matching record. Make sure you design your tables
with good indexes on columns you know will be searched; maintain
your indexes regularly; and write search clauses that can use them!

Looking for non-matches (NOT, <>, etc.), LIKE matches and (pre-Fb 2)
expression matches isn't propitious for performance.

(In Fb 2 you can actually create an expression index; with older
Firebirds, if you know you're often going to do a case-insensitive
search on a varchar that users enter freely themselves (their name or
address, etc.) poke in an indexed search column and write a trigger
that uppercases the entered value, or a substring of it, at
insert/update time, and use that for your searches instead.)

./heLen