468,140 Members | 1,532 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,140 developers. It's quick & easy.

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 2600
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Axel Panning | last post: by
2 posts views Thread by kumar | last post: by
3 posts views Thread by John Rivers | last post: by
3 posts views Thread by NateDawg | last post: by
3 posts views Thread by sarathy | last post: by
22 posts views Thread by Cylix | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.