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 |
|
JokerOfACoder
Starting Member
18 Posts |
 Posted - 2007-05-09 : 12:29:28
|
| Let's end it here. Point out why or why not you should use static or dynamic counts.Almost every forum out there use static counts.Like columns for postcount, topiccount etc.Why not use dynamic count? I'm designing a forum architecture and need to know whether to go the conventional way of using static columns (postcount,topiccount etc) vs dynamic counts.If I use static columns, I would have to use transactions to make sure the columns are accurate.An example of static column design:[category]catidnameforumcountpostcountviewcount[Forum]forumidnamepostcounttopiccountviewcount[post]idnamepostcountviewcountobject model:void UpdateCategory(Cat cat, transaction trans)void UpdateForum(Forum forum, transaction trans)void UpdatePost(Post post, transaction trans){MySqlCommand comm = new MySqlCommand();comm.BeginTransaction();//save post;//new post?if( post.NewPost == true )UpdateForum((post.Forum.PostCount+1), comm);UpdateCategory((post.Forum.PostCount+1), comm);}So the final debate.....if you were desinging a forum architecutre:use static counts or dynamic? why? |
|
|
JokerOfACoder
Starting Member
18 Posts |
Posted - 2007-05-09 : 15:10:28
|
| Also, do you agree with what is said here?http://forums.mysql.com/read.php?10,152560,152563#msg-152563 |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-09 : 15:47:45
|
| It depends on performance. Ideally, you would calculate counts on the fly, but if that is too slow, you store them. Never prematurely optimize; design your database and app using a good, solid normalized model first, and then if performance becomes a problem, you can consider caching certain elements or values to speed up things. But of course, speeding up counts means that you slow down and/or potentially have locking issues with updating those static counts each time posts are added or removed. Moving posts from one forum to the next or similar things, or bulk imports or deletes become more troublesome as well.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-09 : 16:27:54
|
for me the question would be, does the world really need another forum package? there's even one called YetAnotherForum. says to me the market is pretty well saturated. 
www.elsasoft.org |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-05-09 : 20:12:52
|
quote:
Originally posted by JokerOfACoderIf I use static columns, I would have to use transactions to make sure the columns are accurate.
When do you not use transactions? I think you need to cover the fundementals before banging on about 'architecutre'! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-09 : 21:56:12
|
| This question seems more appropriate for the "Database Design and Application Architecture" forum, than the "Transact-SQL (2005)". Not that you should post it there.I think that the real question has to be, under what application conditions does a static count improve performance over calculating it dynamically? Only experience (or testing) can tell you that. Maybe it is better to build the application to be able to do it both ways, and give the site admin the ability to turn it on when it will help.My personal wild-ass guess is that performance would slow down as the number of posts increases if you use a dynamic count. The overhead of a single update of the post count with every post is probably more predictable than the potential of scanning a table with 380,000 rows to find out Tara has a post count of 16,200, over 4% of the total.Another point to consider is that you may not want to actually keep all those old posts, so you would have to have a static count to maintain an accurate count after you purge off the old data.If most of the major players are doing it one way, perhaps experience has already shown what works best under most conditions. This seems like a question that would be good to ask on one of their support forums. You might get an answer from someone who has actual experience developing forum software. That doesn’t guarantee a correct answer, but experience counts.The question of whether or not to use transactions only comes up if you are using a toy, pseudo-RDMS not to be named here. With SQL Server you cannot avoid them, but you can do a bad job of handling them.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 04:10:50
|
| Its an interesting theoretical exercise!One other technique that might be worthwhile is to "flag" the count as invalid, and then to dynamically count (and save) next time it is required.I'm not sure this is useful in this situation, but for example maybe it is easier to just reset the Count column to -1 if a new post is made (or perhaps Delete the PostCount record entirely, so that the next request finds no row, and does a dynamic count), rather than "adjust it" with full transaction posting etc. In practice this will probably mean that concurrent threads re-calculate the same total, but that may be a minor irritation. There might still be a window-of-opportunity for a stale dynamic count to overwrite a "flag it as stale" update:User A: Insert new postUser A: Flag as StaleUser B: Display post, Count is stale, COUNT(*) the table ...User C: Insert new postUser C: Flag as StaleUser B: Update Count column / record -- This is now an incorrect postIf you go down this route, or indeed any route that has a stored count, you could put in a debug mode that did a dynamic count in duplicate, and raised an error if it was different to the stored count. That would bring to light any edge-conditions, and you could disable that code once you were confident no errors had been raised.Kristen |
|
|
|
JokerOfACoder
Starting Member
18 Posts |
Posted - 2007-05-10 : 09:43:13
|
| According to Joe scello (or however you spell his last name, he helped designed the sql language), he states that you should NEVER flag a column (you should always use predicates to compare).The solution you recommend Kirsten, while it would work, would not be...efficient for a large application.I originally started this DB structure out in MySql. A count of 4 million rows took 10 seconds.After converting to MS SQL 2005, the same count took 2 seconds. I was shocked. Sequential counts took 0.02 second (same as Mysql, both cached data).Changing the where clause on the COUNT statement resetted MySql and caused it to delay in returning the count (took about 4 seconds) while in MS SQL 2005, it took 1 second.I'm impressed by MS SQL 2005. I like the idea of dynamic counts because theres less maintenance, and now I'm some "number statistics" of how long a dynamic count takes a on a very large table, I think it's safe to assume that a dynamic query is better than a static one. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 09:55:02
|
| "The solution you recommend Kirsten, while it would work, would not be...efficient for a large application"As ever it all depends. If you cannot easily kept a running tally of posts, and you often do not need the count before another record is added (or removed) then I think a flag would be just fine.If you can keep tally then better to just adjust it - so insert a post = increment the count (but presumably in several places User's post count, Forum post count, Topic post count ...).If you can't adjust the count and you will always need the count immediately then don't flag it, calculate it there & then.Kristen |
|
|
|
JokerOfACoder
Starting Member
18 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-05-10 : 21:25:21
|
| One trouble with running totals is you get a lot of contention on the rows concerned. You will probably be ok with some of what you propose but the counts on forum essentially mean you're adding one at a time instead of in parallel. I got caught out early in my database carreer by keeping an account balance. Unfortunately every transaction in the application hit the institutions house account as well as the client's so every transaction was serialised on the house running total. Not good for throughput to say the least. |
|
|
|
|
|
|
|
|
Static Count vs Dynamic Count, the final debate