SQL 查询出现大量超时

发布于 2024-11-25 03:46:24 字数 1872 浏览 0 评论 0原文

我有一个大型数据库表（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 Server 的超时错误。

这是我的存储过程：

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

我得到的错误如下所示：“超时已过期。在操作完成之前超时时间已过，或者服务器没有响应”

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

有谁知道我如何改进这个问题和这个查询来获取消息及其回复？

谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

固执像三岁 2024-12-02 03:46:24

我想到了两个建议：

删除丑陋 OR 并为条件添加一个UNION（代码如下）
您必须在 ReplyToMessage 上有一个非聚集索引

作为最后的手段，请创建一个非聚集索引并放置 MessageId AND 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

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

回复收藏 0 原文

倦话 2024-12-02 03:46:24

根据您运行的 MS SQL Server 版本，您还可以尝试利用分区表重新创建表以增强 SELECT 性能。

回复收藏 0 原文

孤君无依 2024-12-02 03:46:24

在 ReplyToMessage 上创建索引：

CREATE INDEX
        IX_ForumMessage_ReplyToMessage
ON      ForumMessage (ReplyToMessage)

这很可能会导致两次索引查找（通过 MessageId 上的 PRIMARY KEY 和 MessageId 上的索引>ReplyToMessage）与合并或哈希串联连接，而不是您现在进行的全表扫描。

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.

回复收藏 0 原文