2

I'm using an SQL database with C# to store events. My table name is TabOfEvents; it has the code of the event and the date and time of this event.

CREATE TABLE [dbo].[TabOfEvents] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [cta] NCHAR (10) NOT NULL,
    [code] NVARCHAR (MAX) NOT NULL,
    [date] DATETIME2 (7) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
)

Each time an event happens I save it in my table, ordered by date (from the newest to oldest).

I want to limit the number of lines in my database say 100.000 lines, (or by the size of my database say 300Mo for example, but I'm not sure about this one if it's possible) and when the limit number is reached, I want to overwrite the oldest events and replace them with the new ones.

How can I do that with C#?

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
lawir
  • 55
  • 9

3 Answers3

2

Don't do it this way. You can do this instead:

  • Put a retention policy and/or an archive policy for your data and your events. So for example, you can archive or delete events older than x months or days. You can archive them in a different tables/ or a different database. Or,

  • You can put rules on your application's business layer for how much events are allowed. This way you can control and limit the events and your data the way you like.

So for the second option, the max events count can be stored in something like config table in your database or in config file (in app.config, or web.config) something. If you search online you can find how to read and write in config file.

Then in your application before doing the insert, get the count of the events from the database like:

SELECT COUNT(*) FROM events;

Then compare this result with the value from the web.config. If the value >= the max value reject the insert with an error message otherwise do the insert.

  • i like the second idea, can u give more details please ? – lawir Dec 24 '17 at 16:34
  • @lawir in your insert logic, check how many events exists, and then allow or prevent the insert. You can do this in a database trigger. Also, if these are per user, you can give each user a quote and the same idea, you can allow or prevent the insert. –  Dec 24 '17 at 17:48
  • i tried to think like that then i stoped for many reasons.the most important how am i gonna do an if estatement to my Id(primery key) to limit it ? – lawir Dec 24 '17 at 19:11
  • @lawir - You will just stop the insert from the application layer: `if count of events > MaxNumber then insert new event else stop the insert`. –  Dec 24 '17 at 20:04
  • if i use a counter, im gonna have to store his value too because i will loose his information if the appli is closed, that's other work too right . – lawir Dec 25 '17 at 08:31
  • @lawir You don't need a counter, just select count of the events from the database. For max value allowed, you can store it in a config file. –  Dec 25 '17 at 09:23
  • @ Kennard - i don't know anything about this count of events, could u show me please how to use it and store it ? – lawir Dec 25 '17 at 10:58
0

You can do this with a trigger. However, it probably is not necessary.

Deleting rows in a table does not automatically reclaim space. Database tables do not work the same way the files do. Deleting rows can also be a (relatively) time-consuming operation, because of the logging, locking, and index re-organization.

From a performance perspective, a well-designed database should have no trouble handling millions of rows of data -- for many common types of queries.

If you do want to limit the size of the table, I would recommend using a partitioning scheme. Then use a scheduled job to drop old partitions. Dropping partitions is much more efficient that dropping individual rows. And, recovering space from dropped partitions is trivial.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i do have to limit my database cause i can't store data forever right ? and what do you mean by partitioning scheme? you want me to declare more than one table ? – lawir Dec 24 '17 at 16:03
  • and when you're saying deleting rows in table doesn't reclaim space, do you mean even when i delete the row i don't free space? – lawir Dec 24 '17 at 16:06
  • Of course you can store data "forever" (for reasonable values of "forever"). Database operations use space. If you are in a severely space-limited environment, then you have other considerations as well (such as log space and page fragmentation). – Gordon Linoff Dec 24 '17 at 20:39
0

You could use a sequence with the CYCLE option used as pointer in the table used as ring buffer. This creates a sequence object in the database:

CREATE SEQUENCE BufferPtr  
    START WITH 1
    MINVALUE 1
    MAXVALUE 100000
    CYCLE;

Create the table differently to allow empty entries and to allow entering Ids

CREATE TABLE dbo.TabOfEvents (
    [Id] INT NOT NULL,
    [cta] NCHAR (10) NULL,
    [code] NVARCHAR (MAX) NULL,
    [date] DATETIME2 (7) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
)

Then fill the table with 100000 empty records and Ids 1 .. 100000. We do this to know the number of records in advance and to save ourselves a query. I.e., we do not have to query the number of records with SELECT COUNT (*) FROM TabOfEvents to know if we must make an insert or an update. The Sequence is used in the UPDATE command itself to determine which record we update. A new sequence number is generated each time and after the limit 100,000 is reached the sequence starts over at 1.

-- This creates 100000 empty records with an Id. The code is quite tricky and I won't explain
-- the details here. If you prefer, create a stored procedure with a FOR-loop or do it in an
-- external application (e.g. C# Console app). Performance is not important, since we are
-- doing it only once.

;WITH e1(n) AS
(
    SELECT 1 FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0)) t(n)
)
INSERT INTO dbo.TabOfEvents (Id)
SELECT ROW_NUMBER() OVER (ORDER BY n10.n) AS n
FROM
e1 n10
CROSS JOIN e1 n100
CROSS JOIN e1 n1000
CROSS JOIN e1 n10000
CROSS JOIN e1 n100000

Now the table and the sequence are set up and you can update the records with

UPDATE TabOfEvents
SET cta = 'test', code = 'xxx', [date] = SYSDATETIME()
FROM
    TabOfEvents
    INNER JOIN
    (SELECT
        NEXT VALUE FOR BufferPtr AS Ptr
        FROM (VALUES (1)) t(n)
    ) a
    ON TabOfEvents.Id = a.Ptr;

You can see a test here (http://sqlfiddle.com/#!6/2679e/5/2) using only 4 records. Click Run SQL repeatedly and you will see how events are added in a cycle.

Note: My first attempt was to use UPDATE TabOfEvents SET cta = 'test', code = 'xxx', [date] = SYSDATETIME() WHERE Id = NEXT VALUE FOR BufferPtr, but SQL-Server refuses to query a sequence in the WHERE clause. Can my UPDATE statement be simplified?

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • indeed a buffer is what i need, it can manage delete itself perfect for what i need, but i didn't understand how can i implement it, is it an sql query? or in my application? – lawir Dec 24 '17 at 16:45
  • The buffer would always have the same size. So deletes would not be necessary anymore. `CREATE SEQUENCE` would be needed to setup the database (I assumed SQL-Server syntax). The `UPDATE` is the query you need to enter events into the table. It replaces entries in a cyclic way. – Olivier Jacot-Descombes Dec 24 '17 at 16:52
  • i use insert into query to enter events into my table, the update query is needed when i want to update a row precisely which is not what i need. as regard for the create sequence, maybe it's because im very new with database and programming c# , but i don't know any query which can do that,and i really don't know where and how to create this sequence, can you be more specific please? – lawir Dec 24 '17 at 17:05
  • The Idea of the cyclic buffer is that you are always overwriting the oldest entry. Therefore you are not using `INSERT` but `UPDATE`. The sequence is part of the [Data Definition Language (DDL) Statements (Transact-SQL)](https://technet.microsoft.com/en-us/library/ff848799%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396). You (or a database administrator) would enter it in the SQL-Server Management Studio to create the sequence object; however, I still do not know which database you are using (SQL-Server, Oracle, MySQL, others?). – Olivier Jacot-Descombes Dec 24 '17 at 17:18
  • @ Olivier, Im using sql-server . could you show me an exemple please? – lawir Dec 24 '17 at 18:56
  • Well, I did. My post contains links to the sequence documentation and to a ring buffer article as well as a statement to create a sequence and an update statement using the sequence to insert events. If you want more concrete examples, show the schema of your table. – Olivier Jacot-Descombes Dec 24 '17 at 19:16
  • CREATE TABLE [dbo].[TabOfEvents] ( [Id] INT IDENTITY (1, 1) NOT NULL, [cta] NCHAR (10) NOT NULL, [code] NVARCHAR (MAX) NOT NULL, [date] DATETIME2 (7) NULL, PRIMARY KEY CLUSTERED ([Id] ASC)) – lawir Dec 24 '17 at 19:51
  • sorry the program showed up like that, does it help ? So if i understood, you want me to create the sequence just right below the definition of the table? – lawir Dec 24 '17 at 19:53
  • @ Olivier, Thank you so much, it works perfectly . i'm still trying to figure out how this program works because i'm still very new at SQL Query. Thank you again . – lawir Dec 26 '17 at 09:27
  • @ Olivier, i have a question, why do we have to use " update" instead of " insert into" ? – lawir Dec 26 '17 at 17:52
  • Because all the 100,000 records are already existing in table `TabOfEvents`. First, except the `Id`, the columns are all empty, The `;WITH e1(n) AS ...` statement creates these empty records. When we want to enter events, we just overwrite (UPDATE) the existing records. Because the sequence (which is used to determine the next `Id` to be used) is cyclic, after it reaching 100,000 it starts over at 1. Then the UPDATE begins to overwrite the oldest entries. That way the size of the table never changes. Read about ring buffers or cyclic buffers. – Olivier Jacot-Descombes Dec 26 '17 at 18:01
  • @ Olivier, I understand that, you're saying we're using UPDATE because we already have the existing empty records, but what i'm trying to say is why do we have to create the empty records in the first place ? can't we just not create the empty records and use INSERT INTO instead ? – lawir Dec 27 '17 at 10:22
  • If we do not create empty records, we have to check whether we reached the limit before inserting new records. I.e., we have to make one query for the check (`SELECT COUNT(*) FROM TabOfEvents`) and then one query for either inserting or updating. If we have 100,000 from beginning, we do not have to make the check, since we know the number of records in advance (it does never change). – Olivier Jacot-Descombes Dec 27 '17 at 14:27
  • @ Olivier, I understand,Thank you for your answer. i would like to see how to do it with insert too , if you know where i can found it. – lawir Dec 28 '17 at 10:15
  • Basically, if COUNT(*) < 100000 then insert else update. But this does not save you much, as the estimated table size with 100,000 records is only about 10 MB (depends on avg text size of `code` column). See: [Estimating SQL Server Table Sizes](https://mlichtenberg.wordpress.com/2013/10/16/estimating-sql-server-table-sizes/). – Olivier Jacot-Descombes Dec 28 '17 at 13:18
  • @ Olivier, you're right, after a second thought it doesn't help that much. Thank you for all your explanation it was a huge help . – lawir Dec 28 '17 at 14:48