InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 14:48:05 GMT20http://www.sqlservercentral.com/Forums/Topic1358427-8-1.aspx[quote][b]Luhar (9/14/2012)[/b][hr]we have 4 million records in our test dbHow to delete them batch wise....Can you gimme a hint[/quote]Here's a useful script which Lynn Pettis prepared earlier: [url=http://www.sqlservercentral.com/Forums/FindPost1357526.aspx]http://www.sqlservercentral.com/Forums/FindPost1357526.aspx[/url]You have 4 million rows in your test db which classifies it as "very small", but of those, how many rows do you currently delete?Fri, 14 Sep 2012 04:13:55 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1358427-8-1.aspxwe have 4 million records in our test dbHow to delete them batch wise....Can you gimme a hintFri, 14 Sep 2012 03:41:00 GMTLuharhttp://www.sqlservercentral.com/Forums/Topic1358427-8-1.aspx[quote][b]Luhar (9/13/2012)[/b][hr]I am not running it in transaction.No one will access the DB when this procedure runs as it runs nightly.I just want to modify the procedure so as to run in it batches...for ex processing 10000 records at a time[/quote]How many rows are deleted, typically? -- Recorded[code="sql"]--Retrieve the count of records to be deleted SELECT @expected = COUNT(TRANS_ID) FROM TRANSWHERE SCHEDULED = 1 AND PRACTICE_MODE = @boolean1[/code]-- Used[code="sql"]DELETE FROM TRANS_ASSOCIATE WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANS WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)[/code]Note that what you record and what you actually delete may be different.As Tom stated, you may find some improvement by using a #temp table to hold the keys from table TRANS, as follows:[code="sql"]SET @totalExpected = @expected+@expected2IF OBJECT_ID('tempdb..#Trans') IS NOT NULL DROP TABLE #Trans;SELECT TRANS_IDINTO #Trans FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1GROUP BY TRANS_IDORDER BY TRANS_ID;CREATE UNIQUE CLUSTERED INDEX UCX_TRANS_ID ON #Trans (TRANS_ID);IF (@totalExpected > 0) BEGINDELETE FROM TRANS_ASSOCIATE WHERE TRANS_ID IN (SELECT TRANS_ID FROM #Trans)--If there was an error then return right awayIF (@@ERROR <> 0) RETURN DELETE FROM TRANS_LOCATION WHERE TRANS_ID IN (SELECT TRANS_ID FROM #Trans)--If there was an error then return right awayIF (@@ERROR <> 0) RETURNDELETE FROM TRANS_REBATE_XREF WHERE TRANS_ID IN (SELECT TRANS_ID FROM #Trans) --If there was an error then return right awayIF (@@ERROR <> 0) RETURN[/code]Fri, 14 Sep 2012 03:12:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1358427-8-1.aspxI am not running it in transaction.No one will access the DB when this procedure runs as it runs nightly.I just want to modify the procedure so as to run in it batches...for ex processing 10000 records at a timeThu, 13 Sep 2012 23:35:50 GMTLuharhttp://www.sqlservercentral.com/Forums/Topic1358427-8-1.aspx1) Are you running the stored proc in a transaction, as you mentioned you want to be able to rollback. If you do SQL Server will be very slow deleting a large number of records, and will keep locking more and more of the tables as it deletes. There are some strategies for getting around this (without running large deletes in a transaction), such as using an IsDeleted column, moving records to a Deleted Table - but if it goes wrong, you need code to recover, as obviously you won't have rollback as an option.2) are there any triggers on any of the tables you delete from? these could also slow down your code.3) look for some optimisations. It looks like your using WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1) in a number of places. Perhaps select those TRANS_IDs into a #Tmp table, index it on Trans_ID and join the #tmp to the table you're deleting from instead of accessing the TRANS table for every delete.4) are users accessing the tables at the time you run the delete, or do you run it in a maintenance time?Thu, 13 Sep 2012 14:42:21 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic1358427-8-1.aspxHi AllI have DB with 4 million records and a stored procedure which clears all the data from bunch of tables which are related with a primary key of Table T1 in 4 hrsThe delete procedure which I have is deleting all the records successfully in 4 hrs but it is utilizing huge amount of space.I just want to modify my stored procedure in order to perform deletion batch wise so that even if i kill the it will only rollback the changes to last commit.I tried in different ways but it is taking more than 4 hrs.Any ideas or suggestions are welcome.CREATE procedure TRANS_DELETE @startDate1 datetime, @endDate1 datetime, @boolean1 numeric(1) as--Variables to keep track of record countsDECLARE @processed numeric(20)DECLARE @expected numeric(20)DECLARE @expected2 numeric(20)DECLARE @totalExpected numeric(20)DECLARE @notProcessed numeric(20)--Initialize the record count variablesSET @processed = 0SET @expected = 0SET @expected2 = 0SET @TotalExpected = 0SET @notProcessed = 0--Retrieve the count of records to be deleted SELECT @expected = COUNT(TRANS_ID) FROM TRANSWHERE SCHEDULED = 1 AND PRACTICE_MODE = @boolean1--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDSELECT @expected2 = COUNT(SECURITY_OVERRIDE_LOG_ID) FROM SECURITY_OVERRIDE_LOGWHERE (TRANSACTION_NUMBER = '') AND (START_DATETIME >= @startDate1 AND START_DATETIME <= @endDate1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDSET @totalExpected = @expected+@expected2IF (@totalExpected > 0) BEGINDELETE FROM TRANS_ASSOCIATE WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_LOCATION WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_REBATE_XREF WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_REBATE WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_DISCOUNT_SPREAD WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_DISCOUNT WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_LINE_DISC WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_LINE_PRICE WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_LINE_TAX WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_LINE_ASSOCIATE WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS_CUSTOMER WHERE TRANS_ID IN (SELECT TRANS_ID FROM TRANSWHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDDELETE FROM TRANS WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1--If there was an error then return right awayIF (@@ERROR <> 0)BEGINRETURNENDSET @processed = @totalExpected - @notProcessedSELECT @processed AS PROCESSED, @totalExpected AS EXPECTEDThu, 13 Sep 2012 03:26:35 GMTLuhar