Post reply

Dropping a PrimaryKey takes forever..

  • July 24, 2003 at 5:13 pm

    #59756

    I'm doing a large data load into a large table. I'm experimenting with dropping all indexes, including the PK, before the load and then adding them back in later.

    Each index takes about 1 second to drop with the exception of the PK. It took 20 minutes.

    Any idea why that would be, or what I can do to prevent it?

    (Note: there are no jobs blocking or locking that I can see..)

    thanks

    Bruce

  • July 24, 2003 at 5:42 pm

    #466944

    Pkey clustered? Dropping PK and/or cluster first or last?

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • July 24, 2003 at 6:08 pm

    #466953

    Good question

    BOL 2000:

    On a clustered index, the DROP INDEX statement must rebuild all nonclustered indexes. SQL Server must also replace the clustered index keys in the nonclustered leaf rows with row pointers.

  • July 24, 2003 at 7:00 pm

    #466954

    aha!

    Yes the PK is clustered..and on investigation I don't think it's a good candidate for it. I'll consider the non clustered approach for this key.

    Also, it was the first of all the index drops, so I've changed my code to drop all others first then lastly, the PK.

    Thanks for the advice..

    I'll post results later..

    Bruce

  • July 24, 2003 at 10:02 pm

    #466960

    changing from clustered to non-clustered and moving the PK to the last drop really helped performance..

    from 20 mins to 1 min.

    thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply