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 2 11790
"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
[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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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?
|
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:
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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
|
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
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |