BATTLERxANGE 2014年01月26日
sp_executesql 执行非常慢

exec sp_executesql N'SELECT TOP (10)
[Project1].[ID] AS [ID],
[Project1].[UID] AS [UID],
[Project1].[Name] AS [Name],
[Project1].[Email] AS [Email],
[Project1].[Title] AS [Title],
[Project1].[Content] AS [Content],
[Project1].[ImageSrc] AS [ImageSrc],
[Project1].[ThImageSrc] AS [ThImageSrc],
[Project1].[IP] AS [IP],
[Project1].[PostDateTime] AS [PostDateTime],
[Project1].[UpdateDateTime] AS [UpdateDateTime],
[Project1].[ForumID] AS [ForumID],
[Project1].[ParentID] AS [ParentID]
FROM ( SELECT [Project1].[ID] AS [ID], [Project1].[UID] AS [UID], [Project1].[Name] AS [Name], [Project1].[Email] AS [Email], [Project1].[Title] AS [Title], [Project1].[Content] AS [Content], [Project1].[ImageSrc] AS [ImageSrc], [Project1].[ThImageSrc] AS [ThImageSrc], [Project1].[IP] AS [IP], [Project1].[PostDateTime] AS [PostDateTime], [Project1].[UpdateDateTime] AS [UpdateDateTime], [Project1].[ForumID] AS [ForumID], [Project1].[ParentID] AS [ParentID], row_number() OVER (ORDER BY [Project1].[UpdateDateTime] DESC) AS [row_number]
FROM ( SELECT
[Extent1].[ID] AS [ID],
[Extent1].[UID] AS [UID],
[Extent1].[Name] AS [Name],
[Extent1].[Email] AS [Email],
[Extent1].[Title] AS [Title],
[Extent1].[Content] AS [Content],
[Extent1].[ImageSrc] AS [ImageSrc],
[Extent1].[ThImageSrc] AS [ThImageSrc],
[Extent1].[IP] AS [IP],
[Extent1].[PostDateTime] AS [PostDateTime],
[Extent1].[UpdateDateTime] AS [UpdateDateTime],
[Extent1].[ForumID] AS [ForumID],
[Extent1].[ParentID] AS [ParentID]
FROM [dbo].[Thread] AS [Extent1]
WHERE ([Extent1].[ForumID] = @p__linq__0) AND (@p__linq__0 IS NOT NULL) AND ([Extent1].[ParentID] IS NULL)
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 100
ORDER BY [Project1].[UpdateDateTime] DESC',N'@p__linq__0 int',@p__linq__0=4


这是用ENTITY FRAMEWORK生成的SQL语句,发现很慢:
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

(10 行受影响)
表 'Thread'。扫描计数 50,逻辑读取 2280 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 3816 毫秒,占用时间 = 1528 毫秒。

SQL Server 执行时间:
CPU 时间 = 3816 毫秒,占用时间 = 1528 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。




但是如果将[Project1].[row_number] > 100改成[Project1].[row_number] > 0则查询变得很快,或者在SQL语句中随便加入一个空格也会变的很快,这是什么原因?
这是正常状态下的执行计划:
...全文
239 点赞 收藏 15
写回复
15 条回复

还没有回复,快来抢沙发~

发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告