Re: does unique constraints hurt insert performance

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 13 Aug 2006 11:30:03 +0200
Message-ID: <ebmreq$4fp$00$1@news.t-online.com>

Mladen Gogala schrieb:
> On Sat, 12 Aug 2006 16:52:22 -0700, sunh11373 wrote:
>

>> 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?
>> Assume the inserting rate is 500/sec and the possible duplication is
>> 0.001%.
>>
>> Thanks

>
> I have an outrageous suggestion: create a copy of the production database,
> measure time needed for the insertion of 100,000 rows without the index,
> then re-create the database copy, create the index, re-insert 100,000 rows
> and measure the new time.
>

I suggest, this is the only one possible correct solution. The problem is, you may calculate the overhead for index maintenance in term of additional IO's, but if your IO subsystem is not well tuned for random access, then you may have massive performance degradation in terms of time. So, only tests on your particular hardware can be a proof (IMO). Best regards

Maxim Received on Sun Aug 13 2006 - 04:30:03 CDT