websss
asked on
Delete large number of records without locking table
I have a sql agent task which deletes anything older than 6 months
However its locking the table, and everything is breaking until it completes the delete
Is there a way to delete with no lock or any other way?
my query is something like (its a simple delete right now, but i need to add the section where it also leaves the row which is referenced by Devices.LastRecordID)
I was considering writing to a temp table all the ID's and then calling the delete on these ID's however, I thought I would ask first
The current DB size is 100gb, and I would expect the initial run to get rid of 50gb of data, and the app still needs to run while its deleting
However its locking the table, and everything is breaking until it completes the delete
Is there a way to delete with no lock or any other way?
my query is something like (its a simple delete right now, but i need to add the section where it also leaves the row which is referenced by Devices.LastRecordID)
delete from tblTrackingData c
Where GpsDateTime < (now - 6 months)
and c.id not in (select lastRecordId From Devices d where c.id != d.lastRecordId)
I was considering writing to a temp table all the ID's and then calling the delete on these ID's however, I thought I would ask first
The current DB size is 100gb, and I would expect the initial run to get rid of 50gb of data, and the app still needs to run while its deleting
ASKER
ok thanks, can you explain how this handles the locking issue?
Locking issue:
The first row:
The first row:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on
Last row: SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off
ASKER
ok thanks, does this isolation level apply to the whole db or just this running query?
Supposed to use it in you query
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on
DECLARE @continue INT
DECLARE @rowcount INT
SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 10000
BEGIN TRANSACTION
DELETE from tblTrackingData c
Where GpsDateTime < (now - 6 months)
and c.id not in (select lastRecordId From Devices d where c.id != d.lastRecordId)
SET @rowcount = @@rowcount
COMMIT
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One good way to avoid that the necessary locks will affect all table, go for an strategy of table partitioning. If you partition your table by date, then you just need to delete the partitions that you don't need anymore. In your case, the last 6 months.
ASKER
thanks all
The DB is always on, we have people all around the world using it, so there is no "overnight" for us.
Table partitioning does look interesting but that might be more of a long game as we would need time to implement this and i need something quicker for an interim solution
so as i've understood, the sql code should first get a list of ID's (top 10000)
Then put in a temp table
Then delete these ID's, and pause for 5 seconds before running the query again
?
The DB is always on, we have people all around the world using it, so there is no "overnight" for us.
Table partitioning does look interesting but that might be more of a long game as we would need time to implement this and i need something quicker for an interim solution
so as i've understood, the sql code should first get a list of ID's (top 10000)
Then put in a temp table
Then delete these ID's, and pause for 5 seconds before running the query again
?
The DELETE TOP 10000 approach looks better.
ASKER
ok thanks
I've hit another snag, the sub query which checks LATESTRECORD is very heavy, without its a lot quicker
Is there a more efficient way to do this:
line in question is
I've hit another snag, the sub query which checks LATESTRECORD is very heavy, without its a lot quicker
Is there a more efficient way to do this:
DECLARE @continue INT
DECLARE @rowcount INT
----Create A Temporary Table to hold IDS to Delete
IF OBJECT_ID('tempdb.dbo.#DataToDeleteTable', 'U') IS NOT NULL
DROP TABLE #DataToDeleteTable;
Create Table #DataToDeleteTable ( RowID int IDENTITY(1, 1), ifkCommanTrackingID int, dGPSDateTime datetime)
-----Insert IDs to delete to the Temporary Table (Data older than 12months)
insert into #DataToDeleteTable(ifkCommanTrackingID, dGPSDateTime)
select ipkCommanTrackingID, dGPSDateTime from tblCommonTrackingData
Where dGpsDateTime < DATEADD(month, -12, GETDATE())
and ipkCommanTrackingID not in (select LastRecord From wlt_tblDevices where ipkCommanTrackingID != LastRecord)
order by dGPSDateTime asc
select * from #DataToDeleteTable
go
DROP TABLE #DataToDeleteTable
go
line in question is
and ipkCommanTrackingID not in (select LastRecord From wlt_tblDevices where ipkCommanTrackingID != LastRecord)
I changed the sub-query to a LEFT OUTER JOIN. Can you please see if it helps?
insert into #DataToDeleteTable(ifkCommanTrackingID, dGPSDateTime)
select ctd.ipkCommanTrackingID, ctd.dGPSDateTime
from tblCommonTrackingData AS ctd
LEFT OUTER JOIN wlt_tblDevices td ON ctd.ipkCommanTrackingID = td.LastRecord
Where ctd.dGpsDateTime < DATEADD(month, -12, GETDATE())
--and ctd.ipkCommanTrackingID not in (select LastRecord From wlt_tblDevices where ipkCommanTrackingID != LastRecord)
AND ctd.ipkCommanTrackingID IS NOT NULL
AND td.LastRecord IS NULL
order by ctd.dGPSDateTime asc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window