Raja:

Do you have INDEXES on the column tempids on the table TEMPTABLE ?
If not ....create an index and try this again...

cheers...
Shashi Mannepalli

rajasdantony@gmail.com wrote:
> Hi,
>
> I have a scheduled job that does an archival job. Archive process
> deletes records by family i.e., say the application is based on travel
> trips, "trip"s are archived one by one. When a "trip" is archived,
> records related to that "trip" in various related tables are deleted in
> proper hierarchy. Deletion itself takes long time (around 3 minutes per
> "trip") on top of that it makes any other operation insert, update,
> select to wait even though the data that's getting deleted is not at
> all related to them.
>
> I do deletes based on the primary key only ... some thing like
>
> DELETE FROM TABLE1
> WHERE TABLE1.PRIMARYKEYCOLUMN in (SELECT tempids FROM temptable)
>
> Most of my selects have nolock. But still when this archival process
> runs application is not usable by anyone else. Let me know if I'm
> missing something. and if there is any solution, workaround to this
> problem.
>
> thanks for your help.
>
> Regards,
> Raja