查询25万行,耗时53秒

尝试在stories.lastActivityAt上添加索引。我认为执行计划中的聚集索引扫描可能是由于排序。

编辑: 由于我的查询在一瞬间返回,行只有几个字节长,但已经运行了5分钟,并且在添加了2K varchar之后仍在继续,所以我认为Mitch有一个观点。正是这些数据的容量被毫无意义地乱移,但这可以在查询中修复。

尝试将join、sort和top(10)放在视图或嵌套查询中,然后针对story表进行join,以获取所需10行的其余数据。

这样地:

select * from 
(
    SELECT TOP(10) id, categoryID, userID
    FROM Stories
    ORDER BY Stories.LastActivityAt
) s
INNER JOIN Stories ON Stories.ID = s.id
INNER JOIN Categories ON Categories.ID = s.CategoryID
INNER JOIN Users ON Users.ID = s.UserID

如果您有lastactivityat的索引,那么应该运行得非常快。

因此,如果我正确地阅读了第一部分,它将在17秒内用索引响应。这还需要一段时间才能翻出10张唱片。我想时间是按顺序排列的。我想要一个LastActivityAt、UserID、CategoryID的索引。只是为了好玩,删除之前的订单,看看它是否能快速返回10条记录。如果是这样,那么您就知道它不在与其他表的联接中。此外,将*替换为所需的列也会很有帮助,因为在排序时,所有3个表列都在tempdb中—正如Neil所提到的。

看看执行计划,你会注意到额外的排序——我相信这是需要一些时间的排序。我假设你有一个3的索引,那是17秒…因此,您可能需要一个用于联接条件(userid、categoryid)的索引和另一个用于lastactivityat的索引—看看是否性能更好。另外,最好通过索引优化向导运行查询。

我的第一个建议是删除*,并用所需的最小列替换它。

第二,是否涉及触发器?更新lastactivityat字段的内容?

根据问题查询,尝试在表上添加组合索引 Stories (categoryID、userID、lastActivityAT)

您正在最大限度地利用硬件设置中的磁盘。

考虑到您对数据/日志/tempdb文件放置的评论,我认为任何数量的调优都将是一个创可贴。

25万行很小。想象一下,1000万行的话,你的问题会有多严重。

我建议您将tempdb移动到它自己的物理驱动器上(最好是RAID 0)。

好吧,所以我的测试机不快。实际上很慢。它1.6 GHz,1 GB的RAM,没有多个磁盘,只有一个(读慢)磁盘用于SQL Server、OS和Extras。

我用定义的主键和外键创建了您的表。 插入2个类别,500个随机用户,25万个随机故事。

运行上面的第一个查询需要16秒(也没有计划缓存)。 如果索引LastActivityAt列,我会在一秒钟内得到结果(这里也没有计划缓存)。

这是我做所有这些的脚本。

    --Categories table --
Create table Categories (
[ID] [int] IDENTITY(1,1) primary key NOT NULL,
[ShortName] [nvarchar](8) NOT NULL,
[Name] [nvarchar](64) NOT NULL)

--Users table --
Create table Users(
[ID] [int] IDENTITY(1,1) primary key NOT NULL,
[Username] [nvarchar](32) NOT NULL,
[Password] [nvarchar](64) NOT NULL,
[Email] [nvarchar](320) NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[LastActivityAt] [datetime] NOT NULL
)
go

-- Stories table --
Create table Stories(
[ID] [int] IDENTITY(1,1) primary key NOT NULL,
[UserID] [int] NOT NULL references Users ,
[CategoryID] [int] NOT NULL references Categories,
[VoteCount] [int] NOT NULL,
[CommentCount] [int] NOT NULL,
[Title] [nvarchar](96) NOT NULL,
[Description] [nvarchar](1024) NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[UniqueName] [nvarchar](96) NOT NULL,
[Url] [nvarchar](512) NOT NULL,
[LastActivityAt] [datetime] NOT NULL)

Insert into Categories (ShortName, Name) 
Values ('cat1', 'Test Category One')

Insert into Categories (ShortName, Name) 
Values ('cat2', 'Test Category Two')

--Dummy Users
Insert into Users
Select top 500
UserName=left(SO.name+SC.name, 32)
, Password=left(reverse(SC.name+SO.name), 64)
, Email=Left(SO.name, 128)+'@'+left(SC.name, 123)+'.com'
, CreatedAt='1899-12-31'
, LastActivityAt=GETDATE()
from sysobjects SO 
Inner Join syscolumns SC on SO.id=SC.id
go

--dummy stories!
-- A Count is given every 10000 record inserts (could be faster)
-- RBAR method!
set nocount on
Declare @count as bigint
Set @count = 0
begin transaction
while @count<=250000
begin
Insert into Stories
Select
  USERID=floor(((500 + 1) - 1) * RAND() + 1)
, CategoryID=floor(((2 + 1) - 1) * RAND() + 1)
, votecount=floor(((10 + 1) - 1) * RAND() + 1)
, commentcount=floor(((8 + 1) - 1) * RAND() + 1)
, Title=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))
, Description=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))
, CreatedAt='1899-12-31'
, UniqueName=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36)) 
, Url=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))
, LastActivityAt=Dateadd(day, -floor(((600 + 1) - 1) * RAND() + 1), GETDATE())
If @count % 10000=0
Begin
Print @count
Commit
begin transaction
End
Set @count=@count+1
end 
set nocount off
go

--returns in 16 seconds
DBCC DROPCLEANBUFFERS
SELECT TOP(10) *
FROM Stories
INNER JOIN Categories ON Categories.ID = Stories.CategoryID
INNER JOIN Users ON Users.ID = Stories.UserID
ORDER BY Stories.LastActivityAt
go

--Now create an index
Create index IX_LastADate on Stories (LastActivityAt asc)
go
--With an index returns in less than a second
DBCC DROPCLEANBUFFERS
SELECT TOP(10) *
FROM Stories
INNER JOIN Categories ON Categories.ID = Stories.CategoryID
INNER JOIN Users ON Users.ID = Stories.UserID
ORDER BY Stories.LastActivityAt
go

这就是你慢下来的原因。 排序主要是在tempdb中完成的,一个大的表将导致添加大量数据。 在此列上有一个索引肯定会提高ORDER BY的性能。

另外,定义主键和外键也可以立即帮助SQL Server

代码中列出的方法很优雅,基本上与cdonner编写的响应相同,除了C而不是SQL。调整数据库可能会得到更好的结果!

——克里斯

在运行每个查询之前,是否清除了SQL Server缓存?

在SQL 2000中,它类似于DBCC DropCleanBuffers。谷歌命令以获取更多信息。

查看查询时,我将为

类别ID 故事.categoryid 用户标识 故事书

而且可能 故事.lastactivityat

但是,听起来结果可能是虚假的缓存成本。

当您使用SQL Server一段时间后,您会发现,即使对查询进行最小的更改,也会导致不同的响应时间。根据我在初始问题中读到的内容,以及查询计划,我怀疑优化器已经决定最好的方法是形成一个部分结果,然后将其作为一个单独的步骤进行排序。部分结果是用户和故事表的组合。这是在tempdb中形成的。所以过度的磁盘访问是由于这个临时表的形成和排序造成的。

我同意解决方案应该是在stories.lastActivityat、stories.userid、stories.categoryID上创建一个复合索引。顺序非常重要,字段lastActivityAt必须是第一个。