Subject Re: Insert performance
Author Roman Rokytskyy

> The number of rows affected by an UPDATE statement are commonly
> used to detect update conflicts. Statements like
> UPDATE <table> SET <column> = <newValue> WHERE <column> = <oldValue>
> or
> UPDATE <table> SET <column> = <newValue> WHERE <timestamp> =
> <oldTimestamp>
> are used to detect conflicts. A row count of 0 means that the
> update of the row failed because in the meantime it was updated by
> someone else. This situation doesn't raise any database error and
> can therefore only be detected in this way.

This would only apply to the case when you execute your select and
update calls in different transactions. In this case you are right.
But if you execute your select in the same transaction as update,
then commit of the transaction will fail if both transactions
modified same row. In this case "first commit wins" rule applies.

If you set transaction isolation to "repeatable read", your approach
should not work at all, because SELECT and UPDATE will see the same
value during whole transaction independent of what is actual value
stored in database.

In my opinion, transactions is the only answer to maintain DB in
consistent form. Actually they were invented for exactly this purpose.

> I couldn't understand how exactly the tests were done. I'm mainly
> interested in knowing the difference between InterClient's and
> JayBird's executeUpdate times for INSERT/UPDATE/DELETE statements.
> If a significant (>10%) performance difference is still visible,
> how would you explain that?

Easy: additional network call to the database server. Server part of
InterClient uses local IPC (not network) calls to the database server
(that is why you must run InterServer on the same machine with
database server). When Java part of InterClient calls executeUpdate
(String) on the server, InterServer performs two IPC calls and
returns result over the network. JayBird must perform two network

Best regards,
Roman Rokytskyy