Removing rows from very large table with indices

Hello,
I've got a table with a few million rows in it. This table has one clustere
d index and four other indices. When I try to delete records from it on a d
ay-by-day basis (it has a date column), it typically takes between 2 and 5 m
inutes to remove just one d
ay worth. I need to purge about 90 days worth of data ...
Is there a way to shut off index recomputation or whatever it is that takes
so long for the delete to occur? This is an operational data table that rea
lly only needs about 7 days worth of data in it (not 90).
Thanks
-- JakeHi Jake.
How are you performing the delete statement - row by row or by a single
statement supported by a where clause?
It's important to understand that the indexes will actually help the delete
to identify the rows that meet the criteria for deletion, so "shutting" down
the index during deletion would cause the unenviable effect that a table
scan would be required to identify the rows for deletion...
Regards,
Greg Linwood
SQL Server MVP
"javatopia" <anonymous@.discussions.microsoft.com> wrote in message
news:8D297AF5-9D9A-438F-9A76-971F9DF2D0C1@.microsoft.com...
> Hello,
> I've got a table with a few million rows in it. This table has one
clustered index and four other indices. When I try to delete records from
it on a day-by-day basis (it has a date column), it typically takes between
2 and 5 minutes to remove just one day worth. I need to purge about 90 days
worth of data ...
> Is there a way to shut off index recomputation or whatever it is that
takes so long for the delete to occur? This is an operational data table
that really only needs about 7 days worth of data in it (not 90).
> Thanks
> -- Jake
>|||Hi,
I don't recommend you to delete indexes because proper indexes will help you
to locate the records that you want to delete more quickly . (You have to
find the records first before you delete them.)
If you want to delete records in a single transaction (and if you are deleti
ng large amount of data) your log file will grow fast no matter what you're
recovery model is.
I'll prefer to set rowcount and delete records in a while loop until @.@.rowco
unt = 0. This will delete you're records in smaller transactions.
Also selecting the records that you like to keep in a new table and dropping
the old table and renaming the new table is generally less expensive then d
elete. (Depends the percentage of tha data that you want to keep and you wan
t to delete.)
And partitioned tables are very helpfull for archiving and deleting..
Regards..
Umut Nazl?ca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)|||I like the suggestion of selecting INTO a new table, dropping the old table,
and then renaming the new table to the old one. Seems like that could be d
one in an automated manner as well, right? Is it possible to put the DB int
o single-user mode via a st
ored procedure, then do all of this (select into, drop old, rename table)?
Your help is very much appreciated!!
-- Jake|||Hi,
Yes you can put db into single user mode using alter database and terminatio
n options like:
ALTER DATABASE XXX
SET SINGLE_USER WITH ROLLBACK AFTER X (See Books Online ..)
and you can schedule a job to automate it.
My suggestion is first script your table then create your new table using th
is script (at this point do not create the indexes on new table), transfer
data, create indexes on new table and so on.
But test first to see if it works the way you want..
Regards..