Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Waht is better: Insert and update or Select Update else insert
assuming that you don't know whether you need to perform an update, or an insert, and assuming that the number of rows to be inserted is the same as the number to be updated, and assuming that you are inserting/updating one table, and assuming that the table has an enabled "primary key" constraint...
i don't see that the order of the operations is going to make much of a difference. what IS going to make a difference in performance is the number of "round trips" between the application and the database.
one way to reduce the number of "round trips" would be to write a PL/SQL procedure to perform the "insert or update" operation, something like this:
PROCEDURE insert_or_update_mytable
(arg_pk1 IN VARCHAR2 ,arg_col2 IN VARCHAR2 ,arg_coln IN VARCHAR2
UPDATE mytable SET my_col2 = arg_col2 , my_coln = arg_coln WHERE my_pk1 = arg_pk1;
the procedure could be modified (if you really need) to include a "select ... for update nowait".
if you need to know whether the row was inserted or updated, then the procedure could include an OUT argument, which could be set in the PL/SQL block to indicate whether the row was inserted or updated.
if the application is running from a remote client, and you are processing a lot of rows (as a batch procedure, for example), you may be able to achieve a significant boost in performance by passing arrays from your application (and modifying the procedure arguments to accept the arrays as PL/SQL tables.)
"Nicolas Bronke" <newsgroup_at_trinity.de> wrote in message news:8u3s82$vss$00$1_at_news.t-online.com...
> Hi,
>
> what is the better (performance) if I want to make many updates and inserts:
>
> 1. INSERT and if fails then update
> or
> 2. Select for Update and if records found update else insert
>
> The second is better in my opinion, because if I using also the nowait
> clause I get a checked whether the record is also be locked.
>
> But what is better in performance on condition that insert and updates has
> nearly the same quantity.
>
> Thanks in advance
>
> regards
> Nicolas
>
>
>
Received on Sun Nov 05 2000 - 16:06:49 CST