> 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
>

Sean,

Thank you for your help. I did some more analysis and this is my
results:

1. Started the engine.
2. Execute:

update "Transaction File" set "Quantity"= 1 where "Store ID" =1
and "Unique ID"=47023

Received this result:
Plan
PLAN (Transaction File INDEX (PK_TRANSACTION_FILE))

Adapted Plan
PLAN (Transaction File INDEX (PK_TRANSACTION_FILE))

1 record(s) was(were) updated in Transaction File

------ Performance info ------
Prepare time = 15ms
Execute time = 16ms
Current memory = 36,046,740
Max memory = 38,650,128
Memory buffers = 2,048
Reads from disk to cache = 2
Writes from cache to disk = 2
Fetches from cache = 19

3. Reset the database engine. Stop and restart the service.
4. Executed this statement:

update or insert into "Transaction File" ("Store ID", "Unique
ID", "Quantity") values (1,47023,1) matching ("Store ID", "Unique ID")

Got these performance results:

1 record(s) was(were) updated in Transaction File

------ Performance info ------
Prepare time = 15ms
Execute time = 265ms
Current memory = 36,120,552
Max memory = 38,650,128
Memory buffers = 2,048
Reads from disk to cache = 2,067
Writes from cache to disk = 2
Fetches from cache = 213,917

Note that the both statements where executed on the same table in the
same database updating the same record. I think somthing is wrong.