delete in small chunks from cursor data - T-SQL stored Procedure

Question

i have written this t-sql stored procedure. This is working fine , but since this stored procedure will be used to delete a lot of data (for example 1M to 2M) , I think, this can cause some locks in the table, or cause some db performance etc. So I am thinking, if we delete in batch for example at a time delete 1000 records. I am not totally sure about how to do this without cause any issue in db.

ALTER PROCEDURE [schema].[purge_data] @count INT---(count input can be in millions)
AS
DECLARE @p_number VARCHAR(22)
    ,@p_r_number VARCHAR(5)

DECLARE data_cursor CURSOR
FOR
SELECT TOP (@count) JRC_policy_number
    ,jrc_part_range_nbr
FROM [staging].[test].[p_location]
WHERE JRC_POLICY_TERM_DT < CAST('19950101 00:00:00.000' AS DATETIME)
    AND jrc_policy_status = 'T'

OPEN data_cursor

FETCH NEXT
FROM data_cursor
INTO @p_number
    ,@p_r_number

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE
    FROM [staging].[test].[p_location]
    WHERE JRC_policy_number = @p_number
        AND jrc_part_range_nbr = @p_r_number

    FETCH NEXT
    FROM data_cursor
    INTO @p_number
        ,@p_r_number
END

CLOSE data_cursor

DEALLOCATE data_cursor

Edit :

I had already tried without cursor - direct delete query like below.

DELETE TOP (1000) FROM [MyTab] WHERE YourConditions

It was very fast , it took 34 seconds to delete 1M records, but , during the 34 seconds, the table was locked completely. In production p_locator table is being used 24/7 , and being used by a very critical application, which expects response time in milliseconds, our purge script should not impact the the main application in any way. that's why I have chosen this cursor approach. pls guide

Answer 1

With some of your references I've written the below stored proc. Ofcourse there will be ALOT of scope for improvements. Pls share.

ALTER PROCEDURE [dbo].[purge_data] @count INT
AS
DECLARE @iteration INT
       ,@remainder INT
       ,@current_count INT

BEGIN
    SELECT @current_count = count(*)
    FROM PROD_TBL
    WHERE JRC_POLICY_TERM_DT < CAST('19950101 00:00:00.000' AS DATETIME)
        AND JRC_POLICY_STATUS = 'T'
        AND JRC_PLCY_ADMIN_SYS_CD = 'X'

    IF (@current_count < @count)
    BEGIN
        SET @count = @current_count
    END

    SET @iteration = @count / 10000
    SET @remainder = @count % 10000

    WHILE (@iteration > 0)
    BEGIN
        DELETE
        FROM PROD_TBL
        FROM (
            SELECT TOP 10000 JRC_POLICY_NUMBER
                ,JRC_PART_RANGE_NBR
                ,JRC_PLCY_ADMIN_SYS_CD
            FROM PROD_TBL
            WHERE JRC_POLICY_TERM_DT < CAST('19950101 00:00:00.000' AS DATETIME)
                AND JRC_POLICY_STATUS = 'T'
                AND JRC_PLCY_ADMIN_SYS_CD = 'X'
            ) pol_locator_tbl
        WHERE PROD_TBL.JRC_POLICY_NUMBER = pol_locator_tbl.JRC_POLICY_NUMBER
            AND PROD_TBL.JRC_PART_RANGE_NBR = pol_locator_tbl.JRC_PART_RANGE_NBR
            AND PROD_TBL.JRC_PLCY_ADMIN_SYS_CD=pol_locator_tbl.JRC_PLCY_ADMIN_SYS_CD

        SET @iteration = @iteration - 1
    END

    IF (@remainder > 0)
    BEGIN
        DELETE
        FROM PROD_TBL
        FROM (
            SELECT TOP (@remainder) JRC_POLICY_NUMBER
                ,JRC_PART_RANGE_NBR
                ,JRC_PLCY_ADMIN_SYS_CD
            FROM PROD_TBL
            WHERE JRC_POLICY_TERM_DT < CAST('19950101 00:00:00.000' AS DATETIME)
                AND JRC_POLICY_STATUS = 'T'
                AND JRC_PLCY_ADMIN_SYS_CD = 'X'
            ) pol_locator_tbl
        WHERE PROD_TBL.JRC_POLICY_NUMBER = pol_locator_tbl.JRC_POLICY_NUMBER
            AND PROD_TBL.JRC_PART_RANGE_NBR = pol_locator_tbl.JRC_PART_RANGE_NBR
            AND PROD_TBL.JRC_PLCY_ADMIN_SYS_CD=pol_locator_tbl.JRC_PLCY_ADMIN_SYS_CD

    END
END
END