May 28, 2012 at 6:03 pm
How can I make this query run faster? It is currently taking over an hour to delete about 117,000 records. This is running on SQL 2000 server.
DELETE GlobalRecords
FROM GlobalRecords AS GR
LEFT JOIN localRecord AS LR
ON GR.GlobalRecodID = LR.GlobalRecordID
WHERE DATEDIFF(day, GR.ModificationDate, getdate()) > 365 and LR.GlobalRecordID IS NULL
May 28, 2012 at 6:26 pm
Give this a try, it is written for SQL Server 2000:
declare @batchsize int;
set @batchsize = 10000;
set rowcount 10000;
while @batchsize > 0
begin
delete
gr
from
dbo.GlobalRecords gr
left join dbo.localRecord lr
ON gr.GlobalRecodID = lr.GlobalRecordID
where
gr.ModificationDate <= DATEADD(DD, DATEDIFF(dd, 0, getdate()) -365, 0)
and lr.GlobalRecordID is null;
set @batchsize = @@ROWCOUNT;
end
set rowcount 0;
May 28, 2012 at 10:37 pm
Try below option:
create table #TempData
(GlobalRecodID INT)
insert into #TempData (GlobalRecodID)
select GR.GlobalRecodID FROM GlobalRecords AS GR
LEFT JOIN localRecord AS LR
ON GR.GlobalRecodID = LR.GlobalRecordID
WHERE DATEDIFF(day, GR.ModificationDate, getdate()) > 365 and LR.GlobalRecordID IS NULL
delete GlobalRecords
FROM GlobalRecords AS GR
inner join #TempData AS T ON GR.GlobalRecodID = T.GlobalRecodID
drop table #TempData
This is another option that you can use. Please modify as per your needs.
Thanks
May 28, 2012 at 11:59 pm
Hardy21 (5/28/2012)
Try below option:
create table #TempData(GlobalRecodID INT)
insert into #TempData (GlobalRecodID)
select GR.GlobalRecodID FROM GlobalRecords AS GR
LEFT JOIN localRecord AS LR
ON GR.GlobalRecodID = LR.GlobalRecordID
WHERE DATEDIFF(day, GR.ModificationDate, getdate()) > 365 and LR.GlobalRecordID IS NULL
delete GlobalRecords
FROM GlobalRecords AS GR
inner join #TempData AS T ON GR.GlobalRecodID = T.GlobalRecodID
drop table #TempData
This is another option that you can use. Please modify as per your needs.
Your code still has one of the same problems as the original code from the OP, the WHERE clause is not SARGable. The way it is currently written SQL Server needs to compute the difference between gr.ModificationDate and getdate() for all rows in the table to determine if the difference is greater than or equal to 365.
If there is an index on gr.ModificationDate, it won't be used.
Change this:
WHERE DATEDIFF(day, GR.ModificationDate, getdate()) >= 365 and LR.GlobalRecordID IS NULL
To this:
WHERE
gr.ModificationDate <= DATEADD(dd, datediff(dd, 0, getdate()) - 365, 0)
and LR.GlobalRecordID IS NULL
May 29, 2012 at 12:46 am
Lynn Pettis - you are right.
I have just given one option to change the query.
It will calculate and store filtered records in temp table so during delete, it will directly use the ID value in WHERE condition.
Thanks
May 29, 2012 at 7:23 am
Hardy21 (5/29/2012)
Lynn Pettis - you are right.I have just given one option to change the query.
It will calculate and store filtered records in temp table so during delete, it will directly use the ID value in WHERE condition.
Why in the world would you read all the records to put keys into a temp table and then do another pass for the delete?
OP:
1) check for blocking while your delete is running
2) do you have any triggers on the table?
3) are there any foreign keys being hit?
4) it can be much more effective to break the delete up into smaller batches that can use an index seek to get 5000 or 10000 rows at a time to delete. this allows for better concurrency too since you can get row or page locking instead of a table lock. You can also do explicit transactions and error handling and even put in a waitfor delay to open up headroom on the table/server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 29, 2012 at 7:56 am
TheSQLGuru (5/29/2012)
Hardy21 (5/29/2012)
Lynn Pettis - you are right.I have just given one option to change the query.
It will calculate and store filtered records in temp table so during delete, it will directly use the ID value in WHERE condition.
Why in the world would you read all the records to put keys into a temp table and then do another pass for the delete?
OP:
1) check for blocking while your delete is running
2) do you have any triggers on the table?
3) are there any foreign keys being hit?
4) it can be much more effective to break the delete up into smaller batches that can use an index seek to get 5000 or 10000 rows at a time to delete. this allows for better concurrency too since you can get row or page locking instead of a table lock. You can also do explicit transactions and error handling and even put in a waitfor delay to open up headroom on the table/server.
All of which can easily be done using the code I provided as a starting point.
May 29, 2012 at 8:27 am
DOH!! Coffee clearly hadn't kicked in when I posted! At least I offered up a few improvements ... 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 29, 2012 at 8:39 am
Is it one-off data-fix task? If you have no triggers on the table which you need to execute on delete, if it's not referenced by FK relationships and you need to delete most of records from the table (smaller chunk will stay) you can use "preserve needed, truncate & re-insert" method:
-- the following can be faster, depends on how many records should really stay:
SELECT GR.*
INTO #RecordsToStay
FROM GlobalRecords AS GR
LEFT JOIN localRecord AS LR
ON GR.GlobalRecodID = LR.GlobalRecordID
WHERE NOT (DATEDIFF(day, GR.ModificationDate, getdate()) > 365 and LR.GlobalRecordID IS NULL)
TRUNCATE TABLE GlobalRecords -- that is going to be very fast :-)....
-- again, if it's only hand-full of records and no triggers on insert, it shouldn't be as bad
INSERT GlobalRecords SELECT * FROM #RecordsToStay
May 29, 2012 at 9:24 am
Eugene Elutin (5/29/2012)
Is it one-off data-fix task? If you have no triggers on the table which you need to execute on delete, if it's not referenced by FK relationships and you need to delete most of records from the table (smaller chunk will stay) you can use "preserve needed, truncate & re-insert" method:
-- the following can be faster, depends on how many records should really stay:
SELECT GR.*
INTO #RecordsToStay
FROM GlobalRecords AS GR
LEFT JOIN localRecord AS LR
ON GR.GlobalRecodID = LR.GlobalRecordID
WHERE NOT (DATEDIFF(day, GR.ModificationDate, getdate()) > 365 and LR.GlobalRecordID IS NULL)
TRUNCATE TABLE GlobalRecords -- that is going to be very fast :-)....
-- again, if it's only hand-full of records and no triggers on insert, it shouldn't be as bad
INSERT GlobalRecords SELECT * FROM #RecordsToStay
Let's make your code SARGable:
-- the following can be faster, depends on how many records should really stay:
SELECT
GR.*
INTO
#RecordsToStay
FROM
dbo.GlobalRecords AS GR
LEFT JOIN dbo.localRecord AS LR
ON GR.GlobalRecodID = LR.GlobalRecordID
WHERE
GR.ModificationDate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 365, 0) AND
GR.ModificationDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 1, 0)
--NOT (DATEDIFF(day, GR.ModificationDate, getdate()) > 365
AND LR.GlobalRecordID IS NULL)
TRUNCATE TABLE GlobalRecords -- that is going to be very fast ....
-- again, if it's only hand-full of records and no triggers on insert, it shouldn't be as bad
INSERT GlobalRecords SELECT * FROM #RecordsToStay
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply