2

I'm trying to identify the best way to design a table for audit purposes. Basically I log several last events for many users in one table. There's a limit on the number of records for each user. The new records come, the older go.

Something like this:

CREATE TABLE Audit
(
  UserId INT NOT NULL,
  EventId INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  UpdateDate DATETIME NULL,
  -- Other data fields
)

The question is what to do with indexes. I'm thinking of creating the clustered index on (UserId, EventId). But since user activity happens independently it would mean inserts in the middle of the table and deletes in the middle of the table. Probably not good.

Another thought is to add an artificial AuditId field just to have new records get increasing numbers. Like this:

CREATE TABLE Audit
(
  Id INT, -- Becomes the clustered index
  -- The same as above
)

This way new audit entries will be appended to the end but deletes will still happen in the middle of the table. It's probably better than the first option but I'm not sure.

This table will be used frequently, basically every user activity is going to be logged (1 insert) and the oldest activity gets deleted in the same transaction (1 delete). This needs to be fast. Well, I want it to be instantaneous ideally and not noticeable performance-wise.

I also need to be able to retrieve the set of records for a particular user quickly. It can probably be covered by a non-clustered index.

I'm asking for advice in designing this table for optimal performance.

EDIT: I think I have missed something important to mention.

What I'm trying to track is not instantaneous in time but rather a period in time. There are several places in the system where I need this. Consider what the user is doing an activity of some sort that may span some period of time. If certain conditions are met then an existing activity is reused (refreshed, updated). I only wish to delete older sort of abandoned activities. For example, within 2 weeks one user may have issued like 50 of activities, but for another user to produce than many may take over a year. That's why I don't want a generally ordered log for all users together.

It is also not clear how should I cluster per datetime (as suggested). Do I do it on initial creation event or on the update event?

2
  • 1
    I think as you add things you forgot to mention, you need to give some tangible examples, and how much of this design is truly to benefit the performance of INSERT/UPDATE/DELETE and how much of it needs to cater to SELECT. Mar 5, 2013 at 20:17
  • I'm sorry I missed a thing (not intentionally). It's hard for me to give real examples as I'm designing a new system that hasn't been put into production yet. Like I said. There are several places where I need this design. Some activities are write only and read performance is not important. Others are more like rare writes with intensive reads.
    – buddy
    Mar 5, 2013 at 20:23

1 Answer 1

7

Typically to optimize inserts/deletes for such a table you would cluster on the datetime column. (You are tracking when these events happen, I presume.)

This way, inserts go to the end of the table always, minimizing the "bad" kind of page splits. Deleting or archiving data is easy because the clustered index will support range deletes, and these operations will not typically be locking the pages where you're performing inserts (unless you have escalation for some reason).

You will probably want other indexes like what you're describing to support queries, and maintenance of these of course will interfere somewhat with DML.

I'm not sure I understand why you only want to keep a certain number of rows for any UserId/EventId combination. If one user does a whole bunch of stuff today, do you really want to remove all the stuff they did yesterday, last week, etc., while you keep older data for users that are less active? Wouldn't it make more sense to just keep data based on time (e.g. retain two weeks of history)?

I'm also not sure why the delete absolutely needs to be coupled to the insert. Can't the delete be deferred to a background process instead of holding up the insert transaction?

9
  • Thank you for your answer. I'm reflecting on this. In the meantime I've updated my question, there was something I forgot to say.
    – buddy
    Mar 5, 2013 at 20:16
  • As to separating deletes from inserts, it's probably an option for the future. Right now it's just a test setup on my dev machine and obviously ANY design is fast. I'm planning for the future.
    – buddy
    Mar 5, 2013 at 20:29
  • All the more reason you should consider de-coupling them. Is there any technical reason why you have to delete a row every time you insert one? Mar 5, 2013 at 21:20
  • No technical reason whatsoever. Just thought it was logical to do it together. Perhaps I shouldn't even worry about performance until the application has operated for a period of time and amassed enough content to begin to manifest first signs of bottlnecking.
    – buddy
    Mar 5, 2013 at 21:26
  • There is a delicate balance between pre-emptive optimization and not caring about scale at all. In this case think about the two operations separately IMHO. There is no reason someone inserting an audit row should have to wait for cleanup of other, older data. Mar 5, 2013 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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