0

I have a system with an Events table that holds a list of events to process. Once they are processed, their status field is marked such, and they remain there for archive/history purposes. The PK of the Events table is identity.

The Events table is frequently queried for event type, due time, etc. Most of the time only unprocessed events are of interest. But due to the number of records constantly growing, these queries start taking up more time than they should.

So, I'd like to implement a process that regularly brings the old processed events to another table, so that the primary events table remains lean. Ideally, I'd like that other table be named like EventsArchive and keep exactly the same field setup, with one difference that the PK field is not identity. However, now there is a risk that upon inserting new record in Events table, the PK value is something that's already used in the EventsArchive table.

Is there any way how I can guarantee that the PK field values of Events table do not collide with the archived values in EventsArchive table? I can imagine switching to finding the Events.PK value by means of generating random long value and checking if this value is not taken in both Events and EventsArchive. But that doesn't sound right.

3
  • 1
    First of all, you should consider Filtered Index based on status. It looks like it will be ideal solution for this case. Oct 4, 2017 at 12:40
  • What is the datatype of your primary key in EventsArchive table? Oct 4, 2017 at 12:41
  • 3
    "However, now there is a risk that upon inserting new record in Events table, the PK value is something that's already used in the EventsArchive table." How could that happen? The EventID is an identity column and the EventsArchive gets data only when you transfer them from Events. How can there be a collision, ever? Oct 4, 2017 at 12:57

3 Answers 3

1

As others have said, this is an ideal use case for a filtered index. But in order to satisfy your curiosity, you have a few options.

The EventArchive table could have an additional field "date-inserted" and just populate that when you add records to the EventArchive table. the combination of primary key (from the Events table) and the date-inserted should be unique.

I assume you are going to truncate the Events table after each migration? You could also specify the next identity value to use with the DBCC CHECKIDENT command https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql.

In your case, it would be DBCC CHECKIDENT (Events, RESEED, "NextIdentityValue")

But if you simply delete the items in the events table (do this in a loop with commits and/or log backups in between loops to avoid growing your log file out of control) then your identity value won't change.

1

For performance in this scenario, you should consider Filtered Index.

It looks like it is ideal solution for your case. Just filter only events with appropiate status and index will be small and fast.

Other pros will be minimal effort to implement this solution (add new index).

0

I cant understand how can newly inserted EventID in the Main Events table already exists in the historical table.

---- creating Events table
CREATE TABLE #Events (
EventID INT IDENTITY PRIMARY KEY CLUSTERED, 
EventStatus INT, -- 1 processed, 0 not
EventName VARCHAR(50)
)
GO 

------creating historical table

CREATE TABLE #EventHist(
EventID INT, 
EventStatus INT, 
EventName VARCHAR(50)
)
GO

---- population samle data to events. All the events are not processed yet.
INSERT INTO #Events
        ( EventStatus, EventName )
VALUES  ( 0, -- EventStatus - int
          'Event1'  -- EventName - varchar(50)
          )
,( 0, -- EventStatus - int
          'Event2'  -- EventName - varchar(50)
          )
          ,( 0, -- EventStatus - int
          'Event3'  -- EventName - varchar(50)
          )
          ,( 0, -- EventStatus - int
          'Event4'  -- EventName - varchar(50)
          )
GO

--- Processing 2 events

UPDATE #Events
SET EventStatus = 1
WHERE EventID > 2

--- after this step we need to pop the processed events from the main table and put them in the historical table

INSERT INTO #EventHist
        ( EventID, EventStatus, EventName )
SELECT EventID, EventStatus, EventName 
FROM #Events
WHERE EventStatus = 1

DELETE FROM #Events
WHERE EventStatus = 1

----- insert new events and make sure that the new value does not exist in the historical table

INSERT INTO #Events
        ( EventStatus, EventName )
VALUES  ( 0, -- EventStatus - int
          'Event5'  -- EventName - varchar(50)
          )
          ,( 0, -- EventStatus - int
          'Event6'  -- EventName - varchar(50)
          )
GO

SELECT *
FROM #Events

EventID     EventStatus EventName
----------- ----------- --------------------------------------------------
1           0           Event1
2           0           Event2
5           0           Event5
6           0           Event6

SELECT *
FROM #EventHist

EventID     EventStatus EventName
----------- ----------- --------------------------------------------------
3           1           Event3
4           1           Event4
2
  • 1
    It can't, assuming the OP deletes the events table as you did. But if he truncates the events table, that will reset the identity and thus produce duplicates. As a side note, assuming that the events table is large, I would definitely delete in batches to keep the load on the transaction log to a minimum. Oct 4, 2017 at 14:30
  • 1
    I can see any truncate operation in the question. But if data is deleted from the main table with truncate, he can reseed the table after that. Declare @seed int = (select max(EventID) from EventHist) dbcc checkident(Event, Reseed, @seed). Oct 4, 2017 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.