Re: does unique constraints hurt insert performance

"sunh11373" <sunh11373_at_gmail.com> wrote in message news:1155446530.659077.134280_at_m73g2000cwd.googlegroups.com...
> Hi,
>
> Unfortunately in my current dev environment, I can only have about 50M
> rows. We are still in design phase. I just want to see whether using
> "unique constraint" to keep data integrity for large database table
> with high insert rate is a good design idea or not . Our initial test
> with 30M does not show any problem. But would like to see anyone have a
> larger database ever experienced some performance problem using this
> approach.
>
> "Big" means more than 100% performance hit, e.g. from 10ms (w/o unique
> constraint) -> > 20ms (w/ unique constraint).
>
> Duplicate data is not allowed. If dected, the insert should fail. If
> the "unique constrain" approach does not work, then the caller has to
> ensure the uniqueness before calling the database.
>
>
> Thanks
>
>
>
> Ana C. Dent wrote:
> > sunh11373_at_gmail.com wrote in news:1155426742.867263.327670
> > @p79g2000cwp.googlegroups.com:
> >
> > > Hi,
> > >
> > > If I have a table which has 1 billion rows (data in each row is
> > > relatively small), delcare one column (not the primary key column) as
> > > unique, will this cause big problem for inserting?
> >
> > Quantify "big".
> >
> > > Assume the inserting rate is 500/sec and the possible duplication is
> > > 0.001%.
> >
> > How much will it hurt the application if duplicate records are allowed
to
> > exists.
> >
> > You can't get something for nothing.
>

If you try to duplicate the unique check constraint in the application it will be slower and less reliable than in the database. Even if you figure out an efficient way to check the constraint on the application end you have two problems:
1. You have to be slower than the db because you have to issue a call to the db and get the results over the wire before doing the insert. If the db does the check then it doesn't have to go over the wire. 2. Transaction boundaries. From the application you can't be sure you won't allow non-unique values. Someone else could be inserting a value and not have committed yet and you wouldn't see their change. You could then add the same value as them. (and since you are "protecting" the db you just screwed up your data integrity.)
3. With issue #2 Oracle can do a dirty read and prevent a duplicate constraint if the constraint is in the db.

Don't try and put the constraint in the application. You will have all sorts of problems and it will be slower than in the db. Jim Received on Sun Aug 13 2006 - 11:23:37 CDT