upsert mode in database

[zulfi123786]

The upsert mode in any database stage has trasaction count which defines how many records are bundled in one upsert operation also in some cases we have the flexibility of defining "update then insert" or "insert then update"

Having said the above I was wondering if a record fails for update in "update then insert" mode how is it processed for insert ?

1. All update failures for a transaction are collected and finally inserted in the same transaction.
2. As and when a record fails for update the insert follows before the second record in the transaction is updated.
3. The update failures from all transaction arrays are collected and updated in one transaction.

Thanks

[MT]

Hi zulfi123786

the second point is right:

...
2. As and when a record fails for update the insert follows before the second record in the transaction is updated.
...

This is why you usually check if you have more updates then inserts and then you use update - insert mode and vice versa.

[zulfi123786]

Was just wondering that it would be more efficient if the update failures would have been collected and inserted in one go rather than each being immediately inserted.

Is there any specific reason which prohibits an efficient approach

[MT]

Hi

well in order to know if you will get an update failure you have to try it - don't you.
The update is then an other operation - it is anyway for all additional rows - so I do not think that any other mode would be more efficient.

For example - let us assume you have 10 rows:
This will give you an optimum of 10 updates and in the worst case 10 failed updates + 10 inserts. But the collection of rows would not be beneficial because you will do it row by row in any way.

[zulfi123786]

What I was thinking is, Let the update occur and in worst case none gets updated but all updates were a part of single transaction and now that we are left with 10 inserts let the inserts run in single transaction rather than performing then one by one (good to have one insert transaction for all failed updates rather than having too many insert transaction one for each record)

[MT]

Hi

ok we have to differentiate between number of statements and number of transactions.
The number of statments can increase by one for every failed let us say update in this example. The number of transaction depends on your settings. If you have set it to one it is increase in the same manner but if it is > 1 it is less.

If your setting is 10 - it will be 10 for updates and inserts - so I can not see the point that each insert will become a new transaction.
Why do you think that?