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
2  1316 
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the...
|
by: Ray |
last post by:
I am having my first experience using BLOB as a row in a table. I am
using it to insert graphics for labels we print. I have no problem
inserting into and select from the table. The graphic is...
|
by: Keith Smith |
last post by:
I have noticed that when I uninstall my app it doesn't delete the Program
Files / MyApp folder if there are "new" files in it that weren't there
before. It deletes all files except the "new" ones....
|
by: Matt Hamilton |
last post by:
I have a simple image gallery where I want to allow users to delete files.
The problem I have is that after an image is displayed in the browser, I am
not able to delete the file because "The...
|
by: news.microsoft.com |
last post by:
I need some help here guys.... i have been simply trying to delete a file
from the webserver using asp.net
using the following code.
System.IO.File.Delete(FullPath);
however the error message...
|
by: Daniel |
last post by:
Hi All,
I have question regarding to the read, and update the row in the table.
for example:
user A, read the row of data from the table.
user B, read the row of data from the table as well....
|
by: pike |
last post by:
8.1 FP11 on AIX 5.3.
The following DELETE is poorly performing and causing lock escalation
(and subsequent deadlock time-outs):
DELETE FROM submission_log
WHERE subm_id = ?
OR subm_id =...
|
by: ray2heavy |
last post by:
Hey guys, I am getting a "database cant lock table "blah" because it is already in use by another person or process" while trying to delete, then re-populate it. This table is viewed in a subform...
|
by: Daniel Roy |
last post by:
From Oracle:
ORA-04091 table string.string is mutating, trigger/function may not
see it
Cause: A trigger (or a user defined PL/SQL function that is referenced
in this statement) attempted to...
|
by: Michel Esber |
last post by:
Hello,
Env: DB2 V8 LUW FP16 running Linux
create Table X (machine_id varchar(24) not null, ctime timestamp not
null);
create index iFoo on X (MACHINE_ID, CTIME) allow reverse scans;
alter...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
