How the Error Occurs
Recently I've been working on optimizing the DeleteClassification stored procedure. It works mainly with two tablesValidTransaction (parent) and Classification (child)which are in a one-to-many relationship with the TransactionId foreign key.
The purpose of this stored procedure is to delete Classification records for all ValidTransactions that fall within a specified period/month. Using the TransactionDate field from the ValidTransaction table selects records in the date range. In my case, ValidTransaction contains data for 12 periods (one year) with 1.2 million rows total (100,000 rows per month on average). The Classification table has 10 rows on average for one row in the ValidTransaction table, with 12 million rows total. Cleaning up the Classification data for a specified period/month means deleting 1 million rows from the Classification table.
This presents two major problems:
shows a simplified version of the original DeleteClassification stored procedure. TransactionId values are selected into the TransactToReset cursor for all rows in the ValidTransaction table within a specified month. Then these TransactionIds are fetched into the v_TransactionId variable and are deleted one by one. The v_LoopIndex variable is used to count deleted records and commit every one-hundred of them.