In my quick tests, there seems to be a significant advantage to doing a NOT
EXISTS check before an INSERT if you expect there to be a PK violation, and I
don't understand why.

My test was a simple EXECUTE BLOCK, WHILE/DO over 100 iterations, attempting:

a) straight insert, with WHEN SQLCODE -803 BEGIN END
b) before the insert, do a IF(NOT EXISTS(SELECT * ...))
c) before the insert, do a IF(EXISTS(SELECT * ...))
d) UPDATE OR INSERT

a) very slow (avg. 1.3 s)
b) very fast (avg. 0.015 s)
c) very slow (avg. 1.3 s), so the EXISTS check isn't a significant factor
d) very fast (avg. 0.030 s) (it's significantly faster to perform 100 updates
to a row, than to fail to insert 100 duplicates)

Note that in this loop, I'm always trying to insert the same row (which I know
already exists), so I would expect the index/cache to help out. But even that
still doesn't seem to help the straight INSERT, not even after multiple
attempts to prime the cache. The PK/UNIQUE fields involved are all properly
indexed. It clearly uses these when I use a different method (EXISTS or UPDATE
OR INSERT). So what's with INSERT being so slow? Also, the stats returned to
FlameRobin indicate it "did" 100 inserts, even though they all failed (-803).
(I removed the WHEN SQLCODE just to make sure they were failing properly.) I
would expect that with UPDATE OR INSERT, but not with a failing INSERT.

I'm going to modify the code that caused me to look into this, but I'd like to
know if I'm doing something horribly wrong, or if there's a good logical reason
for this (INSERT can't do a lookup first because $REASON, or failed inserts are
special because of before-insert triggers, or ...) or if it's something being
worked on, or what.

Other info:
SuperServer 2.1.3 Win32
Table being inserted into had 260k rows in it, besides the one I tried to
replace.
Index selectivity for the affected fields: 0.000002 for the PK, 0.000002 for
the three-field UNIQUE (I was attempting insert/update/exists against all 4
fields, no other fields were used anywhere)

-Philip