delete query with large data taking too long in sql server

ykl
ykl
316 Points
15 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: 133
Total Answered: 2
Total Marked As Answer: 2
Posted On: 29-Jul-2024 03:27

Share:   fb twitter linkedin
Answers
Rashmi
Rashmi
1068 Points
19 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
1544 Points
52 Posts
         

Also, try following steps to increase perfomance:

--Disable CONSTRAINT
ALTER TABLE _DataIngressPullerStatus NOCHECK CONSTRAINT ALL;

--Disable Index
ALTER INDEX ALL ON _DataIngressPullerStatus DISABLE;

--Rebuild Index
ALTER INDEX ALL ON _DataIngressPullerStatus REBUILD;

--Enable CONSTRAINT
ALTER TABLE _DataIngressPullerStatus CHECK CONSTRAINT ALL;

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