Unique Index gets slower and slower and slower for inserts

Unique Index gets slower and slower and slower for inserts

Post by Steve Pritcha » Thu, 29 Nov 2001 17:44:00



Hi,

If I have a table with a unique index/constraint on a GUID column (which,
includes using it as a primary key), then inserts to the table get progressively
slower and slower as more records are added.

However, if I specify that the index is non-unique, then this slowing effect is
either not noticeable, or not present at all.

Is it normal for the slowing, and is there anything I can do about it, other
than not have the unique constraint on the index?

Cheers,

Steve.


Remove the 'NOSPAM.' to send me e-Mail

 
 
 

Unique Index gets slower and slower and slower for inserts

Post by Andy Reill » Thu, 29 Nov 2001 18:07:05


It depends on the structure of the table and the clustered index on that
table - If you are entering records into the table that will be inserted
into the middle of the table the rest of the table needs to be reorder and
therefore will get slower as the table fills up - Maybe look at using an
alternative clustered index


> Hi,

> If I have a table with a unique index/constraint on a GUID column (which,
> includes using it as a primary key), then inserts to the table get
progressively
> slower and slower as more records are added.

> However, if I specify that the index is non-unique, then this slowing
effect is
> either not noticeable, or not present at all.

> Is it normal for the slowing, and is there anything I can do about it,
other
> than not have the unique constraint on the index?

> Cheers,

> Steve.


> Remove the 'NOSPAM.' to send me e-Mail


 
 
 

Unique Index gets slower and slower and slower for inserts

Post by Steve Pritcha » Thu, 29 Nov 2001 19:31:00


Thanks for the response.

The problem is that this behaviour seems to be happening even when the index
defining the uniqueness is not clustered.

Cheers,

Steve.


Remove the 'NOSPAM.' to send me e-Mail



> Article: 213494 of microsoft.public.sqlserver.programming

> It depends on the structure of the table and the clustered index on that
> table - If you are entering records into the table that will be inserted
> into the middle of the table the rest of the table needs to be reorder and
> therefore will get slower as the table fills up - Maybe look at using an
> alternative clustered index


> > Hi,

> > If I have a table with a unique index/constraint on a GUID column (which,
> > includes using it as a primary key), then inserts to the table get
> progressively
> > slower and slower as more records are added.

> > However, if I specify that the index is non-unique, then this slowing
> effect is
> > either not noticeable, or not present at all.

> > Is it normal for the slowing, and is there anything I can do about it,
> other
> > than not have the unique constraint on the index?

> > Cheers,

> > Steve.


> > Remove the 'NOSPAM.' to send me e-Mail

 
 
 

Unique Index gets slower and slower and slower for inserts

Post by Andy Reill » Thu, 29 Nov 2001 19:35:35


Have you got an example table with Indexes so We can have a look


> Thanks for the response.

> The problem is that this behaviour seems to be happening even when the
index
> defining the uniqueness is not clustered.

> Cheers,

> Steve.


> Remove the 'NOSPAM.' to send me e-Mail





> > Article: 213494 of microsoft.public.sqlserver.programming

> > It depends on the structure of the table and the clustered index on that
> > table - If you are entering records into the table that will be inserted
> > into the middle of the table the rest of the table needs to be reorder
and
> > therefore will get slower as the table fills up - Maybe look at using an
> > alternative clustered index

in
> > message


> > > Hi,

> > > If I have a table with a unique index/constraint on a GUID column
(which,
> > > includes using it as a primary key), then inserts to the table get
> > progressively
> > > slower and slower as more records are added.

> > > However, if I specify that the index is non-unique, then this slowing
> > effect is
> > > either not noticeable, or not present at all.

> > > Is it normal for the slowing, and is there anything I can do about it,
> > other
> > > than not have the unique constraint on the index?

> > > Cheers,

> > > Steve.


> > > Remove the 'NOSPAM.' to send me e-Mail

 
 
 

1. DB Getting Slower and Slower and Slower....

Hi,
        I recently upgraded to V7.1 version of Postgres on Linux and have noticed
that as the DB is being used, it gets progressively slower and slower, until
very simple queries slow down to a crawl. The basic setup I have is a set of
JSPs and Servlets running on Tomcat, that use Java classes which connect to
Postgres through a JDBC driver and perform various DB operations. But, as I
said, the DB gets noticeably slower, as if it does not clean up behind
itself after performing operations and finally runs out of memory... Then,
the only thing that works is stopping the postmaster and rebooting the
machine.

        If you have any ideas of what may cause this or what I can do to explore
the issue, I would be very grateful. I am fairly new to Postgres, so any
suggestions are welcome.

Thanks,
Othman

_____________________________
Othman Laraki
Epitrope Corporation
860 Hillview Court, Suite 200
Milpitas, CA 95035, USA
650-814-9580 (phone)
650-745-2449 (fax)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

2. vfp illegal operations when adding indexes to tables of >3000 records

3. Insert into SQL Server slow, slow, slow...

4. Help requested

5. Slow slow slow from an ASP

6. Looking for web hosting server that works with Visual Foxpro?

7. Slow, slow, slow search on VB6, ADO and SS7

8. Oracle Datatypes: RAW x BLOB, FLOAT x NUMBER

9. Why does my BCP Load run slower, and slower, and slower

10. PDOXWIN5 Slow (slower than slow)

11. Slow, slow, slow DDEs

12. Query getting slower and slower...

13. O8 getting slower and slower