Hi, I am excutiong below Stored proc in database having nearly 300 tables to delete data based on parameter passed.But its running long hrs.Initially i used cursor same time its taking.so i chnaged to variable instead of cursor still its taking 3 hrs. The data should delete based on the parameter passed (Approx.deletion row count in Millions) Code as below: Please suggest any improvments in terms of performance. Thanks in Advance.

You could be running into blocking that stalls your script. But it could also be that some table is big and takes a long time to process. On my web site, you find beta_lockinfo which is a free stored procedures, which will reveal both blocking and what statement the process is currently running.

Stored Proc Excuting long time

Accepted answer

Erland Sommarskog 101K Reputation points MVP

2022-09-08T21:31:25.207+00:00

You could be running into blocking that stalls your script. But it could also be that some table is big and takes a long time to process.

On my web site, you find beta_lockinfo which is a free stored procedures, which will reveal both blocking and what statement the process is currently running.
Please sign in to rate this answer.
Raj0125 511 Reputation points

2022-09-09T02:22:31.833+00:00

Thanks for response.Yes some tables having Millions of records to delete may cause impact on performance.I am looking is there any ideal solution to improve from long running.
When i check Excution Plan : clusterd index scan is :26% Delete on clustered Index taking 70% cost.

After change the script as below:

;WITH CTE AS
(SELECT Load_Skey FROM ' + @DeezNutz _Of_Table + ')
Delete from CTE
where load_skey not in
(
select distinct top('+@top_deng +') Load_SKey
from CTE
order by 1 desc
)'

Delete on clustered Index taking 70% cost. comes down to 50%. some what better.
Please suggest is there any way instead of Deleting a lot of rows at a time ,choose to delete half of the records first and second half of the records second time . , like that..

Thanks in Advance
Sign in to comment

Stored Proc Excuting long time

0 additional answers