February 2, 2007 at 6:25 am
Hi All
I am currently running a query against a database table and have noticed that it takes a long time to delete from this table, but taking off the non clustered indexes speeds up the delete.
I am after a query plan that offers a high percentage of Clustered index delete, but what I noticed is that with the other indexes, the plan points to an eager/table spool.
I cannot change the ddl of the columns, i.e varchar to int and index it.
Table: PersonComProfile
Column_Name | DataType |
UniqueReferenceNo | varchar |
ListRef | varchar |
SourceRef | varchar |
PersonURN | varchar |
StartDate | datetime |
Country_ID | int |
DB_ID | int |
Create clustered index index_ UniqueReferenceNo on PersonComProfile (StartDate, UniqueReferenceNo)
Create NonClustered index index_Summary_Info on PersonComProfile (Personurn, Startdate)
Create NonClustered index index_Summary_Info2 on PersonComProfile (Personurn, SourceRef, StartDate)
Typical Delete is as follows:
Delete PersonComProfile
From PersonComProfile
Where startdate between (DateA) and (DateB)
I look forwrd to hearing from you
February 2, 2007 at 7:40 am
Is the cost of dropping the clustered index, before the delete and then recreating it after awards too high?
February 2, 2007 at 7:54 am
You should be able to remove start date from the two non clustered indexes, it's implied since the clustered key is part of the non clustered index.
February 2, 2007 at 8:31 am
Thanks for your reply guys, the cost of rebuilding the indexes is quite high, the table itself contains medical records and contains around 200m rows.
Also, when doing a report that includes group by's, wouldn't the index index index_Summary_Info2 come in handy, as it contains the column in which the group by is being done with.
Thanks in advance
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply