为什么在SSMS和代码中执行SQL查询时会有输出/性能差异？

Question

我一段时间前开始使用dapper.net，出于性能原因，我真的很喜欢与LINQ To SQL中的ExecuteQuery相比，它具有命名参数功能。

对于大多数查询，它都运行得很好，但是有时会出现一些非常奇怪的超时问题。最奇怪的是，只有在通过dapper执行SQL时才会发生此超时。如果我将从分析器中复制的执行查询仅在Management Studio中运行，它就会快速且完美地工作。而且这不仅仅是暂时的问题。查询通过dapper始终超时，并且在Management Studio中始终正常工作。

exec sp_executesql N'SELECT Item.Name,dbo.PlatformTextAndUrlName(Item.ItemId) As PlatformString,dbo.MetaString(Item.ItemId) As MetaTagString, Item.StartPageRank,Item.ItemRecentViewCount
                        NAME_SRCH.RANK as NameRank,
                        DESC_SRCH.RANK As DescRank, 
                        ALIAS_SRCH.RANK as AliasRank, 
                        Item.itemrecentviewcount,
                        (COALESCE(ALIAS_SRCH.RANK, 0)) + (COALESCE(NAME_SRCH.RANK, 0)) + (COALESCE(DESC_SRCH.RANK, 0) / 20) + Item.itemrecentviewcount / 4 + ((CASE WHEN altrank > 60 THEN 60 ELSE altrank END) * 4) As SuperRank
                        FROM dbo.Item
                        INNER JOIN dbo.License on Item.LicenseId = License.LicenseId
                        
                        LEFT JOIN dbo.Icon on Item.ItemId = Icon.ItemId
                        LEFT OUTER JOIN FREETEXTTABLE(dbo.Item, name, @SearchString) NAME_SRCH ON
                        Item.ItemId = NAME_SRCH.[KEY] 
                        LEFT OUTER JOIN FREETEXTTABLE(dbo.Item, namealiases, @SearchString) ALIAS_SRCH ON
                        Item.ItemId = ALIAS_SRCH.[KEY] 
                        INNER JOIN FREETEXTTABLE(dbo.Item, *, @SearchString) DESC_SRCH ON
                        Item.ItemId = DESC_SRCH.[KEY]
                        ORDER BY SuperRank DESC OFFSET @Skip ROWS FETCH NEXT @Count ROWS ONLY',N'@Count int,@SearchString nvarchar(4000),@Skip int',@Count=12,@SearchString=N'box,com',@Skip=0

这是我从SQL Profiler复制粘贴的查询。我在我的代码中像这样执行它。

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn"].ToString()))
{
    connection.Open();
    var items = connection.Query<MainItemForList>(query, new { SearchString = searchString, PlatformId = platformId, _LicenseFilter = licenseFilter, Skip = skip, Count = count }, buffered: false);
    return items.ToList();
}

我不知道该从哪里开始。我猜想，由于当我仅执行代码时它能正常工作，所以必须与Dapper有关。

正如您在此屏幕截图中看到的那样。这是通过Code First和Management Studio执行的相同查询。

我还可以补充一点，这种情况（我认为）只会在搜索字符串中有两个或更多单词或者有一个stop字符时发生。所以这可能与全文搜索有关，但我无法找出如何调试它，因为在管理工具中它完美地运行。

更糟糕的是，在我的本地主机上，使用几乎相同的数据库，从代码和管理工具中都可以正常工作。

- Olaj

Answer 1

Dapper只是ADO.NET的一个实用包装器；它不会改变ADO.NET的操作方式。在我看来，这里的问题是“在ssms中运行正常，在ADO.NET中失败”。这并不罕见：偶尔会发现这种情况。可能的原因如下：

• "set"选项：在ADO.NET中有不同的默认值 - 如果您有类似于计算+持久化+索引列等内容，它可能会影响性能 - 如果“set”选项不兼容，它可能会决定不能使用存储的值，而是进行表扫描和重新计算。还有其他类似的场景。
• 系统负载/事务隔离级别/阻塞；在ssms中运行某些操作时，无法重现那个特定时间点的整个系统负载
• 缓存查询计划：有时会缓存并使用错误的计划；从ssms运行通常会强制生成一个新的计划 - 这自然会针对您在测试中使用的参数进行优化。更新所有索引统计信息等，并考虑添加“为...进行优化”的查询提示

- Marc Gravell

---

抱歉回复晚了，但测试有点难。不管怎样，我认为这与缓存的查询计划有关。我现在在本地遇到了超时问题，重启了服务器后，它就正常工作了。我现在尝试添加了一个“优化”的东西，希望能有所帮助。感谢您的见解！ - Olaj

你如何正确地管理传递给Dapper/SqlCommand.ExecuteReader的adhoc查询的"set"选项？ - Terry

@Terry，“set”选项是在连接级别上设置的 - 因此，您需要打开连接，然后（例如）connection.Execute("SET CONCAT_NULL_YIELDS_NULL ON");，然后（其余代码）; 您还可以使用“SET”指令前缀单个命令。但请注意，并非所有“SET”选项都会被sp_reset_connection重置（当连接被重用时自动执行） - 例如（特别是）SET TRANSACTION ISOLATION LEVEL {blah};即使在连接重置之间也会持续存在，这可能会很有趣。 - Marc Gravell

谢谢。调试为什么ADO.NET超时而SSMS不超时的过程很简单，只需开始切换SET选项并查看哪个会影响它即可。 - Terry

我在发送给数据库的SQL语句末尾添加了OPTION(RECOMPILE)，这对我解决了问题[基于上述第3点]。 - akraines

@akraines，“recompile”是一个可怕的提示，几乎永远不应该使用；“for unknown”可能是你想要的。 - Marc Gravell

Answer 2

在ADO中，CommandTimeout的默认值为30秒，在Management Studio中为无限。调整调用Query的命令超时时间，请参见下面的内容。

var param = new { SearchString = searchString, PlatformId = platformId, _LicenseFilter = licenseFilter, Skip = skip, Count = count };
var queryTimeoutInSeconds = 120;
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn"].ToString()))
{
    connection.Open();
    var items = connection.Query<MainItemForList>(query, param, commandTimeout: queryTimeoutInSeconds, buffered: false);
    return items.ToList();
}

另请参阅 MSDN上的SqlCommand.CommandTimeout属性

- Georg

---

链接：https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?redirectedfrom=MSDN&view=netframework-4.7.2#System_Data_SqlClient_SqlCommand_CommandTimeout - Ryan

Answer 3

对于Dapper，其默认超时时间为30秒。但是我们可以通过以下方式增加超时时间。这里我们将超时时间增加到240秒（4分钟）。

    public DataTable GetReport(bool isDepot, string fetchById)
    {
        int? queryTimeoutInSeconds = 240;
        using (IDbConnection _connection = DapperConnection)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@IsDepot", isDepot);
            parameters.Add("@FetchById", fetchById);
            var res = this.ExecuteSP<dynamic>(SPNames.SSP_GetSEPReport, parameters, queryTimeoutInSeconds);
            return ToDataTable(res);
        }
    }

在仓库层，我们可以调用自定义的ExecuteSP方法来执行带有额外参数“queryTimeoutInSeconds”的存储过程。

以下是Dapper的“ExecuteSP”方法：

    public virtual IEnumerable<TEntity> ExecuteSP<TEntity>(string spName, object parameters = null, int? parameterForTimeout = null)
    {
        using (IDbConnection _connection = DapperConnection)
        {
            _connection.Open();
            return _connection.Query<TEntity>(spName, parameters, commandTimeout: parameterForTimeout, commandType: CommandType.StoredProcedure);
        }
    }

- aditya goyal

Answer 4

可能是在Dapper中设置命令超时的问题。以下是调整Dapper中命令超时的示例: 设置Dapper中的命令超时

- Jamie

Answer 5

Dapper默认将参数设置为VARCHAR（4000）。在大型表上，这会导致读取非常缓慢，就好像它没有使用索引一样。

将此更改为： var result = await _connection.QueryFirstOrDefaultAsync<Record>(sql, new { recordId = new DbString { Value = recordId, Length = 15 }}); （或者如果您确信不存在SQL注入，则直接将参数放入SQL字符串中）。

参考资料： SQL Server Hidden “Load Evil” (Performance Issue)With Dapper Dapper.NET: The varchar(4000) default

- ChrisE