At 06:12 PM 9/06/2003 -0700, you wrote:

>Thanks Helen,
>
>As for using SELECT FIRST.... I'm assuming this won't buy me much as it
>will still need to perform the complete query and then grab the first in
>the result set.

I'm not sure what the algorithm would be if your ORDER BY column isn't
indexed, but it has to faster than COUNT if there is an index and a WHERE
clause (after all, it would be pointless without a WHERE clause). But,
because of the extra work in sorting, it has to be more expensive than
EXISTS(). EXISTS() is no good if you want to return the matching record,
though.

>
>Am I wrong in thinking that this isn't much better than...
>
> rs = statement.executeQuery( "SELECT COUNT(*) INTO count FROM Foo WHERE
>Foo.bar = 'bar';" );
> rs.next();
> if ( rs.getInt( 1 ) != 0 )
> one_or_more_exist;

Probably similar to SELECT FIRST. EXISTS() is much cheaper.

>I can't use a Stored Procedure because the query will be dynamically
>built by my object-model.
>
>As for the sub-query, I'm not sure I understand the EXISTS keyword. I
>didn't find much info on it in the Language Reference. But can the outer
>select be a "fake" table, just to dummy a return from the subquery?

Yes. You can, for example, do

SELECT 1 FROM RDB$DATABASE
WHERE EXISTS (SELECT 1 FROM FOO WHERE <whatever>)

>
>
>Am I going through too much effort just to avoid counting records?

In general, select COUNT is more expensive than anything. But, the tighter
the search criteria, the cheaper COUNT gets because, as an aggregate
function, it operates on the output set. If your search criteria pin it
down to a very small range of "possibles", it will be cheaper than SELECT
FIRST with an ORDER BY and virtually the same as SELECT FIRST without an
ORDER BY. But EXISTS() is usually cheaper than everything else, because it
doesn't create an output set and gets the true/false answer directly.

>I
>guess it just seems wastefull to perform a count of everything when all
>I care is whether it exists or not (i.e. stop at the first and don't
>count anymore after that).

COUNT gets cheaper as it gets more concentrated. It's really at its most
useful in a grouped query, because there it's just one of several
operations on aggregated subsets.

COUNT() without a WHERE clause can be a cheap way to query a table's row
count in RDBMSs where the table stores its cardinality in its header. This
"feature" is really only reliable in pessimistic table-locking
systems. It's pretty useless in an optimistic row-level locking system
like Fb/IB, so Fb/IB doesn't do it. That's also the reason why you
shouldn't bet your shirt on the output from COUNT as input to stuff like an
algorithm for incrementing keys or serial numbers...

heLen