CODEWITHSUNDEEP
sqldatabaset-sql
Evik James
Evik James

Reputation: 10483

How to best store and aggregate daily, weekly, monthly visits for quick retrieval?

I am using SQL Server 2008 and ColdFusion 9.

I need to log visit to my web site. This will be for users who are logged in. I need to be able to retrieve how many times they have logged in this week, this, this year and as well as how many consecutive days, very much like how StackExchange does it. I want to be able to show a calendar for any month and display the days that the visitor visited.

I am not sure of the best way to store this data or retrieve it. My initial thought is to create a daily or weekly table that records every hit by every user. I would store the UserID and timestamp like this.

TABLE_VISITS_LAST_SEVEN_DAYS
UserID     VistitDateTime
101        2012-10-06 01:23:00
101        2012-10-06 01:24:00
101        2012-10-07 01:25:00
102        2012-10-07 01:23:00
102        2012-10-07 01:24:00
102        2012-10-07 01:25:00

At the end of each day, I would determine who visited the site and aggregate the visits to essentially remove duplicate info. So, I will delete this above data and insert it into a table that would only store this data:

TABLE_VISITS_ALL_TIME
UserID     VistitDate
101        2012-10-06
101        2012-10-07
102        2012-10-07

This data would be easy to query and wouldn't store any unnecessary data. I'd have all of the data that I need to determine how frequently the user visits my site with not much effort.

Is this a good plan? Is there an easier or better way? Does my plan have a gaping hole in it? Ideas would be appreciated.

Upvotes: 1

Views: 2522

Answers (3)

eaolson
eaolson

Reputation: 15094

Why not just store each visit and if you need daily/weekly/whatever statistics create a query that aggregates as needed? It all depends on how many visits you're expecting and what time period you want to retain statistics for.

Edit:

It sounds like you suggesting that designing it poorly is just fine as long as I've got a fast server. Is that right?

That's not what I'm saying at all. Your first solution is not a poor solution. Your second solution is not "better". If anything, it is somewhat denormalized.

There is no "best way" to do what you've described. There are multiple possible solutions, some of which may be adequate for your needs and some of which may not.

  1. If you are interested in statistics, like how often individual users visit your site and how many times a day and when, your first table tells you that. This comes with some additional overhead when doing aggregation.
  2. If all you will ever care about is whether a user visited your site on a given day, why not store just that information? Insert one row on a user's first visit that day and don't do that again until tomorrow.

Whether or not the additional overhead of recording one row per visit is too much will depend on your exact application. A small site that gets a few thousand hits per month is not the same thing as a massive site like Amazon.

Furthermore, there's multiple ways to do even the first solution. How are the indexes set up, etc. Why not just do it and see if it works? Create a table, insert what you think will be a typical amount of data and give it a try. If it's not performant enough, then worry about other aggregating tables and nightly jobs and such.

... premature optimization is the root of all evil. -- Donald Knuth

Upvotes: 1

John Dewey
John Dewey

Reputation: 7093

You could change the VisitDateTime column declaration in TABLE_VISITS_LAST_SEVEN_DAYS to VisitDate as Date, and then log each visit in a manner like this:

INSERT INTO TABLE_VISITS_LAST_SEVEN_DAYS 
SELECT @UserID, @VisitDate
WHERE NOT EXISTS (
  SELECT 1 FROM TABLE_VISITS_LAST_SEVEN_DAYS (NOLOCK)
  WHERE UserID=@UserID AND VisitDate=@VisitDate
)

(@VisitDate is a Date type variable)

Upvotes: 1

Ben
Ben

Reputation: 52863

I don't understand the need for the two tables. The second one is simply a de-duplicated version of the first; any aggregate queries you do will still have to do the same index scans, just on a slightly smaller table.

Personally I think it would make more sense if you created your first table, but put a unique index on userid and the yyyy-mm-dd part of visitdatetime ( though visitdate might now be more appropriate ). If you have a duplicate entry catch the exception and ignore it.

Then your first table becomes your second by definition and you don't need to do any extra work in the background.

The major problem with this method would be that if you ever wanted to count the number of time someone logged on in a single day you couldn't.

Upvotes: 1

Related Questions