Clustered index on DateTime field causing problems

[jeetendra]

Hi,

I have a table in database which gets filled with around 300000 transactions every day and each transaction has Date time with it. Since our retrieval is based on time interval, we have cluster indexed the Datetime field. Now retrieval seems to be working fast, but when i delets transactions between a time interval SQL server is taking very very long time (About 40 mts to delete a days data i.e 300000 transaction approx).
I am not able to figure out a solution for this since i am new to databases. Could somebody help me out with this problem. Appreciate your help.

Regards
Jeet

[exterminator]

Indexes are only good in case of selecting or better say searching for records. They make the insertions, updations and deletions very in-efficient because in that case, be it clustered or non-clustered index, they also need to be updated. This might be the reason for your getting such a bad performance on the delete and probably insert and update as well. My suggestion would be - while deleting the records (if its done in one hit to the db) - temporarily disable/remove the indexes and then once you are completed with the execution of the delete of the data, enable/add them again. You might want to look into your database documentation about how this done.

Now, depending upon the way of storing (inserting) data into the db, the frequency of inserts/updates, the way removing (deleting) data and the frequency of deletes there could be more similar options of disabling and then later activating the indexes. How is this daily data changing? Do you completely erase off the data or is it that you have some field like "Active" or something and you just turn it off but the data remains in the table? In short, I would like you to expand on the table schema and kind of operations that would be performed on this table on a daily basis. But as far as performance is concerned, the answer lies in the first paragraph of my post. Hope this helps. Regards.