Deleting all but top record.

Hey Guys,

I have Performance Monitor running and storing the network usage to my MsSQL database, and this is done a few times a minute. I have a page that then shows show much of my bandwidth is being used. As you can gather, the database quickly starts filling up with hundrreds of records so I could do with a script that delete these records.
I cant simply delete all records because that would cause my webpage to fail so I need a way to delete all records apart from the latest one.
Wondering if anyone would know how I could do this?delete from tableRecords where recordid < (select max(recordid) from tablerecords)

Nick|||

Thank you, that works great.
I've just however noticed a potential problem. I understand that the RecordID can only go so high then will give errors. The thing is, the PerfMon has only been running a few hours and already it has produced 1800+ records. With your script I can just keep it as one record in the table, but the RecordID will get higher and higher. Im wondering what will happening if I let this run for a week or so, eventually it will stop working.
Anyone got any ideas?

|||Dont use an auto-identity field.
insert into table
select max(recordid) + 1 from table, [rest of fields]
Since you will only have one record in there at a time, this shouldnt hurt your performance.
Nick

Delete-operation performance problem

Hi!

I'm experiencing the following experience problem with my SQL Server 2000.

Explanation a' la example;

1.
I insert data using my SP; EXEC dbo.up_DataInsert
This is fine, SQL Profiler duration only a few ms.

2.
I try to select the data.
select * from dbo.tblData where DataNumber = 283279
This is fine, SQL Profiler duration only a few ms.

3.
delete from dbo.tblData where DataNumber = 283279
This is NOT fine, SQL Profiler duration up to 50 seconds!!!

I have got a unique index with ignore duplicate key and the table has about
180.000 records.

What could be wrong here??

/Magnus"Magnus sterberg" <magnus.osterberg@.abo.fi> wrote in message
news:d9bgug$rcr$1@.plaza.suomi.net...
> Hi!
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.
> What could be wrong here??
> /Magnus

No idea - have you checked the query plan in Profiler to see where the time
is going? And are there any other factors, such as a DELETE trigger on the
table? If you can post the table DDL (including keys and indexes) and also
details of the query plan (SET SHOWPLAN_TEXT/ALL), someone may be able to
suggest something.

Simon|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.

Possible causes:

1) Blocking.

2) Autogrow.

3) There is a trigger on the table.

4) There is a FK constraint from another, big table, and the FK
column in that table is not indexed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp