473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How do I delete many rows without monopolizing server?

Hello,

I am working on a project using SQL Server 2000 with a database containing
about 10 related tables with a lot of columns containing text. The total
current size of the database is about 2 Gig. When I delete data from the
database, it takes a lot of system resources and monopolizes the database so
that all other query requests are slow as mud!

Ideally, I would like to be able to issue delete commands to the database on
a primary table and get a fast response back. Then, it doesn't matter to me
how long the actual deletion operation takes as long as its priority is low
compared to the other query requests coming in. Typically, removing a
single row from the primary table results in a deletion of up to 300 rows
from related tables.

Questions:
1. Can I create a trigger on the primary table that will delete the rows
from that table, issue a delayed/low priority delete for all of the other
tables, and return to the application quickly?
2. Can a trigger be run in an asynchrous mode? (that is, issue the command,
return immediately, and then go about its business on its own time).
3. Can the priority of an SQL statement be specified?
4. Is there a Transact-SQL "sleep" command that would allow you to do some
work -- sleep for a little bit -- do some more work -- etc?

Any help in this area would be greatly appreciated.

....Thanks in advance...

--
Bob Ganger
General Dynamics
rg*******@hotmail.com
Jul 20 '05 #1
2 11756

"Bob Ganger" <rg*******@hotmail.com> wrote in message
news:Y4***************@news.uswest.net...
Hello,

I am working on a project using SQL Server 2000 with a database containing
about 10 related tables with a lot of columns containing text. The total
current size of the database is about 2 Gig. When I delete data from the
database, it takes a lot of system resources and monopolizes the database so that all other query requests are slow as mud!

Ideally, I would like to be able to issue delete commands to the database on a primary table and get a fast response back. Then, it doesn't matter to me how long the actual deletion operation takes as long as its priority is low compared to the other query requests coming in. Typically, removing a
single row from the primary table results in a deletion of up to 300 rows
from related tables.
If you want to delete ALL of them, a simpe TRUNCATE TABLE FOO will work.

A non-portable method is to use ROWCOUNT

SET ROWCOUNT=100
DELETE FROM FOO

Repeat as needed.


Questions:
1. Can I create a trigger on the primary table that will delete the rows
from that table, issue a delayed/low priority delete for all of the other
tables, and return to the application quickly?
2. Can a trigger be run in an asynchrous mode? (that is, issue the command, return immediately, and then go about its business on its own time).
3. Can the priority of an SQL statement be specified?
4. Is there a Transact-SQL "sleep" command that would allow you to do some
work -- sleep for a little bit -- do some more work -- etc?

Any help in this area would be greatly appreciated.

...Thanks in advance...

--
Bob Ganger
General Dynamics
rg*******@hotmail.com

Jul 20 '05 #2
[posted and mailed, please reply in news]

Bob Ganger (rg*******@hotmail.com) writes:
Ideally, I would like to be able to issue delete commands to the
database on a primary table and get a fast response back. Then, it
doesn't matter to me how long the actual deletion operation takes as
long as its priority is low compared to the other query requests coming
in. Typically, removing a single row from the primary table results in
a deletion of up to 300 rows from related tables.
And deletion of 300 rows is not much. If deletion of 300 rows is
bringing the server to its knees, you should probably review the
DELETE statements, and make sure that there are appropriate indexes.
You could run the query from Query Analyzer with SET STATISTISTICS IO
ON to see which tables that are hit the most. You can also use SHOW
EXECUTION PLAN to analyse whether you have the best plan.

One thing to consider with DELETE statements is that there may be
foreign-key relations that are not indexed. Say that you have:

CREATE TABLE smalltable (smallid int NOT NULL PRIMARY KEY,
....)
go
CREATE TABLE bigtable (bigid int NOT NULL PRIMARY KEY,
...
smallid int NULL REFERENCES smalltable(smallid)
..

if bigtable.smallid is not indexed and you delete a row from
smalltable.smallid, then SQL Server has to scan bigtable from left to
right to check for references.

I answer your questions below for completeness. However, I hope the
answers will be moot, once you have found why the delete operations
takes so much resources.
1. Can I create a trigger on the primary table that will delete the rows
from that table, issue a delayed/low priority delete for all of the other
tables, and return to the application quickly?
This is only possible if there is now foreign-key constraints from the
sub-tables to the primary table - and there should probably be. And
"possible" hear does not mean that it is simple - it takes some real
trickery.
2. Can a trigger be run in an asynchrous mode? (that is, issue the
command, return immediately, and then go about its business on its own
time).
No, it is fundamental in database technology that a trigger is part
of the transaction that includes the statement that fired the trigger.
This is because triggers are intended for enforcing rules in the database,
either by checking for violations of the rules, or cascade updates to
implement them. In both cases it is instrumental that the trigger +
checks/cascades are executed in whole, else all must be rolled back.

What you can do, though, is set a bit in a table, and then have a job
run from SQL Agent which checks that table for work to do. In this
case, maybe you do not need a trigger at all. You could just have a
job that is run periodically from SQL Agent and that deletes orphans.
3. Can the priority of an SQL statement be specified?
No.
4. Is there a Transact-SQL "sleep" command that would allow you to do some
work -- sleep for a little bit -- do some more work -- etc?


No. But if you have very many rows to delete - 300 is a far cry
from that - you can use SET ROWCOUNT to limit the number of rows
that the DELETE statement operates on.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Gordon | last post by:
I have 2 tables t and t1. In this case, t1 is a copy of t. I want to delete rows from t1 based on criteria on the t table and a relationship between t ad t1 (in this case the id column). In the...
1
by: Andrew DeFaria | last post by:
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB...
1
by: Simon Withers | last post by:
I am using a sql server 2000 database to log the results from a monitor that I have running - essentially every minuite, the table described below has a insert and delete statements similar to the...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
5
by: rn5a | last post by:
The .NET 2.0 documentation states the following: When using a DataSet or DataTable in conjunction with a DataAdapter & a relational data source, use the Delete method of the DataRow to remove...
13
by: =?Utf-8?B?VmVybm9uIFBlcHBlcnM=?= | last post by:
I am using VS2005. I created a Windows Appication project. Inside the Server Explorer, I created a new SQLExpress database, and then created a new table. I added the rows, making my first row an...
4
by: =?Utf-8?B?UmljaA==?= | last post by:
On a form - I have a datagridview which is docked to the entire form. The datagridview allows users to Delete and/or Add Rows. On the Form_Load event I Fill the datagridview source table with a...
2
by: Radu | last post by:
Hi. I have a "union" table which results of a union of two tables. Occasionally I could have duplicates, when the same PIN has been added to both tables, albeit at different Datees/Times, such...
3
by: Michel Esber | last post by:
Hello, Environment: DB2 LUW v8 FP15 / Linux I have a table with 50+ Million rows. The table structure is basically (ID - Timestamp). I have two main applications - one inserting rows, and the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.