June 8, 2012 at 9:56 am
Great insight all.
There IS an FK between the tables. I'd rather NOT drop it.
The child table does NOT contain the archive_date field, but BOTH tables contain [license_message_id].
The way I've implemented the maintenance job, I build a "pillar" table (#batchlist) that contains ONLY the [license_message_id]'s to be DELETED on every batch iteration. Since both tables contain that field, the DELETE for each simply contains an INNER JOIN to that pillar.
Your help is indispensable. Thanks.
The @batch sweet spot is officially 3000...
June 8, 2012 at 10:02 am
ScottPletcher (6/8/2012)
Might not be a good idea if inserts are expected while also doing the deletes.
Perhaps, if you really normally do INSERTs that would violate the constraint. Hopefully that's extremely rare, and the constraint is more a DELETE preventative than an INSERT check (and presumably unauthorized DELETEs are also not allowed to occur (except by accident)).
I'd have to disagree. You can't rely on the application to ensure that foriegn keys won't be violated. Also, you need to be concerned about other means of inserting data as well, such as ad hoc queries.
June 8, 2012 at 10:54 am
ScottPletcher (6/8/2012)
Might not be a good idea if inserts are expected while also doing the deletes.
Perhaps, if you really normally do INSERTs that would violate the constraint. Hopefully that's extremely rare, and the constraint is more a DELETE preventative than an INSERT check (and presumably unauthorized DELETEs are also not allowed to occur (except by accident)).
Then why even have the constraint at all then. If all inserts/updates and deletes are done under controlled access methods (ie SPs) and ad-hoc access to the database is not a factor, the constraints are just extra overhead.
Either way, it is always better to make a snapshot table of the IDs you want to delete then delete from the snapshot table. With that method you get the best concurrency: you only use the where clause once against the criteria to select IDs that are to be aged off and you do not need to wrap the delete from both tables in one big transaction.
The probability of survival is inversely proportional to the angle of arrival.
June 8, 2012 at 12:23 pm
Then why even have the constraint at all then. If all inserts/updates and deletes are done under controlled access methods (ie SPs) and ad-hoc access to the database is not a factor, the constraints are just extra overhead.
No. They still provide a fail-safe and de facto documentation.
To me, if you routinely have INSERTs (or DELETEs) failing daily because of violated fk constraints, you've got a major problem.
I presumed these types of DELETEs are a specialized process, done during least-used times, and on a limited schedule.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2012 at 1:08 pm
I presumed these types of DELETEs are a specialized process, done during least-used times, and on a limited schedule.
You presumed well.
I'm not horribly against the idea of dropping the FK, but maybe I'm not fully understanding the advantage of doing so. How does deleting the parent records first improve performance?
June 8, 2012 at 2:26 pm
I'm not horribly against the idea of dropping the FK, but maybe I'm not fully understanding the advantage of doing so. How does deleting the parent records first improve performance?
It's not guaranteed, but it might.
Since the date column that determines deletion-eligibility is in the parent table, to start the deletes from the child table requires joining from the child to the parent.
If that join is a lot of overhead and/or very slow and/or causes delays to other tasks, then starting the delete from the parent table and avoiding the join can speed things up.
If that join is not particularly performance-harming, then do the join, delete from the child first, and use the OUTPUT results from the child delete to delete the associated parents.
I didn't mean to overstate it. I'm not gung-ho on disabling/removing fk constraints, but there are sometimes situations where it can really speed up delete processing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply