473,729 Members | 2,064 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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*******@hotma il.com
Jul 20 '05 #1
2 11794

"Bob Ganger" <rg*******@hotm ail.com> wrote in message
news:Y4******** *******@news.us west.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*******@hotma il.com

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

Bob Ganger (rg*******@hotm ail.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(smal lid)
..

if bigtable.smalli d is not indexed and you delete a row from
smalltable.smal lid, 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
6459
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 results below, I would think that the delete should have deleted row 1 {1 5 me) and not row 3 (1 5 they) when I run this statement delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name = 'me'; Any ideas on why row 2 is deleted?
1
8900
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 automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However:
1
3849
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 ones below run againt it. Everything is fine for a few weeks, and then without fail, all accesses to the table start slowing down, to the point where even trying to select all rows starts timing out. At that point, the only way to make...
6
3858
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 "Generations"), and it allows the user to add, edit and delete the various records of the table. "Generations" table has the following fields: "IDPerson", NamePerson", "AgePerson" and "IDParent". A record contains the information about a person (his name, his...
5
3109
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 the row. The Delete method marks the row as Deleted in the DataSet or DataTable but does not remove it. Instead when the DataAdapter encounters a row marked as Deleted, it executes its DeleteCommand method to delete the row at the data source. The...
13
1884
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 integer, and setting the identity property to true. I set that row to be my primary key. I then added a dataset, and added my table to the dataset. When I configure the table, I have no Update or Delete commands, even though the wizard has the...
4
4862
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 sql DataAdapter (da) da.SelectCommand.CommandText = "Select * from Servertbl1" da.Fill(ds, "tbl1") so far, so good. If I add a row to the datagridview I use the following sqlDataAdapter code to update the server table - which works OK when...
2
5420
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 as: PIN Name Added Date 100411 A 7/11/2007 10:12:58 AM 100411 A 7/17/2007 10:54:23 AM 100413 B 7/11/2007 10:13:28 AM
3
12334
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 other reading/deleting rows. The 'deleter' application runs a MIN/MAX (timestamp) for each ID and, if the difference between min/max is greater than 1h, it reads all
0
9291
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9217
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9158
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8160
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4535
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4799
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2698
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.