SQL查询在代码中比直接查询数据库要花费更长的时间

Question

我有一个SQL查询，当我使用SQL Management Studio时，它只需要不到一秒钟就可以执行，但是当我的代码执行它时，从数据库服务器获取结果要花费30多秒的时间。查询结果包含1700行。另一个类似的查询，返回900行，只需要几毫秒的时间来执行。造成这种奇怪行为的原因是什么？

    public SqlDataReader ExecuteReader(string strSQL, ArrayList arParams)
    {
        OpenConnection();

        SqlCommand myCommand = new SqlCommand(strSQL, myConnection);
        myCommand.CommandTimeout = intTimeout;


        foreach (SqlParameter myParameter in arParams)
            myCommand.Parameters.Add(myParameter);

        return myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }

strSQL:

SELECT [Group].[Id]
       ,[Group].[intCustomerId]   
       ,[Group].[strName]
       ,[Permission].[dtmCreated]
       ,[Permission].[intPermissionTypeId]
       ,[Permission].[intObjectTypeId]          
       ,[Permission].[intObjectId]            
       ,[Permission].[blnActive]            
       ,[Permission].[blnHaveAccess]
       ,[Permission].[intLevelTypeId]             
 FROM [Group]
 LEFT JOIN Permission ON [Group].[Id] = intGroupId AND 
                         intObjectId = @ObjectId AND 
                         intObjectTypeId = @ObjectTypeId AND 
                         intLevelTypeId = @LevelType AND
                         intPermissionTypeId = @PermissionTypeId AND
                         blnActive = 1                                             
 WHERE [Group].[intCustomerId] = @CustomerId  AND
       [Group].[blnDeleted] = 0
 ORDER BY strName, blnActive DESC

参数：

arParams.Add(DatabaseHandler.MakeSqlParameter("@CustomerId", customer.Id));
arParams.Add(DatabaseHandler.MakeSqlParameter("@ObjectId", masterprocess.Id));
arParams.Add(DatabaseHandler.MakeSqlParameter("@ObjectTypeId", Convert.ToInt32(ObjectType.MasterProcess)));
arParams.Add(DatabaseHandler.MakeSqlParameter("@PermissionTypeId", Convert.ToInt32(permissiontype)));
arParams.Add(DatabaseHandler.MakeSqlParameter("@LevelType", Convert.ToInt32(leveltype)));

DatabaseHandler.MakeSqlParameter:

public static SqlParameter MakeSqlParameter(String strName, int intInput)
{
    return new SqlParameter(strName, intInput);
}

- Jimmy Mattsson

---

请查看https://dev59.com/p0rSa4cB1Zd3GeqPTAgr以及@gbn的答案。我认为，可能是SQLParameters的数据类型与SQL所期望的不同步。例如，一个或多个SQLParameter的参数类型可能是varchar，而它应该是int（因为SQL会期望它是int）。 - shahkalpesh

这可能是“参数嗅探”的情况，但要应用此方法：strSQL是字面查询还是调用内部查询的某个存储过程的名称？您在两种情况下使用相同的参数值进行测试吗？ - Christian.K

也许发布时那个盒子有足够的内存吗？SQL管理工具对当前的资源等待情况有何说法？ - EKS

@Christian.K strSQL是字面查询。我使用相同的参数进行测试。 - Jimmy Mattsson

@EKS：当我从代码中运行查询时，资源等待为0，但处理器时间达到了100%。 - Jimmy Mattsson

Answer 1

根据您在评论中的回复，我认为正确的解决方案是索引。

最简单的方法是在运行常规查询时运行SQL日志记录一段时间，然后运行SQL分析器。

根据其建议，它可能已经发现了缺失的索引。

- EKS

---

你似乎把我引向了正确的方向。我发现我可以重建索引，我在我的Permission表上这样做了，现在它以正常速度运行。但我仍然不明白为什么在管理工具中运行查询比在代码中运行要快得多。 - Jimmy Mattsson

Answer 2

我曾经遇到过同样的问题，但是在看到这个问题时想到了解决方案。

当您使用带参数的SQLCommand运行查询时，查询的执行方式并不相同。它使用存储过程助手来打包参数。类似于Exec sp_Execute。

如果您进行参数替换，您应该会注意到改进（我知道那样会有SQL注入的风险）。

此外，您甚至可以更进一步，在表旁边指定WITH(INDEX)，以指定要使用哪个索引。（因为它可能会尝试使用PK索引）。

- Kind Contributor