In my Sql Server 2005 database, I'm considering two ways of creating a table to track daily views on 100,000 items:

Option 1) Each day would insert a new row for each item. So the table columns would look like this:

| ItemID | Date | #ofHits |  

That would mean the table grows by 100,000 rows per day.

Option 2) Each row would contain an entire month's hits per item. So the table columns would look like this:

| ItemID | Year | Month | Day01Hits | Day02Hits | Day03Hits | ... | Day31Hits |

That would mean the table grows by 100,000 rows per month.

Option 3) Perhaps you have a better idea altogether?

Keep in mind performance, such as when doing a sum of all hits between two dates (i.e. total hits for July 5th-Aug12th). Also, I'd want to keep at least 1 year of historical data.

On a related note, we're simply parsing the IIS logs nightly to get the hit data.

In summary:

Option 1 would probably be the easiest way to query and sum data across a date range. The downside is it would add 100,000 rows every day (or a million rows everyday if we had a million items).

Option 2 would create much less rows, but probably wouldn't be efficient to query and sum data across a data range.

Is there a better way to store daily hits for many items?

Unless someone has a better design, I'm considering this:
Use option 1, but create a new table every month (this will prevent the table from growing too large). So my table names would contain the year and month, like "Stats2012_01", "Stats2012_02", etc. Then, to query across dates (such as total hits from July 5th-Aug12th) it'd be:

SELECT ItemID, SUM(Hits)  
FROM (  
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_06 WHERE Day>=5 GROUP BY ItemID  
        UNION ALL  
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_07 GROUP BY ItemID  
        UNION ALL  
    SELECT ItemID, SUM(Hits) AS Hits FROM Stats2012_08 WHERE Day<=12 GROUP BY ItemID  
) s  

Update 2:
I just discovered partitioning, thanks to the suggested answer from MZDBA. I'll continue to look into partitioning, which may lead to combining Option 1 with partitioning.

  • 1
    Option 2 doesn't make much sense given your requirement to determine cumulative hits between any 2 dates. Your first option gives most flexibility.
    – StuartLC
    Oct 8, 2012 at 9:41
  • Option 2 is still possible to query between variables dates. Not efficient, but definitely possible (we do it that way already). Though option 1 is the most flexible, the downside is the huge amount of rows added every day (100,000).
    – Doug S
    Oct 8, 2012 at 10:08

4 Answers 4


I would want to attempt to insert a row for every hit and use SQL partitioning to store the table in managable chronological slices (by week or by month). This way, by keeping the original timestamp for each hit, you'll be able to aggregate the data using different methods as needed (by year one day, by hour the next, etc). For doing the aggregation, I would build a SSAS cube or Excel PivotTable, or T-SQL grouping functions. By using paritioning, you can store data for different time periods into individual filegroups spread across different disks to maintain scalability. When it comes to archival, you can perform what's called a sliding window to detach and later drop old unneeded time periods of data without expensive DELETE operations.

  • Partitioning! Aw, this may be the answer. In regards to inserting a row for every hit, I see the advantage of that for much more flexibility when aggregating data, but the downside is I'd be inserting several millions rows per day instead of 100,000 (at least it'd be at the end of each day when I parse the IIS logs). I'll have to weigh the benefits with the cons for my particular needs.
    – Doug S
    Oct 8, 2012 at 19:14
  • Try looking into bulk insert operations and either Simple or Bulk-Logged recovery models to cut back on the logging. Anything that can allow you to import the data in batches should improve in the efficiency.
    – MattyZDBA
    Oct 8, 2012 at 19:28
  • Much thanks. You've definitely sent me off in the right direction.
    – Doug S
    Oct 9, 2012 at 18:26

If performance of the site is a concern, then I would suggest going a third route.

Each time a page is viewed, insert a record into a table which is like:

ItemId | TimeStamp

Then, each night (or whenever your site is least utilised), have a process that counts the occurences of ItemId and builds a value for a table such as you have in Option 1.

Doing it this way will be much more efficient, as an insert is ridiculously cheap compared to trying to update on a table that has 36,500,000 rows and avoids issues with locking.

I'd also strongly suggest you hold this data not on your main database, so you can query it as you like without a performace hit; and consider splitting the tables into weekly or monthly reports to cut down on the amount of data that needs to be scrolled through.

  • Good One. But why even need the TimeStamp :)
    – Luftwaffe
    Oct 8, 2012 at 9:50
  • Mainly incase midnight isn't a good time for the system to do a count, but also to guard against instances where the count may fail.
    – TZHX
    Oct 8, 2012 at 9:52
  • We don't need to do this. The IIS logs are already tracking hits. We're doing a nightly parse of the logs to store in the DB.
    – Doug S
    Oct 8, 2012 at 10:00
  • In regards to your last suggestion of "consider splitting the tables into weekly or monthly reports", the trade-off is it'd be more difficult to sum the hits between two dates, if those dates span multiple tables (say if you wanted to sum all the hits for the past year). If I misunderstood or am missing something, definitely let me know.
    – Doug S
    Oct 8, 2012 at 10:24
  • It wouldn't be that much more difficult, really. It'd be fairly trivial to build queries calling multiple tables. But your question has fundamentally changed from what it was when I answered it.
    – TZHX
    Oct 8, 2012 at 10:44

With the introduction of Windowing and Pivoting functions from SQL server 2005, it makes even more sense to store the data in a proper normalized form. The 2nd structure can be achieved using queries when required, and is very hard to use at a "day" level, due to the gaps at 29-31 between months.

It also makes comparisons like weekdays vs weekends terribly difficult without unwinding the data to normal form using UNPIVOT.

  • Interesting. I had used Windowing before without knowing it was called that. Pivoting is a new concept to me. I looked it up, and looks like it'd be duct-tape for this situation, where a better design should alleviate the need for pivoting.
    – Doug S
    Oct 8, 2012 at 18:26

I'm currently working on this same issue.

Here is what I'm thinking about doing to keep the entries in the database low.

id | item_id | time_period | time_stamp | count

id = unique, auto increment id

item_id = the id of the item you are counting for

time_period = the time period that you want to hold that count for
   options would be something like: (year, month, week, day, hour, minute)

time_stamp = the date that would match the time_period:
  Y, Y-m, Y-m-d, Y-m-d-H, Y-m-d-H-i

count = the tracked count for this time period that gets updated.

Your Answer

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

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