>We have a SQL Server table(table_1) that we occasionally need to perform several
>thousand deletes on. This table has several other tables(table_2, table_3, table_4)
>with foreign keys defined to the PK in table_1.
>
>Would it be more efficient to enable ON DELETE CASCADE on the
>child tables and then in our stored procedure delete table_1 records in batches?
>
>Or would it be better to not enable ON DELETE CASCADE, then in our
>stored procedure perform the deletes
>on table_2, table_3, table_4 then lastly table_1, and perform the deletes in batches?
>
>We want to avoid blocking and make this as efficient as possible.

I think DELETE CASCADE is a good option. You may want to do your deletes in batches - the optimal batch size you need to determine based on many factors.