Where's my Derby?

[Arne Vajhøj]

You mean, if it has them, you remember to use them, and you're happy to
live with the consequences of using the serializable isolation level,
you'll be safe.

Being able to do an 'upsert' in a single atomic operation makes it
possible to be safe much faster than having to do it with two queries.

Why?

The UPSERT will more or less have to do the same thing. The fact
that it is one SQL command does not guarantee that it is more
efficient.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

<quote>
Performance considerations:

Please note that REPLACE INTO is a much slower performer than an UPDATE
statement. Keep in mind that a REPLACE INTO requires a test on the keys,
and if a matching unique key is found on any or all columns, a DELETE
FROM is executed, then an INSERT is executed. There's a lot of
management of rows involved in this, and if you're doing it frequently,
you'll hurt your performance unless you simply cannot do with any other
syntax.

The only time when I can see where you'd actually need a REPLACE INTO is
when you have multiple unique constraints on a table, and need to drop
any rows that would match any of the constraints. Then REPLACE INTO
becomes more efficient from DELETE FROM... INSERT INTO...

If you're looking at a single unique column table (Primary Key), please
use UPDATE, or INSERT. Also, check out INSERT ... ON DUPLIATE KEY
UPDATE... as an alternative if you're willing to stick to MySQL 4.1+
</quote>

does not promise blazing speed.

Arne

 

[Tom Anderson]

Why?

Because you don't have to use serializable transaction isolation.

The UPSERT will more or less have to do the same thing. The fact that it
is one SQL command does not guarantee that it is more efficient.

Perhaps not, but the effect of the transaction isolation level on
concurrency is, AIUI, likely to be significant. Mind you, with RDBMSs
which implement serializable as not-really-serializable on top of MVCC,
like Oracle, the performance hit should be much smaller. It would be
interesting to get numbers on this, of course.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

<quote>
Performance considerations:

Yeah, well, MySQL is MySQL. I wouldn't expect MySQL to behave like a real
database. But anyway:

Also, check out INSERT ... ON DUPLIATE KEY UPDATE... as an
alternative if you're willing to stick to MySQL 4.1+
</quote>

ON DUPLICATE KEY UPDATE is the standard way to do UPSERT on MySQL, and
isn't covered by this dire warning.

tom

 

[Arne Vajhøj]

Because you don't have to use serializable transaction isolation.
>

Perhaps not, but the effect of the transaction isolation level on
concurrency is, AIUI, likely to be significant.

serializable transaction isolation level in this case basicly means a lock.

An UPSERT statement also needs to do a lock.

It is not obvious to me why the first lock is so much more
expensive that the second lock.

Yeah, well, MySQL is MySQL. I wouldn't expect MySQL to behave like a
real database. But anyway:


ON DUPLICATE KEY UPDATE is the standard way to do UPSERT on MySQL, and
isn't covered by this dire warning.

No. But it still has to the work.

Arne

 

[elakkiya]

Hai i have one dout

 

[elakkiya]

Can you help me?

 

[elakkiya]

I create the webservice apllication in .net,now i want to connect it with my wowza server..how can i connect it?