Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Deleting top 'n' rows from table

Posted on 2003-02-26
13
Medium Priority
?
380 Views
Last Modified: 2008-03-10
I have to maintain the size of tables in my database.
The records are inserted at realtime at a frequency of say 2 records per second.

So to maintain the size of tables i deleted the oldest records(FIFO)from the table by an insert trigger.

This is causing performance problems and I switch over to sql server agent's job shedule, which executes a procedure once in a day and checks if the rowcount of the table > 200000.  If it is so then it has to delete the oldest records to maintain the table size @ 200000.

To do this i wrote a procedure like this.

declare @excessrows integer
set @excessrows = 200000 - (select count(*) from table)

delete table from (select top @excessrows * from table ) as t1 where table.date = t1.date

This is not working as TOP clause does not takes variables
and if we give TOP 1000 it works.

I dont want to use Set RowCount, since other parallel queries will be affected..

So how to maintain the size of table in SQLServer?

Regards.
Viswanath.
0
Comment
Question by:viswanathat
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 1

Expert Comment

by:johan_asplund
ID: 8031910
Hello

Is the following any help? assuming that u have somekind of id in the table

DELETE FROM table WHERE id in (
SELECT top @excessrows ID form Table order by date desc
)

/Johan
0
 
LVL 1

Accepted Solution

by:
CarMar earned 80 total points
ID: 8031961
I use in this example the column RecordID (identity) to be able to select the records I want to delete.

IF ( SELECT COUNT(*) FROM table ) > 200000
BEGIN
  DELETE table FROM table
  WHERE RecordID NOT IN
    (SELECT TOP 200000 RecordID FROM table
     ORDER BY RecordID DESC )
END

You must pay attention to the order of the records on your table. As you see in the code I order RecordID in DESC order to delete only the oldest records.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8032163
To use the TOP keyword with a variable you need to use dynamic SQL:

declare @excessrows integer
set @excessrows = 200000
EXECUTE(
'DELETE TABLE FROM (SELECT TOP ' + CAST(@excessrows AS VARCHAR) + ' * FROM TABLE ) AS t1 WHERE table.date = t1.date')

Cheers
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Expert Comment

by:hakyemez
ID: 8032533
Try this

declare @excessrows integer
set @excessrows = 200000 - (select count(*) from table)

SET ROWCOUNT @execrows

delete table from (select * from table ) as t1 where table.date = t1.date
0
 

Expert Comment

by:Tyip
ID: 8034400
Why would your other parallel queries be affected if you reset rowcount after your delete? rowcount will only affect your current session.

There are 2 solutions to this problem:
1. declare cursor and delete 1 at a time, or
2. if you have a single field for PK on the table,
  delete
  from   table t
  where  PK NOT IN ( select top 200000 PK
                      from   table t0
                      where  ...
                      order by ...
                   )

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 8034673
One additional point:

Since you want to delete the oldest records, you should ORDER BY date (or ORDER BY id), but ascending not descending.  A descending sort will cause you to delete the most recent records first.

0
 
LVL 1

Expert Comment

by:CarMar
ID: 8038012
To ScottPletcher:

You just forget one important point: The sort order depends on the clause he might use:
- IN
- NOT IN
0
 

Expert Comment

by:Tyip
ID: 8038053
The purpose of the subquery is to select all the rows that he wants to keep. Therefore, the ORDER BY in this case should be in DESC. You want to keep the 200000 most recent rows.

The outer query deletes everything that is NOT IN this subquery, which is everything else.

0
 
LVL 1

Expert Comment

by:CarMar
ID: 8038105
Viswanath,

I added a where clause to my code to improve performance to avoid a table scan.
You should use in the where clause an index filed. If you use an identity PK you can just use: WHERE PKID > 0


IF ( SELECT COUNT(*) FROM table ) > 200000
BEGIN
 DELETE table FROM table
 WHERE RecordID NOT IN
   (SELECT TOP 200000 RecordID FROM table
    WHERE ....(use an index ou identity PK)
    ORDER BY RecordID DESC )
END

Carlos
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 8042066
CarMar,

Actually I was referring to the first post by Johan.  I was very slow in posting because of other activity here, so I should have refreshed prior to the post.
0
 

Expert Comment

by:CleanupPing
ID: 9276289
viswanathat:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question