I have a large database table (SQL Server 2008) where i have all my forum messages being stored (The table currently have more than 4.5 million entries).

this is the table schema:

CREATE TABLE [dbo].[ForumMessage](
    [MessageId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [ForumId] [int] NOT NULL,
    [MemberId] [int] NOT NULL,
    [Type] [tinyint] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [Subject] [nvarchar](500) NOT NULL,
    [Body] [text] NOT NULL,
    [Posted] [datetime] NOT NULL,
    [Confirmed] [datetime] NULL,
    [ReplyToMessage] [int] NOT NULL,
    [TotalAnswers] [int] NOT NULL,
    [AvgRateing] [decimal](18, 2) NOT NULL,
    [TotalRated] [int] NOT NULL,
    [ReadCounter] [int] NOT NULL,
 CONSTRAINT [PK_GroupMessage] PRIMARY KEY CLUSTERED 
(
    [MessageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

One issue that i see keep coming back is that when i'm running my stored procedure that select a message and all its replies, i get sometime time-outs errors from the SQL server.

This is my stored procedure:

select fm1.[MessageId]
      ,fm1.[ForumId]
      ,fm1.[MemberId]
      ,fm1.[Type]
      ,fm1.[Status]
      ,fm1.[Subject]
    ,fm1.[Body]
      ,fm1.[Posted]
      ,fm1.[Confirmed]
      ,fm1.[ReplyToMessage]
      ,fm1.[TotalAnswers]
      ,fm1.[AvgRateing]
      ,fm1.[TotalRated]
      ,fm1.[ReadCounter],
     Member.NickName AS MemberNickName, Forum.Name as ForumName
from ForumMessage fm1 LEFT OUTER JOIN
                      Member ON fm1.MemberId = Member.MemberId INNER JOIN
                Forum On fm1.ForumId = Forum.ForumId
where MessageId = @MessageId or ReplyToMessage=@MessageId
order by MessageId 

the error that i get look like this: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I was looking on the execution plan, and the only this that look suspicious is that is see that the query has a cost of about 75%-87% (it varies) on the key lookup in the forummessage table (which i don't understand why, because i set it up as开发者_运维技巧 clustered, so i was hoping it will be much more efficient). I was always under that assumption that when you search on clustered index, the query should be very efficient.

Is there anyone has any idea how i can improve this issue and this query to get a message and its replies?

Thanks.

Two suggestions come to my mind:

• Remove the ugly OR and add a UNION for the condition (CODE BELOW)
• You must have a non-clustered index on ReplyToMessage

As a last resort, create a non-clustered index and put MessageId AND ReplyToMessage in there. (See my answer to another question here Why does this Sql Statement (with 2 table joins) takes 5 mins to complete?)

CODE:

select fm1.[MessageId]
      ,fm1.[ForumId]
      ,fm1.[MemberId]
      ,fm1.[Type]
      ,fm1.[Status]
      ,fm1.[Subject]
    ,fm1.[Body]
      ,fm1.[Posted]
      ,fm1.[Confirmed]
      ,fm1.[ReplyToMessage]
      ,fm1.[TotalAnswers]
      ,fm1.[AvgRateing]
      ,fm1.[TotalRated]
      ,fm1.[ReadCounter],
     Member.NickName AS MemberNickName, Forum.Name as ForumName
from ForumMessage fm1 LEFT OUTER JOIN
                      Member ON fm1.MemberId = Member.MemberId INNER JOIN
                Forum On fm1.ForumId = Forum.ForumId
where MessageId = @MessageId
UNION
select fm1.[MessageId]
      ,fm1.[ForumId]
      ,fm1.[MemberId]
      ,fm1.[Type]
      ,fm1.[Status]
      ,fm1.[Subject]
    ,fm1.[Body]
      ,fm1.[Posted]
      ,fm1.[Confirmed]
      ,fm1.[ReplyToMessage]
      ,fm1.[TotalAnswers]
      ,fm1.[AvgRateing]
      ,fm1.[TotalRated]
      ,fm1.[ReadCounter],
     Member.NickName AS MemberNickName, Forum.Name as ForumName
from ForumMessage fm1 LEFT OUTER JOIN
                      Member ON fm1.MemberId = Member.MemberId INNER JOIN
                Forum On fm1.ForumId = Forum.ForumId
where MessageId = @MessageId
order by MessageId 

depending on the version of MS SQL Server you're running you could also try recreating the table utilizing partitioned tables to enhance the SELECT performance.

Create an index on ReplyToMessage:

CREATE INDEX
        IX_ForumMessage_ReplyToMessage
ON      ForumMessage (ReplyToMessage)

This will most probably result in two index seeks (over the PRIMARY KEY on MessageId and over the index on ReplyToMessage) contatenated with a merge or hash concatenation, rather than a full table scan which you are having now.

Why you are doing ORDER BY MessageId, is it so necessary ordering?

Try to refactory your SELECT to SELECT FROM Forum and than joining the Member, and finally LEFT JOIN ForumMessage. So order tables from small to large