Michael,

> update or insert into "Transaction File" ("Store ID", "Unique
> ID", "Quantity") values (1,47023,1) matching ("Store ID", "Unique ID")
>
> and
>
> update "Transaction File" set "Quantity"= 1 where "Store ID" =1
> and "Unique ID"=47023
>
> Statement Plan
> --------------
> PLAN (Transaction File INDEX (PK_TRANSACTION_FILE))
>
>
> The first statment took over 200ms to execute the second only 16ms.
> The record was present for both statements. I also executed these
> statments several times with almost the same results ("update or
> insert" being MUCH slower). "Store ID" and "Unique ID" is the primary
> key.

Did you test both statements, separately, from a clean engine start?

I suspect not.

In that case, the performance of the 2nd statement will have benefited
from the cost of loading the appropriate index data pages into the cache
(and in this case, the table schema data pages since the statements
would need to be parsed and processed by the optimizer), which it
reflected in the slower performance of the 1st statement.

In order to test performance of similar statements, you need to run the
statement several times to properly determine the cost.


Sean