0

I have a table on a SQL server database that stores logging information about various user activity. Date/time, user id, and a few columns that specify where and what was done. None of this data is guaranteed to be unique (date/time will likely be unique, but it's far from guaranteed). This table will be written to often and searched rarely. I'd like to have it optimized for inserting, but at the same time be able to search the large quantities of data in as short a period of time as possible.

I'm still very new to sql server, if I was to have every column indexed, would that be a big performance hit with constantly adding new entries?

Edit: The problem is, it can be important to search for a lot of stuff. Some of the things this logs is who looks at important client personal information. So, ideally, you should be able to search for both which users accessed this specific information (select user where thing_done=looked at that data) as well as what a suspect user has been up to (select alldata where user=suspect). The speed of this is heavily dependent on those indexes though, right?

Improve this question

3 Answers 3

Reset to default
2

Q: If were to to have every column indexed, would that be a big performance hit?

A: Uh - yes :) It would :)

Don't do it :)

Q: I'd like to be able to search the large quantities of data in as short a period of time as possible

A: And that's precisely what you want to build your indexes on.

What are you likely to be "searching"?

I'm guessing date/time (a certain range of minutes/hours/days/etc) and some "type". For example, users might be "eating", "sleeping" or "studying".

You definitely want at least one index, and you definitely want to "strategically determine" what should be your "clustered index". Clustered indexes are a Sql Server thing.

You can learn more here:

Improve this answer
2
  • Ah, those are some good reads, thanks. The problem is, it can be important to search for a lot of stuff. Some of the things this logs is who looks at important client personal information. So, ideally, you should be able to search for both which users accessed this specific information (select user where thing_done=looked at that data) as well as what a suspect user has been up to (select alldata where user=suspect). The speed of this is heavily dependent on those indexes though, right?
    – cost
    Feb 18, 2012 at 2:13
  • If you can filter by other criteria (e.g. "where time between '2011-12-25' and '2012-01-02' and type=5 and user in ('Tom', 'Dick', 'Harry')", then it wouldn't necessarily benefit to index "user" as long as you can efficiently get the matching set for "time+type". IMHO...
    – paulsm4
    Feb 18, 2012 at 6:19
1

The mark of a good candidate for a clustered index is narrow (as few bytes as possible), unique, and ever increasing. If there's nothing like that in your data, adding an identity column just for this purpose wouldn't be a bad idea. In fact, this is a pattern I see time and time again in database design.

Improve this answer
2
  • I agree with the "narrow, unique and ever increasing" part. But in this case, the time might be sufficient. Provided there aren't too many records sharing exactly the same time value... It's probably not beneficial to add an id field if you're probably never going to use it in a "where" clause. IMHO...
    – paulsm4
    Feb 18, 2012 at 6:15
  • @paulsm4: having a good clustered index vs. having a heap (a steaming heap of data ......) is always a good idea - it speeds up all operations - yes, even INSERTs ! Kimberly Tripp explains this in great detail here - so the overhead of adding a 4-byte INT IDENTITY column is usually definitely worth it!
    – marc_s
    Feb 18, 2012 at 9:52
1

A few options:

  1. If almost all of your accesses will be writes, then you may not need an index at all. The resulting object is called a "heap".
  2. You can assign an identity column as the primary key, and let the DB handle creating them when you insert. It can be useful for certain types of queries, but doesn't help at all for others. It can be faster than a heap.
  3. If you can identify which columns you're most likely, you can use them for a clustered or nonclustered index. They don't have to be unique to be used as an index. However, having an index will slow down your inserts somewhat -- certain types of indexes are slower than others (such as ones that cause table fragmentation, including relatively random strings).
Improve this answer
4
  • I guess my big concern is, this thing will be written to a lot, and I don't want the logger adding any more overhead than necessary. At the same time, after a few months there could easily be a million+ entries, and if now we want to hunt something down, I don't want to wait a few minutes every time a search is run
    – cost
    Feb 18, 2012 at 3:04
  • So, it sounds like you need to make a trade-off: extra cost at insert time vs. extra cost at query time vs. implementation complexity. Depending on your architecture, if you have times of day when the DB isn't busy, you might be able to offload it into a second table with a fulltext index, or perhaps even to an Analysis Services cube.
    – RickNZ
    Feb 18, 2012 at 6:12
  • Actually, that may not be a bad idea to have a temporary table for a day's logs, and then in the middle of the night when the server is mostly idle, have them all added to the main table, where I can have most of the columns indexed. What would be the best way to combine the two? A merge or a join or something like that?
    – cost
    Feb 18, 2012 at 22:33
  • That's a good topic for another question, but briefly: if the source table can be large, you may want to enable BULK_LOGGED while you move the data over. Assuming the two tables have the same columns, then you can do something like DELETE FROM SrcTable OUTPUT DELETED.* INTO DestTable
    – RickNZ
    Feb 18, 2012 at 23:19

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.