Data timeline performance in Sql Server

Question

I have a table with three columns:

user varchar, status varchar , rep int

status and rep get updated often and one of the requirements is:

Given a datetime value view status and rep values, for each user, at the given date.

I can add an updated_at datetime column to the table and insert new rows whith the same user instead of update existing ones, but the table gets large and I need to join the query results to similar results from other tables, thus I need a fast query.

What is the best performing solution to this problem in SQL Server?

Answer 1

Depending on exactly how you are going to use the reporting information, it might be best to create a History table that before you update the main table, you write the current values off to the History table, adding the timestamp.

That would be best if you are really only picking out specific days/users/statuses from the history

Answer 2

I would store this is a separate related table since I assume most of the time you will only be querying the current status when you join. If you will be doing a lot of this type of querying and adding this type of data to many tables not just this one, I would create a separate OLAP database for reporting.

Incidentally as long as you are doing this, you might consider if you want to record who made the change as well. Then you have basically built an auditing solution.

Answer 3

One option for this would be to use two separate tables: a "current values" table, and a history table. The current values table would contain the only latest values for each user, which would keep it small and easily-joinable. The history table would contain the three columns you listed as well as the timestamp column, and would track the changes to the values over time. Anytime you wanted to change a value in the current values table, you would simultaneously add a row with the new values to the history table, using gettime() as the timestamp.