Re: does unique constraints hurt insert performance

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.
Received on Sun Aug 13 2006 - 00:22:10 CDT