?
Solved

Deleting top 'n' rows from table

Posted on 2003-02-26
13
Medium Priority
?
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
13 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 69

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 69

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 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