For implementing wrap around for a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Iam looking a good solution for wrapping around records in the table. The
requirement is to keep only last n (eg. 20000) number of records in the
table.
After inserting the n+1 record the first record need to be removed from the
table. so on...

Is it better to place the logic in the application side or in the database
server side (using stored procedure /triggeres??)?

Hari
 
If you do this using a trigger, you can be sure to keep 20,000 rows at all
times ... real time. Generally though I have seen this done by creating some
automated process that runs at an interval (hourly, nightly, weekly, etc.)
that arhives (or deletes) the records over 20,000. This automated process
could be a .NET application or a scheduled SQL Server job. I am not a fan of
the trigger option because every time you insert into the table the trigger
will run. But f you want real time limits, it will work.


// John Papa
// http://codebetter.com/blogs/john.papa
 
Is it better to place the logic in the application side or in the database
server side (using stored procedure /triggeres??)?
Click to expand...

I would do it in "stored procedure" along with the "Insert" command in 3
steps.

1. Kount how many records (using primary key) in excess of 20000.
2. Delete the oldest ones or archive them.
3. Finally, insert the new records.

This way, there is no need to schedule a job

John
 
Please give some idea about the performace issues on
these both solutions?
Trigger/stored procedure vs. scheduled job deletion?

or instead of inserting into the end(appeding)and
deleting the first record, Is there anyway i can force to
insert the next record to the beging of the record postion
(so i dont have delete the record..)

-Hari
 
Please give some idea about the performace issues on
these both solutions?
Trigger/stored procedure vs. scheduled job deletion?

or instead of inserting into the end(appeding)and
deleting the first record, Is there anyway i can force to
insert the next record to the beging of the record postion
(so i dont have delete the record..)

-Hari
 
Hari said:
Please give some idea about the performace issues on
these both solutions?
Click to expand...

Depend on the capacity of your system. Mine is quad-processors, 2+GHZ clock
speed, 4GB RAM. With sproc, and 20,000 records to deal with. Performance is
not an issue.

John
 
Back
Top