[dba-SQLServer] Best way to do a large delete

That's a whole lotta deletes :)  setting the database to simple recovery
will aid you in not logging each record.  You may even get some performance
help if you set your view to a WITH(NOLOCK) option.  That way it doesn't
bother to lock any records while reading the view.  I'm going to just guess
that these 25million records are backed up.

one thing you might want to do is JOIN on the VIEW and run you delete in the
following way

Delete
>From Claims
         INNER JOIN SessionView WITH(NOLOCK)
ON Claims.SessionID = SessionView.SessionID

Joins are FASTER than IN where clauses and thus you should yeild much better
performance. (and yes you're deleting from the claims table.)

On 1/26/06, Nancy Lytle <word_diva at hotmail.com> wrote:
>
>
> 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 at terplaum.umd.edu
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.22/239 - Release Date: 1/24/2006
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...