sql server语言 - sql查询有很多超时

Question

我有一个大的数据库表(SQL Server 2008)，我有我所有的论坛消息被存储(表目前有超过450万个条目)。

表模式:

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]

我看到不断回来的一个问题是，当我运行我的存储过程，选择一个消息和它的所有答复，我有时得到超时错误从SQL服务器。

这是我的存储过程:

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 

我得到的错误看起来像这样:"Timeout expired. "操作完成前的超时时间或服务器没有响应"

我正在查看执行计划，唯一看起来可疑的是，看到查询在forummessage表中的键查找上的成本约为75%-87%(它各不相同)(我不明白为什么，因为我将其设置为集群，所以我希望它会更有效)。我总是假设，当你在聚类索引上搜索时，查询应该是非常有效的。

是否有人有任何想法，我可以如何改善这个问题和这个查询得到消息和它的答复?

谢谢。

Answer 1

我想到了两个建议:

• 删除丑陋的 OR，并为条件(代码如下)添加UNION
• 必须在ReplyToMessage
上有非聚集索引

作为最后的手段，创建一个非聚集索引，并将MessageId和ReplyToMessage放在那里。(参见我对另一个问题的回答，为什么这个Sql语句(有2个表连接)需要5分钟才能完成?)

---

<标题>代码:

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 

Answer 2

根据你运行的MS SQL Server的版本，你也可以尝试使用分区表来重新创建表，以提高SELECT的性能。

Answer 3

创建ReplyToMessage的索引:

CREATE INDEX
        IX_ForumMessage_ReplyToMessage
ON      ForumMessage (ReplyToMessage)

这很可能导致两次索引查找(在MessageId上查找PRIMARY KEY，在ReplyToMessage上查找索引)与合并或哈希连接相关联，而不是您现在所拥有的全表扫描。

Answer 4

你为什么要做ORDER BY MessageId，有必要订购吗?

尝试将SELECT重构到SELECT FROM Forum，然后加入Member，最后加入LEFT JOIN ForumMessage。