fhtapia at gmail.com
Mon Jan 30 16:33:58 CST 2006
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...
More information about the dba-SQLServer mailing list