Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jgallen23
Starting Member
17 Posts |
 Posted - 2006-11-15 : 14:48:15
|
| I have a website, similar to digg.com, that users can post items and the items can get "dugg".What is the best way to calculate user stats (such as total diggs, items submitted). If a lot of users hit the site, calculating everything on the fly every time could be quite a load on the database, but on the other hand, running a sql job every 15 mins means that the stats aren't up to the second and could there be issues when truncating the stats table, a new user could join and try to add to that table? Anybody run in to this situation before?Thanks |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-15 : 15:25:33
|
| You're describing a very common scenario and how you solve it depends on the application. Depending on the numbers involved it may be fine to run queries to count the records, but if that's going to be slow you'll need to store the numbers pre-aggregated. You wouldn't want to update the aggregates with a process that runs every 15 min though because as you noted, the numbers will be out of date, but also - that process you run every 15 min will make the whole system very slow every 15 min. You want to use stored procedures to update the database and in the stored procedures update the aggregates. So in a stored procedure that adds a new digg, you'd update that user's digg total. You could consider triggers for this too although in most cases stored procedures should be better. |
 |
|
|
jgallen23
Starting Member
17 Posts |
Posted - 2006-11-15 : 16:35:56
|
| snSQL, thanks, that makes sense. Should I use triggers to update the aggregates? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-15 : 16:51:07
|
| I'd say no - rather create stored procedures and only use those stored procedures to update the data. |
|
|
|
jgallen23
Starting Member
17 Posts |
Posted - 2006-11-15 : 16:52:39
|
| ok, are there any drawbacks to triggers? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-15 : 17:14:47
|
| No not really - but you should constraints instead of triggers when possible because they perform better. In this case you can't do what you need with constraints, so it's either procs or triggers. Triggers would work fine but given that you're just starting out, I think you may find that triggers will have some side effects that will be more difficult for you to understand than procs at this stage. |
|
|
|
|
|
|
|
|
Calculating User Stats and Rankings