SQL Server Performance

PK constraints on horizontal partitioned tables

Discussion in 'Performance Tuning for DBAs' started by SanetteWessels, May 10, 2005.

  1. SanetteWessels New Member

    Hi All,

    Our Sales table has been partitioned. The partitioning is based on a range of InventoryID's. I now have a cleanup procedure that will remove old sales records from these partitioned tables. Should I first drop the PK constraints before removing the records and then add them back in again afterwards? Or should I leave them alone and simply delete the records as needed? I am looking for the fastest way of doing this. There are +/- 14 Million records per partioned table.

    Thanks in advance!

    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend
  2. dineshasanka Moderator

    My view is it is better to delete with the PK. if there are records with Cascaded to theese table then those also will be deleted and it will maintain the data intergrity.

    But If you don't wnat to delete those data then you can drop PK and delete the nesserary data

    quote:
    Our Sales table has been partitioned. The partitioning is based on a range of InventoryID's. I now have a cleanup procedure that will remove old sales records from these partitioned tables. Should I first drop the PK constraints before removing the records and then add them back in again afterwards? Or should I leave them alone and simply delete the records as needed? I am looking for the fastest way of doing this. There are +/- 14 Million records per partioned table.

  3. SanetteWessels New Member

    There will be no cascaded deletes here. I am just worried that if I do drop and recreate the PK it will have an impact on the speed of the procedure. There are +/- 80 partitioned tables with each having around 14 Million records. My procedures loops through every partitioned table and then deletes the records. So with every loop I will have to drop the PK and before exiting the loop I will have to recreate it again.

    Unless there is an easier way???
  4. dineshasanka Moderator

    In that case, I think it is better to execute a delete with out droping PK

Share This Page