Better way to do a large delete?

I have to do a delete from a table with about 25 million rows. I need to
delete about 4 million rows, based on the date in another table
Table Session
SessionID SessionDate
1 1/25/2004
2 7/25/2001
I have created a view (SessionView) containing all sessionid's where the
sessiondate less than 3 years old.
(Create view SessionView As Select SessionID From Session Where
Session.sessiondate >= getdate() - 1095)
Table Claims
ClaimID SessionID etc etc
etc
24578 1 15
HealthPractice 5554441234
6548975 2 52 ChirocPract
5551234567
What I need is to delete all rows from Table Claims that have sessionids in
SessionView.
Delete From Claims Where Claims.sessionID In (Select SessionID from
SessionView)
-In this case ClaimID 6548975 would be deleted but ClaimID 24578 would not.
But I must be doing something wrong (I am very new at SQL Server, and have
never done a large scale delete that wasn't just straight forward), because
the delete seems to take forever. Last time I had to stop it at just over 5
hours because the network admin needed to do some work on the server and
reboot it.
I have set the recovery to simple, and the server has no other active
databases or other applications running on it.
Any assistance appreciated.
Nancy Lytle
N_Lytle@.terplaum.umd.eduyou could also copy the rows you want to keep to another table, drop
the orginal one, and rename the copy|||nancy,
try deleting in smaller chunks, say 1000 rows each. this should help.
dean
"Nancy Lytle" <word_diva@.hotmail.com> wrote in message
news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
> I have to do a delete from a table with about 25 million rows. I need to
> delete about 4 million rows, based on the date in another table
> Table Session
> SessionID SessionDate
> 1 1/25/2004
> 2 7/25/2001
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
> Table Claims
> ClaimID SessionID etc etc etc
> 24578 1 15 HealthPractice
> 5554441234
> 6548975 2 52
> ChirocPract 5551234567
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and have
> never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on the
> server and reboot it.
> I have set the recovery to simple, and the server has no other active
> databases or other applications running on it.
> Any assistance appreciated.
> Nancy Lytle
> N_Lytle@.terplaum.umd.edu
>|||Another thing could be to drop all indexes, delete your rows, recreate
the indexes.
Its all about how much you can do with the table. Do you have a
maintenance window where no one are using the database or is it always
online?|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:580414
Nancy Lytle (word_diva@.hotmail.com) writes:
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
>...
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and
> have never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on
> the server and reboot it.
Rather than creating a view, insert all the sessionids to delete into
a temp table.
You could also try this syntax:
DELETE Claims
FROM Claims c
JOIN #temp t ON c.session_id = t.session_id
If Claims does not have its clustered index on SessionId, it could be
worth to do
CREATE CLUSTERED INDEX session_id_ix ON Claims(session_id)
WITH DROP_EXISTING
for this task, and then restore the original index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, I was trying to avoid that because I actually have about 10 tables
that I have to handle in a similiar fashion, and copying was also going
slowly.
I think I will end up using Dean's suggesting and chunking it out.
Thanks,
Nancy
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1138312336.363053.109410@.o13g2000cwo.googlegroups.com...
> you could also copy the rows you want to keep to another table, drop
> the orginal one, and rename the copy
>|||I think this is the way I will have to go, so I will just use smaller date
intervals, until I get everything deleted.
Thanks,
Nancy
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:OvslVLsIGHA.2828@.TK2MSFTNGP12.phx.gbl...
> nancy,
> try deleting in smaller chunks, say 1000 rows each. this should help.
> dean
> "Nancy Lytle" <word_diva@.hotmail.com> wrote in message
> news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
>|||When bulk deleting 4 million rows, the slowness you are experiencing
probably has more to do with transaction logging than anything else. I
assume this must a one time purge, but if it is a recurring process, then
you will probably want to reconsider your data model and workflow.
Start by dropping any indexes from the table that would not assist in
qualifying the rows needed for deletion. For example, if you are deleting
based on transaction date, then you would not need to retain an index on
LastName or AccountType. This will reduce the amount of transaction logging
during the process, and besides, all the indexes will need to be
defragmented after the process has completed anyway.
You can try creating a loop and delete the rows based on a date column,
however, this usally results in batches that vary greatly in size. For
example, your company have a lot more transactions in 2005 than in 2004.
Using the technique below, you can delete in batches of exactly 1000 rows.
set rowcount 1000
while
delete from mytable where . . .
if @.@.rowcount = 0 break
checkpoint
end
"Nancy Lytle" <word_diva@.hotmail.com> wrote in message
news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
> I have to do a delete from a table with about 25 million rows. I need to
> delete about 4 million rows, based on the date in another table
> Table Session
> SessionID SessionDate
> 1 1/25/2004
> 2 7/25/2001
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
> Table Claims
> ClaimID SessionID etc etc etc
> 24578 1 15 HealthPractice
> 5554441234
> 6548975 2 52
> ChirocPract 5551234567
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and have
> never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on the
> server and reboot it.
> I have set the recovery to simple, and the server has no other active
> databases or other applications running on it.
> Any assistance appreciated.
> Nancy Lytle
> N_Lytle@.terplaum.umd.edu
>