delete query with large data taking too long in sql server

ykl
ykl
340 Points
17 Posts

Delete query with large data taking too long in sql server. I'm executing delete command to delete around 2lac row with large data row taking undefine time to execute and never ending process.

Is there any way to reduce time for delete?

Views: 353
Total Answered: 2
Total Marked As Answer: 2
Posted On: 29-Jul-2024 03:27

Share:   fb twitter linkedin
Answers
Rashmi
Rashmi
1200 Points
20 Posts
         

Try to split task in small pieces as:

DELETE TOP (10) YourTable WHERE col in ('1','2','3','7')
WHILE @@rowcount > 0
    BEGINE
    DELETE TOP (10) YourTable where col in ('1','2','3','7')
    END
Posted On: 29-Jul-2024 04:52
Thanks. It's good idea. Worked for me.
 - ykl  30-Jul-2024 00:56
beginer
beginer
1576 Points
53 Posts
         

Also, try following steps to increase perfomance:

--Disable CONSTRAINT
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL;

--Disable Index
ALTER INDEX ALL ON YourTableName DISABLE;

--Rebuild Index
ALTER INDEX ALL ON YourTableName REBUILD;

--Enable CONSTRAINT
ALTER TABLE YourTableName CHECK CONSTRAINT ALL;

--Delete again
Posted On: 30-Jul-2024 00:54
Thanks
 - ykl  30-Jul-2024 00:56
 Log In to Chat