There is no order by in my query. I'll try the SELECT 1 FROM
RDB&DATABASE with an EXISTS subquery. As always, thanks for the help.

R.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Monday, June 09, 2003 7:02 PM
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] SELECT first


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



Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.