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

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

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