> [HACKERS] What executes faster? 
> Now that I've found the solution for my duplicate key problem, 
> I'm wondering what executes faster when I have to check for 
> duplicates. 
> 1. try to update 
>    if no row affected -> do the insert 
>    else done 
> 2. do a select 
>    if row not found -> do the insert 
>    else do the update 
> Another idea I'm thinking about: 
> I'm doing the check for duplicate key by myself now. 
> Aren't insert commands running faster, if I replace 
> an unique index by a not-unique index. 

I have solved an almost similar problem.
I have a large table (about 8 milion rows) called radius and a table with 
updates and newlines called radiusupdate.
The first thing I tried was 2 queries:
update radius 
  from radiusupdate 
 where radius.pk = radiusupdate.pk

insert into radius 
select * 
  from radiusupdate RU
 where RU.pk not in (select pk from radius)

But the second one is obviously not very fast. A "not in" never is... So I 
now do things just a little bit different. I added a field to the table 
radiusupdate called "newline". It is default set to true. Then I replace 
the second query by these two:

update radiusupdate
   set newline = false
  from radius R
 where radiusupdate.pk = radius.pk

insert into radius
select *
  from radiusupdate RU
 where newline = true

This is a lot faster in my case....

Reinoud


Mon, 1 Oct 2001 11:39:45 +0200 (CEST)
"Reinoud van Leeuwen" <reinoud@xs4all.nl>
Re: What executes faster?
[HACKERS] What executes faster? 
Now that I've found the solution for my duplicate key problem, 
I'm wondering what executes faster when I have to check for 
duplicates. 
 1. try to update 
    if no row affected -> do the insert 
    else done 
 2. do a select 
    if row not found -> do the insert 
    else do the update 
Another idea I'm thinking about: 
I'm doing the check for duplicate key by myself now. 
Aren't insert commands running faster, if I replace 
an unique index by a not-unique index. 

Regards, Christoph 

Mon, 01 Oct 2001 11:24:11 METDST
Haller Christoph <ch@rodos.fzk.de>
What executes faster? 