Hi,

thanks for the reply. actually TEMPTABLE is a temporary table variable
with tempids as primary key. should I take car of anything else?

Thanks.
Raja

Shashi Mannepalli wrote:
> 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