c# - Ef Linq 查询超时，但相同的查询在 SSMS 上不到 1 秒

Question

首先，我在 SSMS 上尝试了 ARITHABORT OFF，它仍然不到 1 秒。

我使用 EntityFrameWork:6.1.3 和 Azure Sql S1 层(我会尝试使用第 3 层，如果有变化会通知您。)

我使用 EF Profiler 从 linq 获取生成的 sql。我已经查询了我共享的所有 linq，它们在 SSMS 上都不到 1 秒。

我在 AuditLog 表上有 300 万条记录。 ID 为 3 的一位客户有 170K 条记录，另一位 ID 为 35 的客户有 125 条记录。我将最小化代码。

审计日志模型:

 public class AuditLog
  {
    public long? CustomerId { get; set; }

    [ForeignKey("CustomerId")]
    public virtual CustomerSummary Customer { get; set; }

    [Required]
    [Index]
     public DateTime CreatedDate { get; set; }
  }

第一次查询:

 if (customer != null)
    {
      var customerId = customer.Id;
      var result= Dbset.Where(x => x.CustomerId == customerId).OrderByDescending(x => x.CreatedDate).Skip(0).Take(25).ToList();
    }

如果我尝试与拥有 170k 行的客户一起尝试，它会给出超时异常。如果我对有 125 条记录的客户进行尝试，没问题。

第二个查询:与第一个查询相同，我只包括客户。

if (customer != null)
   {
      var customerId = customer.Id;
      var result= Dbset.Where(x => x.CustomerId == customerId).OrderByDescending(x => x.CreatedDate).Skip(0).Take(25).Include(x => x.Customer).ToList();
    }

结果与第一个查询相反。如果我尝试与拥有 170k 行的客户一起使用，那很好。如果我尝试与有 125 条记录的客户一起尝试，它会给出超时异常。

第三个查询:与第一个查询相同，但我在 customerId 的位置匹配 long?。

 if (customer != null)
    {
      long? customerId = customer.Id;
      var result= Dbset.Where(x => x.CustomerId == customerId).OrderByDescending(x => x.CreatedDate).Skip(0).Take(25).ToList();
    }

结果与第一个查询相反。如果我尝试与拥有 170k 行的客户一起使用，那很好。如果我尝试与有 125 条记录的客户一起尝试，它会给出超时异常。

第四个查询:与第二个查询相同，但我在 customerId 的位置匹配 long?。

 if (customer != null)
    {
      long? customerId = customer.Id;
      var result= Dbset.Where(x => x.CustomerId == customerId).OrderByDescending(x => x.CreatedDate).Skip(0).Take(25).Include(x => x.Customer).ToList();
    }

结果与第二个查询相反。如果我尝试与拥有 170k 行的客户一起尝试，它会给出超时异常。如果我对有 125 条记录的客户进行尝试，没问题。

我真的很困惑。为什么内部连接或将匹配参数更改为 long? 会改变结果？为什么所有这些查询在 SSMS 上运行不到 1 秒并在 ef linq 上给出错误？

错误:

{System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

更新(19/04/2016):

在 Ivan Stoev 之后意见建议。

Have you tried (just for the sake of test) using hardcoded 3 and 35 instead of customerId variable?

我没有收到任何错误，查询速度与在 SSMS 上一样快。

更新(2016 年 4 月 20 日):真正的问题是参数嗅探。当我将参数包含或更改为可为空时，实际上我已经创建了另一个查询和另一个查询计划。我为拥有 125 条记录的客户创建了一些计划，为拥有这 4 个查询的 170k 记录的客户创建了其他计划。这就是我得到不同结果的原因。

Answer 1

您遇到的是所谓的 Parameter Sniffing Problem 的结果。 .到目前为止，我还不知道一个简单的通用解决方案，因此通常建议通过在表达式中手动绑定(bind)常量值来消除一些 SQL 查询参数来提出解决方法，例如 EntityFramework LINQ query count fails but query returns result. How to optimize LINQ query? .

对于您的场景，我建议使用以下自定义扩展方法:

public static class QueryableExtensions
{
    public static IQueryable<T> WhereEquals<T, TValue>(this IQueryable<T> source, Expression<Func<T, TValue>> selector, TValue value)
    {
        var predicate = Expression.Lambda<Func<T, bool>>(
            Expression.Equal(selector.Body, Expression.Constant(value)),
            selector.Parameters);
        return source.Where(predicate);
    }
}

然后像这样更新你的代码段

if (customer != null)
{
    var result= Dbset.WhereEquals(x => x.CustomerId.Value, customer.Id)
        .OrderByDescending(x => x.CreatedDate)
        .Skip(0).Take(25)
        .Include(x => x.Customer)
        .ToList();
}