I have a table (MyTable) with the following structure:
dataCol1 and dataCol2 together are the primary key, no other indices are defined on the table.
dataCol1 is a foreign key to another table, dataCol2 is a foreign key to a different table.
There are approx 18000 rows in this table.
The database in total is a little under 1Gb, but SQL Server should be able to cope with that OK. There were also no active connections to the DB when I ran the command.
I am running the command DELETE FROM MyTable WHERE dataCol2 = x. In my case, there should never really be more than 70 being deleted at any one time.
When there are 70 rows to be deleted, this command (run in Query Analyzer) takes approx. 43 seconds to complete. That's almost 0.6 seconds per row!! How can this be, and how can I improve the performance so that it works in a (much) more reasonable time scale?