How to delete foreign key with lots of references

Question

Table B have several thousand records that references table A with a foreign key.

The application sends a request to delete the row from table A. It's important that the synchronous operation would be immediate and won't be at risk for timeouts.

If a cascade delete is used, and thus several thousands of records will be deleted as well, could it cause the deletion to take a long time? (not immediate).

Assuming that it could lead to a long deletion operation, what could be done as an alternative?

I've though perhaps to delete the record as deleted and to perform the actual deletion in some background process (where the referencing rows would be deleted before the parent), but this approach feels rather error prone (since all of the existing queries will need to reference this new markedAsDeleted column.

Any ideas?

Answer 1

If the only concern is timeout there are probably 3 ways to get around that.

1. Like referenced by Lennart, proper indexing should allow even the deletion of 1000's of rows to happen fairly quickly. Assuming you can run a query like below fairly quickly, a DELETE would only be negatively impacted by the number of indexes on [Table B] that have to be updated for each row removed.

SELECT [Table B].ID
FROM [Table B]
    INNER JOIN [Table A]
        ON [Table B].[Table A ID] = [Table A].ID
WHERE [Table A].ID = @Value

2. Extend out the command timeout on the operation that triggers this delete. The user would need to wait for it to complete, but they would be notified of a failure if there was one and would know for sure that the row (and all its child data) was removed.
3. (Full-disclosure, I dislike this option but it make serve your purposes if the above two don't help enough) You can also create a Delete Log table. Where you would store whatever records from [Table A] need to be removed. Your Delete operation on the end user application would be a simple Insert into this Delete Log table. You would then have a background process (through either an Automation tool or SSIS/Sql Agent) that takes that log and removes the associated Table B and Table A records. This would then have all the time it needs to remove the records in question. The main downside is all the records exist until the background routine Runs and then completes so the end user would need to understand that their records are not removed immediately.

Hopefully this helps.