Why DELETE takes longer than INSERT?

[MAB71]

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.

[Lyndon Hills]

On Mon, 3 Nov 2003 12:51:20 +0500, "MAB71" <b1*********@ya hoo.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.

[louis nguyen]

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.

[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*********@ya hoo.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.

[WangKhar]

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" <[e-mail] > wrote in message news:<3f******* ***@newspeer2.t ds.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.