Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WaterWolf
Starting Member
24 Posts |
 Posted - 2009-02-17 : 07:25:09
|
| I have a problem with a database whereby deleting a record from a particular table is very slow. The table does not cause cascade delete onto any other tables however it does appear to cause deletes in the indexes of other tables. When I look at the query (a simple DELETE FROM table WHERE ID = #) in the execution plan, there are lots of Clustered Index Deletes on other tables. These seem to be slow enough to bring the total time of the execution above 30 seconds and so time out the website.Could anyone explain to me what's going on here and is there any way I can speed it up ? |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-17 : 08:14:14
|
| deletes on other tables ? |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-17 : 08:14:55
|
| Does the table have triggers on it ? |
|
|
|
WaterWolf
Starting Member
24 Posts |
Posted - 2009-02-17 : 11:02:50
|
| Okay, I've been misinterpreting what I'm seeing in the execution plan. Still learning ! A 'clustered index delete' is deleting records, not just the index relating to those records !So for one particular table it's doing a clustered index scan, a nested loop, and then a clustered index delete. The clustered index scan is taking quite a long time so I suspect that's not good. I can put an extra index on that part and maybe it will improve. I assume anything with the word 'scan' in it is bad but 'seek' is good ? |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-17 : 11:15:14
|
| In general yes, you want seek over scan. What is the key column (your where clause) in your delete statement and what is your clustered index on? Are they the same column? How many rows in the table and how many are you deleting?Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
Deleting from indexes is very slow.