Deletes

In a heavly indexed table, why does a delete step run forever?
JimBecuase it has to change the data pages and the indexes...

INSERTS should take even longer...

What does the DELETE statement look like?

And what about the DDL for the table...|||Originally posted by Brett Kaiser
Becuase it has to change the data pages and the indexes...

INSERTS should take even longer...

What does the DELETE statement look like?

And what about the DDL for the table...
Thats what i was thinking

the statment is basiacaly
delete tablename
where date = '2003/12/12'
and direction = 'E'

simple enought but there are about 12 indexes on the table. There is also 20 some od columns to the table so its got a bit to go through. Sounds like a delete step isnt the right thing to do...but I dont know of anything else. Rebuilding the table has proven to be faster though, guess im gona go back to that.
Jim|||apparently you have a long table with quite a few records meeting the criteria, because even if there are 12 indexes it should go through delete farely fast if the number of rows affected it significantly less than the total number of rows (<500)|||Originally posted by ms_sql_dba
apparently you have a long table with quite a few records meeting the criteria, because even if there are 12 indexes it should go through delete farely fast if the number of rows affected it significantly less than the total number of rows (<500)
we are trying to delete about 500000 rows out of a 23Million row table
hehe
Jim|||Do any of your indexes have date and/or direction?|||Originally posted by Brett Kaiser
Do any of your indexes have date and/or direction?

Direction yes, no date indexes...coleage of mine was thinking that it would be benificial if we had a date index....
Jim|||Well hell yes...

Do a show estimated execution plan, and tell us what you get...

I'm thinking table scan...

Edit is direction part of a composite of another index, or is it all by it's lonesome?

If it's alone, just add date to that one, and go have a couple of beers...it'll take a while....

better to schedule a script to run overnight...

let us know how long it takes if you do do it...|||An informative an lengthy discourse on indexes

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30090&SearchTerms=index,intersection|||you'd really benefit having date and direction as an index (order depends on selectivity of direction vs. date, the less selective field should be first).|||Make sure you have a clustered index on the table as well as heaps reclaim empty space and deletes on fragmented tables take longer than normal.

dbcc showcontig on the table will tell you how fragemented the table is.

HTH