select + insert vs. insert and catching exception

select + insert vs. insert and catching exception

Post by Christoph Seide » Fri, 27 Sep 2002 21:05:05



what is better in respect of performance?

select by primary key, if not found insert

or

insert and catch exception if primary key exists

the table is quite small, less than 100 rows

 
 
 

select + insert vs. insert and catching exception

Post by Richard Foot » Fri, 27 Sep 2002 21:57:55


Hi Christopher,

I'll go for option two. Avoids having to unnecessarily perform the select
and access the index twice.

I would also recommend the use of a sequence (if appropriate) to eliminate
the potential duplicate key error.

Cheers

Richard

Quote:> what is better in respect of performance?

> select by primary key, if not found insert

> or

> insert and catch exception if primary key exists

> the table is quite small, less than 100 rows


 
 
 

select + insert vs. insert and catching exception

Post by Kevin Gillin » Fri, 27 Sep 2002 23:44:38


wouldn't a sequence actually generate a problem of duplicates when not
wanted.  Say the PK is phone number.  Then, using a sequence as the PK would
require a UNIQUE index on the phone number causing more overhead.  If you
just begin with a solid design of PK/FK relationships then you can let the
database do the work for you.

Kevin


> Hi Christopher,

> I'll go for option two. Avoids having to unnecessarily perform the select
> and access the index twice.

> I would also recommend the use of a sequence (if appropriate) to eliminate
> the potential duplicate key error.

> Cheers

> Richard


> > what is better in respect of performance?

> > select by primary key, if not found insert

> > or

> > insert and catch exception if primary key exists

> > the table is quite small, less than 100 rows

 
 
 

select + insert vs. insert and catching exception

Post by Karsten Farel » Sat, 28 Sep 2002 00:55:02


While conceptually true, you would introduce an application problem if
you make the phone number a PK. In Oracle, you cannot modify the value
of a PK. If someone changes their phone number, the app would be forced
to delete the old and insert the new ... which might cause other
problems with referential integrity. PKs are better left with absolutely
no intelligence built in ... kinda like me. :) Whenever someone thinks
about using smart numbers as keys in your database, it's time to start
asking a few pointed questions.

> wouldn't a sequence actually generate a problem of duplicates when not
> wanted.  Say the PK is phone number.  Then, using a sequence as the PK would
> require a UNIQUE index on the phone number causing more overhead.  If you
> just begin with a solid design of PK/FK relationships then you can let the
> database do the work for you.

> Kevin



>>Hi Christopher,

>>I'll go for option two. Avoids having to unnecessarily perform the select
>>and access the index twice.

>>I would also recommend the use of a sequence (if appropriate) to eliminate
>>the potential duplicate key error.

>>Cheers

>>Richard


>>>what is better in respect of performance?

>>>select by primary key, if not found insert

>>>or

>>>insert and catch exception if primary key exists

>>>the table is quite small, less than 100 rows

 
 
 

select + insert vs. insert and catching exception

Post by Christoph Seide » Sat, 28 Sep 2002 02:41:33



Quote:> PKs are better left with absolutely
> no intelligence built in ... kinda like me. :)

LOL
 
 
 

select + insert vs. insert and catching exception

Post by Jaap W. van Dij » Sat, 28 Sep 2002 02:58:18


On Thu, 26 Sep 2002 15:55:02 GMT, Karsten Farell

(...)

Quote:> In Oracle, you cannot modify the value
>of a PK.

(...)

Proof that! You can't because it is not true.

Jaap.

 
 
 

select + insert vs. insert and catching exception

Post by Jonathan Lewi » Sat, 28 Sep 2002 04:26:38


How often is it going to happen ?
How often is it likely to fail on insert ?

If the task runs rarely, who cares about a
marginal overhead.  If the task runs very
frequently and the select usually succeeds
then do the select first.  If the task runs
very frequently and the insert usually succeeds
(at least 19 times out of 20) then consider
doing the insert first.

Inserting and getting a constraint breached
is much more expensive than doing a single
select which returns no rows.

(Would you be doing an update if the select
succeeds ? If so why not:
    update
    if no rows updated
        insert

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


>what is better in respect of performance?

>select by primary key, if not found insert

>or

>insert and catch exception if primary key exists

>the table is quite small, less than 100 rows

 
 
 

select + insert vs. insert and catching exception

Post by Christoph Seide » Sat, 28 Sep 2002 04:58:28


the routine is called very often and the insert is done very seldom
(10000:1)

what gives you the certainty that the select is better? a load test?


> How often is it going to happen ?
> How often is it likely to fail on insert ?

> If the task runs rarely, who cares about a
> marginal overhead.  If the task runs very
> frequently and the select usually succeeds
> then do the select first.  If the task runs
> very frequently and the insert usually succeeds
> (at least 19 times out of 20) then consider
> doing the insert first.

> Inserting and getting a constraint breached
> is much more expensive than doing a single
> select which returns no rows.

> (Would you be doing an update if the select
> succeeds ? If so why not:
>     update
>     if no rows updated
>         insert

> --
> Regards

> Jonathan Lewis
> http://www.jlcomp.demon.co.uk

> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )

> ____England______September 24/26, November 12/14

> ____USA__________November 7/9 (MI), 19/21 (TX)

> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html


> >what is better in respect of performance?

> >select by primary key, if not found insert

> >or

> >insert and catch exception if primary key exists

> >the table is quite small, less than 100 rows

 
 
 

select + insert vs. insert and catching exception

Post by Jonathan Lewi » Sat, 28 Sep 2002 05:06:25


No.

The fact that when you insert and fail Oracle has
to rollback the attempted insert and process the
constraint failure by calling a select on con$ to
translate the constraint number into the constraint
name so that it can be reported into the error message.

(A volume test then demonstrates the point,
of course - but pure clock tests should always
be backed up by analysis of activity).

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


>the routine is called very often and the insert is done very seldom
>(10000:1)

>what gives you the certainty that the select is better? a load test?



>> How often is it going to happen ?
>> How often is it likely to fail on insert ?

>> If the task runs rarely, who cares about a
>> marginal overhead.  If the task runs very
>> frequently and the select usually succeeds
>> then do the select first.  If the task runs
>> very frequently and the insert usually succeeds
>> (at least 19 times out of 20) then consider
>> doing the insert first.

>> Inserting and getting a constraint breached
>> is much more expensive than doing a single
>> select which returns no rows.

>> (Would you be doing an update if the select
>> succeeds ? If so why not:
>>     update
>>     if no rows updated
>>         insert

>> --
>> Regards

>> Jonathan Lewis
>> http://www.jlcomp.demon.co.uk

>> Next Seminar dates:
>> (see http://www.jlcomp.demon.co.uk/seminar.html )

>> ____England______September 24/26, November 12/14

>> ____USA__________November 7/9 (MI), 19/21 (TX)

>> The Co-operative Oracle Users' FAQ
>> http://www.jlcomp.demon.co.uk/faq/ind_faq.html


>> >what is better in respect of performance?

>> >select by primary key, if not found insert

>> >or

>> >insert and catch exception if primary key exists

>> >the table is quite small, less than 100 rows

 
 
 

select + insert vs. insert and catching exception

Post by Howard J. Roger » Sat, 28 Sep 2002 05:19:07



Quote:> While conceptually true, you would introduce an application problem if
> you make the phone number a PK.
> In Oracle, you cannot modify the value of a PK.

As written, that statement is off the scale of incorrectness. Of course you
can modify a primary key. I do it all the time... as does any update to any
column executed through an Oracle Forms app, usually.

Perhaps you were thinking of foreign keys? Even then the statement is only
half-right: the primary key of the child table in a parent-child
relationship is perfectly updateable.

Of course, updating the primary key of a parent table is more problematic,
because of the dependent child records. But even there, it's possible to do
a 'cascaded update' by judicious use of triggers (or, rather more
drastically, a simple disabling of the foreign key constraint).

There is nothing intrinsically special about a primary key that prevents it
being updateable.

Regards
HJR

>If someone changes their phone number, the app would be forced
> to delete the old and insert the new ... which might cause other
> problems with referential integrity. PKs are better left with absolutely
> no intelligence built in ... kinda like me. :) Whenever someone thinks
> about using smart numbers as keys in your database, it's time to start
> asking a few pointed questions.


> > wouldn't a sequence actually generate a problem of duplicates when not
> > wanted.  Say the PK is phone number.  Then, using a sequence as the PK
would
> > require a UNIQUE index on the phone number causing more overhead.  If
you
> > just begin with a solid design of PK/FK relationships then you can let
the
> > database do the work for you.

> > Kevin



> >>Hi Christopher,

> >>I'll go for option two. Avoids having to unnecessarily perform the
select
> >>and access the index twice.

> >>I would also recommend the use of a sequence (if appropriate) to
eliminate
> >>the potential duplicate key error.

> >>Cheers

> >>Richard


> >>>what is better in respect of performance?

> >>>select by primary key, if not found insert

> >>>or

> >>>insert and catch exception if primary key exists

> >>>the table is quite small, less than 100 rows

 
 
 

select + insert vs. insert and catching exception

Post by Karsten Farel » Sat, 28 Sep 2002 05:45:41


I stand corrected. You *can* modify the value of a PK (must be that lack
of intelligence). However, you'd still face having to change the phone
number in any table where it's a FK if you change it where it's a PK.


> On Thu, 26 Sep 2002 15:55:02 GMT, Karsten Farell

> (...)

>>In Oracle, you cannot modify the value
>>of a PK.

> (...)

> Proof that! You can't because it is not true.

> Jaap.

 
 
 

select + insert vs. insert and catching exception

Post by Karsten Farel » Sat, 28 Sep 2002 06:09:15


Okay, here's what I was thinking about when I wrote that. Perform this
little test:

create table t1 (
   pk number not null,
   constraint pk1 primary key (pk)
);
create table t2 (
   pk number not null,
   fk number
);
alter table t2 add (
   constraint fk2 foreign key (fk) references t1 (pk)
);
insert into t1 values (100);
insert into t2 values (200, 100);
update t1 set pk=999 where pk=100;
ERROR at line 1:
ORA-02292: integrity constraint (owner.FK2) violated - child record found

I admit this is adding a FK parent-child relationship. I should have
been more specific (or is it more generic) in my statement. I apologize
for any misunderstanding. Hey, I'm only human.




>>While conceptually true, you would introduce an application problem if
>>you make the phone number a PK.
>>In Oracle, you cannot modify the value of a PK.

> As written, that statement is off the scale of incorrectness. Of course you
> can modify a primary key. I do it all the time... as does any update to any
> column executed through an Oracle Forms app, usually.

> Perhaps you were thinking of foreign keys? Even then the statement is only
> half-right: the primary key of the child table in a parent-child
> relationship is perfectly updateable.

> Of course, updating the primary key of a parent table is more problematic,
> because of the dependent child records. But even there, it's possible to do
> a 'cascaded update' by judicious use of triggers (or, rather more
> drastically, a simple disabling of the foreign key constraint).

> There is nothing intrinsically special about a primary key that prevents it
> being updateable.

> Regards
> HJR

 
 
 

select + insert vs. insert and catching exception

Post by Howard J. Roger » Sun, 29 Sep 2002 06:46:25



Quote:> Okay, here's what I was thinking about when I wrote that. Perform this
> little test:

> create table t1 (
>    pk number not null,
>    constraint pk1 primary key (pk)
> );
> create table t2 (
>    pk number not null,
>    fk number
> );
> alter table t2 add (
>    constraint fk2 foreign key (fk) references t1 (pk)
> );
> insert into t1 values (100);
> insert into t2 values (200, 100);
> update t1 set pk=999 where pk=100;
> ERROR at line 1:
> ORA-02292: integrity constraint (owner.FK2) violated - child record found

Hi Karsten,

But this is to be expected. Now write a trigger on the parent table that,
before update, locates all child records and updates *them*, and then the
update you're trying to do the parent will succeed. Cascading updates, in
other words.

Regards
HJR

- Show quoted text -

> I admit this is adding a FK parent-child relationship. I should have
> been more specific (or is it more generic) in my statement. I apologize
> for any misunderstanding. Hey, I'm only human.




> >>While conceptually true, you would introduce an application problem if
> >>you make the phone number a PK.
> >>In Oracle, you cannot modify the value of a PK.

> > As written, that statement is off the scale of incorrectness. Of course
you
> > can modify a primary key. I do it all the time... as does any update to
any
> > column executed through an Oracle Forms app, usually.

> > Perhaps you were thinking of foreign keys? Even then the statement is
only
> > half-right: the primary key of the child table in a parent-child
> > relationship is perfectly updateable.

> > Of course, updating the primary key of a parent table is more
problematic,
> > because of the dependent child records. But even there, it's possible to
do
> > a 'cascaded update' by judicious use of triggers (or, rather more
> > drastically, a simple disabling of the foreign key constraint).

> > There is nothing intrinsically special about a primary key that prevents
it
> > being updateable.

> > Regards
> > HJR

 
 
 

select + insert vs. insert and catching exception

Post by ctc.. » Mon, 30 Sep 2002 09:53:22



> what is better in respect of performance?

> select by primary key, if not found insert

> or

> insert and catch exception if primary key exists

> the table is quite small, less than 100 rows

It seems to me the first option necessarily includes the second.

The select followed by a insert if necessary sets up a race condition,
(two transactions do the same select, both come up empty then both try
to do the insert),  thus catching the exception will be necessary anyway.

But I would expect doing all three (select, insert, catch) would be
best for performance.  If the table is < 100 rows, there probably aren't
that many inserts going on.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
                    Usenet Newsgroup Service

 
 
 

select + insert vs. insert and catching exception

Post by Marc Blu » Mon, 30 Sep 2002 17:31:09



>Okay, here's what I was thinking about when I wrote that. Perform this
>little test:

>create table t1 (
>   pk number not null,
>   constraint pk1 primary key (pk)
>);
>create table t2 (
>   pk number not null,
>   fk number
>);
>alter table t2 add (
>   constraint fk2 foreign key (fk) references t1 (pk)
>);
>insert into t1 values (100);
>insert into t2 values (200, 100);
>update t1 set pk=999 where pk=100;
>ERROR at line 1:
>ORA-02292: integrity constraint (owner.FK2) violated - child record found

But you can avoid that one with deferrable constraints. Look:

SQL> DROP TABLE t2;

Tabelle wurde gel?scht.

SQL> DROP TABLE t1;

Tabelle wurde gel?scht.

SQL>
SQL> create table t1 (
  2     pk number not null,
  3     constraint pk1 primary key (pk)
  4  );

Tabelle wurde angelegt.

SQL>
SQL> create table t2 (
  2     pk number not null,
  3     fk number
  4  );

Tabelle wurde angelegt.

**Now add a deferrable FK:**

SQL>
SQL> alter table t2 add (
  2     constraint fk2
  3     foreign key (fk)
  4     references t1 (pk)
  5     DEFERRABLE
  6     INITIALLY DEFERRED
  7  );

Tabelle wurde ge?ndert.

SQL>
SQL> insert into t1 values (100);

1 Zeile wurde erstellt.

SQL> insert into t2 values (200, 100);

1 Zeile wurde erstellt.

SQL> COMMIT;

Transaktion mit COMMIT abgeschlossen.

SQL>

**The following UPDATE doesn't result in an error:**

SQL> update t1 set pk=999 where pk=100;

1 Zeile wurde aktualisiert.

**Now update all childs (of course a trigger could do that):**

SQL> update t2 set fk=999 where fk=100;

1 Zeile wurde aktualisiert.

SQL> COMMIT;

Transaktion mit COMMIT abgeschlossen.

Marc Blum

http://www.marcblum.de