473,372 Members | 952 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,372 software developers and data experts.

Why DELETE takes longer than INSERT?

I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the
new month starts I create a new table with the same fields and move the data
of previous month into that table and delete it from calls. So 'calls' holds
the data of only the current month. for example at the start of november
2003 I ran the queries

Create Table Oct2003Calls {
................
................

}

/* Now insert data of october into new table */

INSERT Oct2003Calls

SELECT *
FROM calls
WHERE calldate < '11/1/03'
/* Finaly delete october data from calls table */

DELETE FROM calls
WHERE calldate < '11/1/03'

The problem is that while the insert query takes about 2 minutes to execute
the delete queries takes over 10 minutes to affect the same no. of rows. Why
is that?
This causes problems because user authentication stops when this query is
running which means users cant connect to the internet.


Jul 20 '05 #1
4 2775
On Mon, 3 Nov 2003 12:51:20 +0500, "MAB71" <b1*********@yahoo.com>
wrote:
I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the
new month starts I create a new table with the same fields and move the data
of previous month into that table and delete it from calls. So 'calls' holds
the data of only the current month. for example at the start of november
2003 I ran the queries

Create Table Oct2003Calls {
...............
...............

}

/* Now insert data of october into new table */

INSERT Oct2003Calls

SELECT *
FROM calls
WHERE calldate < '11/1/03'
/* Finaly delete october data from calls table */

DELETE FROM calls
WHERE calldate < '11/1/03'

The problem is that while the insert query takes about 2 minutes to execute
the delete queries takes over 10 minutes to affect the same no. of rows. Why
is that?
This causes problems because user authentication stops when this query is
running which means users cant connect to the internet.

Difficult to be sure, but some thoughts;

There are other tables referencing this one and a delete here is
cascading,

This table has a delete trigger on it (perhaps for auditing purposes)

RI is being checked

This table is indexed and other one isn't, or the number of indexes is
different.

Jul 20 '05 #2
Open up query analyzer, select view execution plan, and run your code.
It will tell you exactly what sql is doing. Taking a shot in the
dark, the table probably needs an index.
Jul 20 '05 #3
Bo
here's an idea to make your table maintenance a bit easier:

Assuming your really must keep data for each month in its own table, why
not put an trigger on CALLS table, and in the trigger select datepart of
current date. Then a case statement, which examines the result of datapart.
When = 01 insert new row into Jan2003Calls, When = 02 insert new row into
Feb2003Calls, etc. Then run a daemon which deletes from CALLS every 5
minutes, so it never loads up.
"MAB71" <b1*********@yahoo.com> wrote in message
news:bo*************@ID-31123.news.uni-berlin.de...
I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the
new month starts I create a new table with the same fields and move the data of previous month into that table and delete it from calls. So 'calls' holds the data of only the current month. for example at the start of november
2003 I ran the queries

Create Table Oct2003Calls {
...............
...............

}

/* Now insert data of october into new table */

INSERT Oct2003Calls

SELECT *
FROM calls
WHERE calldate < '11/1/03'
/* Finaly delete october data from calls table */

DELETE FROM calls
WHERE calldate < '11/1/03'

The problem is that while the insert query takes about 2 minutes to execute the delete queries takes over 10 minutes to affect the same no. of rows. Why is that?
This causes problems because user authentication stops when this query is
running which means users cant connect to the internet.


Jul 20 '05 #4
lol ... n1 Bo. Didnt realise MS Developers had a sense of humour, but
htats the best joke Ive read all day. Except the one about the hippo
eating the dwarf...
"Bo" <no@thank.you> wrote in message news:<3f**********@newspeer2.tds.net>...
here's an idea to make your table maintenance a bit easier:

Assuming your really must keep data for each month in its own table, why
not put an trigger on CALLS table, and in the trigger select datepart of
current date. Then a case statement, which examines the result of datapart.
When = 01 insert new row into Jan2003Calls, When = 02 insert new row into
Feb2003Calls, etc. Then run a daemon which deletes from CALLS every 5
minutes, so it never loads up.

Jul 20 '05 #5

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

Similar topics

13
by: Axel Panning | last post by:
Hallo, ich hab bei einem Programm bei mir festgestellt, daß das "deleten" von Feldern erheblich länger dauert, als das Anlegen von Feldern. p. Bei einem Programm von mir werden oft große...
2
by: kumar | last post by:
we are trying to delete data from a huge 75 million records table it takes 4hr to prune data delete from Company where recordid in (select top 10000 recordid from recordid_Fed3 where flag = 0)...
3
by: John Rivers | last post by:
Hello, I think this will apply to alot of web applications: users want the ability to delete a record in table x this record is related to records in other tables and those to others in...
3
by: NateDawg | last post by:
I'm reposting this. I'm kinda in a bind untill i get this figured out, so if anyone has some input it would sure help me out. Ok, I’ve noticed a few gridview problems floating around the forum....
3
by: sarathy | last post by:
Hi, Need a clarification wrt new and delete operators. Consider the 2 code snippets. 1. { Object *obj = new Object(); .... obj->method1();
22
by: Cylix | last post by:
I have a 4row x 1col table, I would like to drop all the content of row three. Since Mac IE5.2 does not suppport deleteRow method, I have also try to set the innerHTML=''; but it does not work. ...
33
by: desktop | last post by:
In the C++ standard sec 23.1.2 table 69 it says that erase(q) where q is a pointer to an element can be done in amortized constant time. I guess that is not worst case since std::set is...
1
watertraveller
by: watertraveller | last post by:
Hi all. My ultimate goal is to return two columns, where no single value appears anywhere twice. This means that not only do I want to check that nothing from column A appears in column B and...
1
by: ChrisC | last post by:
Hello, I am attempting to create a trigger to keep track of changes to a table, mainly about specific changes to the data in the table. I had hoped that triggers defined as for each row would...
1
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: 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...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
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...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
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...
0
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...

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.