Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to delete in batches ?


How to delete in batches ?

Author
Message
SQL Kidu
SQL Kidu
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 250
Hi,

I am working on a GPS related issue. There is a table where we keep geographical information of vehicles tracked using GPS devices. This devise will be sending data to the server every second. So we have a huge dump of data in the tables. There is a table for each day. Table structure is :

TabsenPK Int
DateTimestamp BigInt ( We keep in UTC format which is managed by JAVA)
Longitude Decimal(16,9)
Lattitude Decimal(16,9)
Field1 Int
Field2 Int
Field3 Int
Field4 Int ( Field1 to Field4 are used to keep few details )


I need to delete all records from this table where DateTimeStamp is prior to a passed date. This will come to millions of records. What is the best method to delete this records with least performance impact ? How can I implement a batch based removal ?
sroos
sroos
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 240
I do not understand what you mean by batch based. You can issue only one delete statement with the datetimestamp in the WHERE clause and it will be one batch.
If you are worried about the transaction log growth of a huge DELETE statement, you can split it into different smaller transactions by altering the where clause and put it through a loop.
There is no way that you can turn of logging to speed the process up, but you can possibly alter your design (you mention you have a table for each day?) to truncate the whole table and it should be much quicker.
MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
sanujss (5/23/2012)

Hi,

I am working on a GPS related issue. There is a table where we keep geographical information of vehicles tracked using GPS devices. This devise will be sending data to the server every second. So we have a huge dump of data in the tables. There is a table for each day. Table structure is :

TabsenPK Int
DateTimestamp BigInt ( We keep in UTC format which is managed by JAVA)
Longitude Decimal(16,9)
Lattitude Decimal(16,9)
Field1 Int
Field2 Int
Field3 Int
Field4 Int ( Field1 to Field4 are used to keep few details )


I need to delete all records from this table where DateTimeStamp is prior to a passed date. This will come to millions of records. What is the best method to delete this records with least performance impact ? How can I implement a batch based removal ?




you can use the top clause in your delete statement

e.g.

delete TOP (1000) from mytable where x=y

you can also loop that statement with a delay if you like

declare @rc bigint=1
while (@rc>0)
begin
delete top(100) from #x
set @rc=@@ROWCOUNT
waitfor delay '00:00:05'
end

this might help you delete in batches , which would have less of a hit on transaction logs and avoid massive deletes that block users for hours on end

you might have to try different quantities and different delays, but i usually find that there is a tipping point where the number of rows you delete causes the query to run way too long

MVDBA
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
This article may help, http://www.sqlservercentral.com/articles/T-SQL/67898/.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5950 Visits: 8304
I would have (probably clustered) index on your datetimestamp column. then do something like this:

declare @error int, @rowcount int
set @rowcount = 9999
while @rowcount > 0
begin
begin tran
delete top (50000) from gpstable where datetimestamp < 'somedatehere'
select @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0
BEGIN
ROLLBACK TRAN
--report error?
BREAK --get out of loop
END

COMMIT TRAN

--delay maybe to keep query from hammering system?
waitfor delay '00:00:01'
end

the DELETE should be validated to make index seeks and row or page locks, which will avoid locking up the table for concurrent reads/inserts. You may need to adjust the 50000 number to ensure get the right plan - although with a clustered index I think you will be guaranteed to get a seek regardless of the number

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
SQL Kidu
SQL Kidu
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 250
Thanks to every one for the quick help. This works fine for a table. One more help I need.

I have a partitioned view in which I have all these tables joined using union.
If I try to delete using TOP , it will not allow.
Also, I tried setting the rowcount to 10000 and run the delete. But it is ignoring the rowcount limit as it is a view.
What is the alternative here ?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
I would identify the tables that you need to delete records frm and delete them from the tables directly.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
Lynn Pettis (5/24/2012)
I would identify the tables that you need to delete records frm and delete them from the tables directly.


+100

MVDBA
SQL Kidu
SQL Kidu
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 250
Hi.. Thanks every one ... I have resolved this.

I have dynamically created procedures for each table which meets the criteria, rather than running dynamic SQL to delete the data. Then executed the proc and dropped it. It worked well .

Thanks all bros in sql server central.. They are always there for help.. Kudos bros..
Thomas Stringer
Thomas Stringer
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 1074
Just in case you (or anybody else) would like to read yet another post on the topic, I've written about this.



Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search