General SQL Server query performance

Question

Hey guys,

This might be stupid, but databases are not my thing :) Imagine the following scenario. A user can create a post and other users can reply to his post, thus forming a thread. Everything goes in a single table called Posts. All the posts that form a thread are connected with each other through a generated key called ThreadID. This means that when user #1 creates a new post, a ThreadID is generated, and every reply that follows has a ThreadID pointing to the initial post (created by user #1). What I am trying to do is limit the number of replies to let's say 20 per thread. I'm wondering which of the approaches bellow is faster:

1

I add a new integer column (e.x. Counter) to Posts. After a user replies to the initial post, I update the initial post's Counter field. If it reaches 20 I lock the thread.

2

After a user replies to the initial post, I select all the posts that have the same ThreadID. If this collection has more than 20 items, I lock the thread.

For further information: I am using SQL Server database and Linq-to-SQL entity model. I'd be glad if you tell me your opinions on the two approaches or share another, faster approach.

Best Regards,

Kiril

Answer 1

Option 2 will be fine so long as you have an index on ThreadID. Option 1 would also be fine (assuming you have an index on ThreadID), but the code would be more complex I think.

Answer 2

In my opinion, this reply counter is not part of a post, neither a thread. This is a system parameter which would fit into another data table. Let's call it SysParams, for the sake of this example.

CREATE TABLE SysParams (
    SpId int IDENTITY(1, 1) primary key
    , SpTableName nvarchar(20) NOT NULL -- To what table this parameter applies?
    , SpName nvarchar(10) NOT NULL  -- Parameter name
    , SpValue nvarchar(20) NOT NULL -- Value of the parameter
)

insert into SysParams (SpTableName, SpName, SpValue) values (N'Posts', N'MaxAnswersPerThread', N'20')
GO

Then, when loading your posts or threads, you're loading only once this parameter from the SysParams table and lock if the count of answers is equals to this parameter value.

Why a nvarchar(20) for the SpValue field?

Because doing so, you might as well have some other parameters which would consist of string values.

As for getting these values, you shall code a GetParameterValue() function which would simply return the value of the SpValue field depending on the SpTableName and SpName specified on the function call. This way, you might as well have other parameters for the same table serving different purposes.

Answer 3

Been there and have done variants of both solutions.

I personally don't like solution 1 because the counter column doesn't have any meaning for all the reply posts.

I usually end up with

3

Create two tables, one for the Threads (thread starters) and one for the Posts (thread replies).

Often you'll find that for a Thread there are more fields than for a post. For example, you could add a column IsLocked to the Threads table. You then won't have to remember the magic number (20) to know whether the Thread is locked or not.

I often also have a Title for the thread, but not for the posts. And sometimes another column to know whether the thread IsSticky or not. And so on...