Database as ringbuffer

Hello
I'm using a MS SQL Server 2000 database to log data from a datalogger. What
is the best way to make the database work as a ringbuffer, so when it has
stored say 5000 records it will start overwriting the oldest ones? (FIFO)
I need this ringbuffer functionality to maintain a static database size,
while still being able to log new data.
Are stored procedures the way to go, or are there any settings or tools
available in SQL Server 2000 to make this easy'
Regards,
AllanWhy not base your data retention on a time period rather than a fixed
number of rows:
CREATE TABLE foo (dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
etc)
As a scheduled process you can delete the old data for whatever time
period is required:
DELETE FROM foo
WHERE dt < DATEADD(HOUR,-24,CURRENT_TIMESTAMP)
If you really wanted to constrain the table to 5000 rows then you could
try this:
CREATE TABLE foo (seq INTEGER NOT NULL PRIMARY KEY, ...)
insert each new row:
INSERT INTO foo (seq, ...)
SELECT COALESCE(MAX(seq)+1,1), ...
FROM foo
remove old data:
DELETE FROM foo
WHERE seq <=
(SELECT MAX(seq)-5000
FROM foo)
but don't assume that this would guarantee a static database size.
Database size will still be determined by the recovery model and log
backup policy whatever method you use. Is database growth really that
much of a problem at 5000 rows? Seems like this should be well within
the spec of any system that meets the minimum hardware requirements for
SQL Server.
David Portas
SQL Server MVP
--|||Thank you very much for a very good and helpful reply, David!
Yes, data size is important in this specific case. However, the 5,000 was
just an example, it could be 10,000 or 100,000 for that matter. The thing
is, I only have limited resources available, since this is for a prototype
of an embedded system. I need to make sure that the database does not grow
beyond a certain point.
(SQL Server won't be used for the final embedded system of course - but for
timing reasons I have to go with what I know for now)
Again, thank you!
Allan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112346631.506210.317100@.g14g2000cwa.googlegroups.com...
> Why not base your data retention on a time period rather than a fixed
> number of rows:
> CREATE TABLE foo (dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
> etc)
> As a scheduled process you can delete the old data for whatever time
> period is required:
> DELETE FROM foo
> WHERE dt < DATEADD(HOUR,-24,CURRENT_TIMESTAMP)
> If you really wanted to constrain the table to 5000 rows then you could
> try this:
> CREATE TABLE foo (seq INTEGER NOT NULL PRIMARY KEY, ...)
> insert each new row:
> INSERT INTO foo (seq, ...)
> SELECT COALESCE(MAX(seq)+1,1), ...
> FROM foo
> remove old data:
> DELETE FROM foo
> WHERE seq <=
> (SELECT MAX(seq)-5000
> FROM foo)
> but don't assume that this would guarantee a static database size.
> Database size will still be determined by the recovery model and log
> backup policy whatever method you use. Is database growth really that
> much of a problem at 5000 rows? Seems like this should be well within
> the spec of any system that meets the minimum hardware requirements for
> SQL Server.
> --
> David Portas
> SQL Server MVP
> --
>|||> SQL Server won't be used for the final embedded system of course
Why "of course"? I'm not an expert on embedded systems but SQL Server
CE is intended for that market:
http://www.microsoft.com/sql/ce/default.asp
David Portas
SQL Server MVP
--|||Win CE wouldn't be a bad option. However, it is a decision that isn't in my
hands, but I will be sure to mention it as an alternative in my report. I'm
not sure what the system specs will be exactly.
I just need to get this prototype running, and we will see what happends.
:-)
Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112354491.449183.150370@.f14g2000cwb.googlegroups.com...
> Why "of course"? I'm not an expert on embedded systems but SQL Server
> CE is intended for that market:
> http://www.microsoft.com/sql/ce/default.asp
> --
> David Portas
> SQL Server MVP
> --
>|||Allan,
You could use an after insert trigger similar to that provided in the
following example:
create table table1 (col1 numeric(10,0) not null identity, col2 char(1))
create trigger table1_mytrigger
on table1 after insert as
begin
delete from table1
where table1.col1 in (select (col1 - 2) from inserted)
end
insert into table1 (col2) values ('A')
insert into table1 (col2) values ('B')
insert into table1 (col2) values ('C')
insert into table1 (col2) values ('D')
insert into table1 (col2) values ('E')
insert into table1 (col2) values ('E')
insert into table1 (col2) values ('F')
select * from table1
The trigger allows only 2 rows to exist in the table at anytime as long as
the identity column doesn't get out of wack. Of course you could to do any
type of select statement in the subquery to return a single value and then
change the where clause to use a less than sign on your key column.
HTH,
James K.
"Allan Nielsen" <ace-allan@.rocketmail.com> wrote in message
news:O4HASPpNFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Hello
> I'm using a MS SQL Server 2000 database to log data from a datalogger.
> What
> is the best way to make the database work as a ringbuffer, so when it has
> stored say 5000 records it will start overwriting the oldest ones? (FIFO)
> I need this ringbuffer functionality to maintain a static database size,
> while still being able to log new data.
> Are stored procedures the way to go, or are there any settings or tools
> available in SQL Server 2000 to make this easy'
> Regards,
> Allan
>|||On Fri, 1 Apr 2005 10:29:07 -0500, -=JLK=- wrote:
(snip)
>The trigger allows only 2 rows to exist in the table at anytime as long as
>the identity column doesn't get out of wack.
Hi James,
Neat trick - but not very reliable. An IDENTITY column should not be
used like this, as there is no guarantee that the identity values will
always be contiguous. Indeed - if ever an insert is rolled back, there
WILL be gaps in the range of identity values, and then your code will
fail.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Kind of crude, but preallocate a table with a row number between 0 and
4999, then set up a one-row,one column table with the "current pointer"
in it. Use a trigger on RingBuffer to update that value to MOD(pointer
+1, 5000).
UPDATE RingBuffer
SET ...
WHERE RingBuffer.row_nbr
= (SELECT current_ptr FROM Pointer);
Add some CHECK() constraints to both tables to keep them the right
size.|||Thank you all for the input, it is a great help. I really appreciate it.|||Agreed, for a production database it is not reliable, but for what he was
doing for development probably sufficient to get an approximate result
quickly and without jumping through hoops. Thanks for pointing out the
deficiency, as I should have done :-)
James K.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:j6br419b315b7tdp3526kdua5bqsb2cr8k@.
4ax.com...
> On Fri, 1 Apr 2005 10:29:07 -0500, -=JLK=- wrote:
> (snip)
> Hi James,
> Neat trick - but not very reliable. An IDENTITY column should not be
> used like this, as there is no guarantee that the identity values will
> always be contiguous. Indeed - if ever an insert is rolled back, there
> WILL be gaps in the range of identity values, and then your code will
> fail.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Database & Flash Wear Management

Hello everyone,

I'm currently developping a windows .net compact framework application which is basically a local datalogger.

Since my application will log data to the database (located on compact flash card) a few times a second over long period, I wonder if SQL Server Compact Edition offers some mechanism to reduce disk access.
By example, can SQL Server compact edition wait let's say 5-10 "insert into" commands before actually write to the database located on the flash card ?.

Any ideas which could help me to reduce flash wear would be greatly appreciated !

Thanks

The storage engine introduced in SQL Mobile (v3.0) and currently in SQL CE is storage-card aware.

If you feel you need to queue up inserts to further reduce writes to the card, that would be something that

you would need to code into your logger application.

My suggestion, instead of queuing your DML commands would be to periodically perform a Verify

and if needed a Repair on the database itself. See the SqlCeEngine documentation for samples

of how to perform these checks. Repair will reorganize the database (reorder indexes, reclaim

unused page space, etc) and int he process the physical file will be rewritten on the storage card.

Regards,

Darren Shaffer

|||

Thank you very much ! It's very appreciated

Regards,

Emmanuel