Post reply

Primary Key Clustered & Inserts

  • August 30, 2006 at 10:47 am

    #61322

    Hi All,

     

    I was wondering whether if one had a table with a primary key which is also clustered, if there are a lot of heavy inserts on the table, if this going to cause performance problems ?

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • August 30, 2006 at 11:31 am

    #657651

    Depends of the key.  If it's an identity then no.  If it's another field like a date, then it could slow the inserts but it could also really speed up the selects.

     

    What table and key do you have in mind with this question?

  • August 30, 2006 at 11:58 pm

    #657727

    The keys in question here, one is of a varchar data type which should always be unique, the other is of an int data type and the last one is a datetime data type.

    I also believe that with these keys on, it means that the tables will be heavily fragmented, as these keys above are clustered primary keys.

    what do you think ?

    Kindest Regards,

    John Burchel (Trainee Developer)

  • August 31, 2006 at 3:23 am

    #657753

    It will depend on size of database, size, capacity and power of server, number of reads v number of writes, there is no hard and fast rule.

    I would add to a test environment, simulate transactions viewing the execution plans and time the results, if you can simulate a normal day in test it will give you many of your answers, obviously this might not be possible for you but at least do some basic tests along these lines to get a feel for what is happening. Fragmentation will affect performance, but is there anything stopping you defragmenting regularly

    Regards

    Carolyn

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • August 31, 2006 at 9:23 am

    #657850

    The important thing is whether you will be adding on to the end of the table (ie sequential keys) or inserting into the middle. If the latter, you will be generating lots of page splits which will hit insert performance (and read performance if the page population becomes more sparse as a result).

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

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