Posted by Erland Sommarskog on 10/01/15 11:17

akej via SQLMonster.com (forum@SQLMonster.com) writes:
> Thanks Erland, your suggestion is helpful however how can implement it,
> i guess by job that must define, so when this job will be start and who
> will start it??
>
> Also i can remove all these relationship, only the ID's will be stay,
> now each time when t1ID will be deleted i will put this t1ID in some
> pemanent table suppose [toDelete] and from now i need to start job or
> jobs that let's say every 10 minut will remove 1000 rows from each
> connection table or from first connection table after this from second
> and so on, in the end i need to remove this t1ID. However i'm new in sql
> and i don't really know how to implement it. Maybe u reject this and
> have more efficient suggestion.

Well, since I don't like dropping foreign-key constraints, I would first
look into the situation a little closer. It seems that you have a
"connection" and you have 200000 rows added for that connection that
you want to drop. Maybe there is reason to consider why all those rows
were added in the first place?

This may be a stupid question, but pleaes keep in mind that since I know
nothing about your business, I have to try some stabs in the dark.

But if you truly want an asynchronous delete, I would set up a in SQL
Agent that runs with some frequency and which deletes orphaned rows.
There could be a trigger on the table, so that when a row is deleted,
the job is started, but frankly, I would actually skip that step. This
job could be devised so that it deletes all orphans it can find, possibly
batched with SET ROWCOUNT. Or it could be written so that it stops after
some time, and take remaining oprhans on the next time, depending on
how you want to spread the load.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]