By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

delete lock problem

P: n/a
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

Aug 21 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

ra**********@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
Aug 22 '06 #2

P: n/a
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

ra**********@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
Aug 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.