> In our triggers and stored procedures we use
>
> select count(*)
> from table
> where value = Primary Key.value
>
> to determine whether we should run an insert or an update
> query on triggered tables.
>
> Is there a faster way?
>
> I am half way through Helen's book "The firebird book"
>
> She suggests never using select count(*) but I do not know a
> faster way of doing this.

Yes. IN THIS CASE it is not terribly bad because there is a primary
key index which will assist with flagging which data pages contain the
records of interest. Being a primary key, there is a unique constraint
so count is only ever going to return 0 or 1, but you are misusing the
count operator.

There are two ways to do this without the count hack.

1) Run the update, and if no rows were effected, then run the insert.

update tablea
set blah = :somevalue
where ID = new.id;

IF (ROW_COUNT = 0) THEN
BEGIN
insert into tablea (ID, blah) .....
END

Simple and efficient, and for what you want this is the way to go.
Note that this is conceptually the equivalent to the SQL2003 MERGE
keyword (aka UPSERT) for which there is an enhancement request.

2) Check using exists. Exists returns true immediately the first
record is returned. Since in many cases, you have no interest whether
1 or 5 million records are returned, this will be significantly faster
when multiple records can potentially meet the where criteria.

IF (EXISTS(select * from tablea where ID = NEW.ID)) THEN
BEGIN

END
ELSE
BEGIN

END

Again, simple and fast. I use this method if I only want to take an
action if the record does exist, or if I only want to take an action
if the record does not exist. If I want both cases to take an action,
I use the first method.

Count is an implicitly slow operation, because every record that meets
your where condition must be checked to see whether your transaction
should include it (ie can see it) or not. The index includes records
that have been inserted but not yet committed (in other transactions),
records that have been deleted that your transaction no longer cares
about etc. It is not simply a case of counting the number of index
nodes as you go.

Adam