sp_executesql 执行非常慢

BATTLERxANGE 2014-01-26 06:11:15



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语句中随便加入一个空格也会变的很快，这是什么原因？
这是正常状态下的执行计划：

...全文

828 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

發糞塗牆 2014-02-08

打赏
举报

用sp_executesql应该能减少参数嗅探的问题，或者你重编译一下你的查询试试

yumifanshu 2014-02-08

打赏
举报

其实是这个语句写得有问题. row_number() OVER (ORDER BY [Project1].[UpdateDateTime] DESC) AS [row_number] 看下这个字段应该是没有使用上索引.或者没有索引. 建议改下语句. 把语句写得有效率一些.

蛋蛋の忧伤 2014-02-08

打赏
举报

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

suek225 2014-02-08

打赏
举报

t101lian 2014-01-27

打赏
举报

發糞塗牆 2014-01-27

打赏
举报

不能修改的话就用计划向导

BATTLERxANGE 2014-01-27

打赏
举报

引用 8 楼 x_wy46 的回复:

你所说的第二个正常的情况下：执行计划走的是索引扫描，证明你查询的那个ID数据分布应该比较多，否则就索引查找了那你在最外层的ORDER BY [Project1].[UpdateDateTime] DESC 后面加个option(recompile)试试

ORM无法修改生成的SQL

还有什么好的办法吗？

专注or全面 2014-01-26

打赏
举报

你所说的第二个正常的情况下：执行计划走的是索引扫描，证明你查询的那个ID数据分布应该比较多，否则就索引查找了那你在最外层的ORDER BY [Project1].[UpdateDateTime] DESC 后面加个option(recompile)试试

BATTLERxANGE 2014-01-26

打赏
举报

引用 6 楼 yupeigu 的回复:

[quote=引用 5 楼 BATTLERxANGE 的回复:] [quote=引用 3 楼 yupeigu 的回复:] [quote=引用 2 楼 BATTLERxANGE 的回复:] [quote=引用 1 楼 yupeigu 的回复:] 试试更新一下，统计信息： update statistics [Thread]

还是一样

查询的唯一区别就是WHERE [Project1].[row_number] > ？？这个不一样而已，有的很快有的很慢，而且很慢的是一直很慢。但是在sp_executesql中的SQL语句随便哪里改变一下，比如加个空格啊之类的，就正常了变的很快了，GOOGLE了一下是不是参数嗅探的问题？？[/quote] 有可能的，建议修改成exec，这样每次都会重新编译，能解决这个参数嗅探问题[/quote]

引用 4 楼 x_wy46 的回复: