StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok, a couple of things for audit log tables.</p> <p><strong>For most applications, we want audit tables to be extremely quick on insertion.</strong></p> <p>If the audit log is truly for diagnostic or for very irregular audit reasons, then the quickest insertion criteria is to make the table physically ordered upon insertion time.</p> <p>And this means to put the audit time as the first column of the clustered index, e.g.</p> <pre><code>create unique clustered index idx_mytable on mytable(AuditDateTime, ID) </code></pre> <p>This will allow for extremely efficient select queries upon AuditDateTime O(log n), and O(1) insertions.</p> <p>If you wish to look up your audit table on a per CustomerID basis, then you will need to compromise.</p> <p>You may add a nonclustered index upon (CustomerID, AuditDateTime), which will allow for O(log n) lookup of per-customer audit history, however the cost will be the maintenance of that nonclustered index upon insertion - that maintenance will be O(log n) conversely.</p> <p>However that insertion time penalty may be preferable to the table scan (that is, O(n) time complexity cost) that you will need to pay if you don't have an index on CustomerID and this is a regular query that is performed. An O(n) lookup which locks the table for the writing process for an irregular query may block up writers, so it is sometimes in writers' interests to be slightly slower if it guarantees that readers aren't going to be blocking their commits, because readers need to table scan because of a lack of a good index to support them....</p> <hr> <p>Addition: if you are looking to restrict to a given timeframe, the most important thing first of all is the index upon AuditDateTime. And make it clustered as you are inserting in AuditDateTime order. This is the biggest thing you can do to make your query efficient from the start.</p> <p>Next, if you are looking for the most recent update for all CustomerID's within a given timespan, well thereafter a full scan of the data, restricted by insertion date, is required.</p> <p>You will need to do a subquery upon your audit table, between the range,</p> <pre><code>select CustomerID, max(AuditDateTime) MaxAuditDateTime from AuditTrail where AuditDateTime >= @begin and Audit DateTime <= @end </code></pre> <p>and then incorporate that into your select query proper, eg.</p> <pre><code>select AuditTrail.* from AuditTrail inner join (select CustomerID, max(AuditDateTime) MaxAuditDateTime from AuditTrail where AuditDateTime >= @begin and Audit DateTime <= @end ) filtration on filtration.CustomerID = AuditTrail.CustomerID and filtration.AuditDateTime = AuditTrail.AuditDateTime </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload