Stephane,

> Thanks to my paranoia, i was thinking like everyone that
>
> Update or insert
> OR
> Insert
>
> will be close to the same speed (or very similare)

I don't know that "everyone" had the same expectation.

It is incorrect to assume that all SQL operators/ statements will perform the same. Further, I am not sure why you would think that way.

It seems obvious to me that "INSERT" and "UPDATE ***OR*** INSERT" would have different performance -- they are not equivalent!

> Update or insert is 2x more slower than insert alone :( :( :(
>
> 100000 Update or insert => 0.501 ms average insert time
> 100000 insert => 0.300 ms average insert time
>
> sad but interesting to know !

This was an invalid test.

The correct test would have been to create 2 SPs, one which used UPDATE OR INSERT and one which followed the legacy method:

IF (EXISTS(SELECT 1 FROM Table WHERE Fieldx = X)) THEN
UPDATE Table SET... WHERE Fieldx = X;
ELSE
INSERT INTO Table (...) VALUES (...);

And then compare the performance of the SPs.

I am 100% that you would be very happy with the performance of UPDATE OR INSERT.


Sean