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