Ef Linq查询超时，但在SSMS上相同的查询少于1秒 [英] Ef Linq queries timed out, but same queries less than 1 second on SSMS

问题描述

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

Firstly I tried ARITHABORT OFF on SSMS it's still less than 1 second.

我使用EntityFrameWork:6.1.3和Azure Sql S1层(我将尝试方法3，并让您知道是否有所更改.)

I use EntityFrameWork: 6.1.3 and Azure Sql S1 tier (I will try with Tier 3 and let you know if something changes.)

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

I use EF Profiler to get generated sql from linq. I have queried all of linqs which I have shared, they all are less than 1 second on SSMS.

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

I have 3 million recods on AuditLog Table. One customer with ID 3 has 170K records the other customer with ID 35 has 125 records. I will minimize the code.

AuditLog模型:

AuditLog Model:

 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 I try with customer who has 170k rows, it gives time out exception. If I try with customer who has 125 records, it's fine.

第二个查询:与仅包含客户"的第一个查询相同.

Second Query: It's same with first one I just include Customers.

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();
    }

结果与第一个查询相反.如果我尝试有17万行的客户，那就很好.如果我尝试使用具有125条记录的客户，则会给出超时异常.

The result is opposite of first query. if I try with customer who has 170k rows, it's fine. If I try with customer who has 125 records,it gives timeout exception.

第三条查询:与第一条查询相同，但是我将long?匹配到customerId所在的位置.

Third query: It's same with first query, but I match long? on where for customerId.

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

结果与第一个查询相反.如果我尝试有17万行的客户，那就很好.如果我尝试使用具有125条记录的客户，则会给出超时异常.

The result is opposite of first query. if I try with customer who has 170k rows, it's fine. If I try with customer who has 125 records,it gives timeout exception.

第四次查询:与第二次查询相同，但是我将long?匹配到customerId所在的位置.

Fourth query: It's same with second query, but I match long? on where for customerId.

 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条记录的客户，那很好.

The result is opposite of second query. if I try with customer who has 170k rows, it gives time out exception. If I try with customer who has 125 records, it's fine.

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

I'm really confused. Why inner join or changing match paramter to long? are changing results ? And why this all queries run under 1 sec on SSMS and give error on ef linq ?

错误:

{System.Data.SqlClient.SqlException(0x80131904):超时已过期. 在操作完成或操作完成之前已过超时时间 服务器没有响应. -> System.ComponentModel.Win32Exception (0x80004005):等待操作在以下位置超时 System.Data.SqlClient.SqlConnection.OnError(SqlException异常， 布尔值breakConnection，动作为1 wrapCloseInAction)

{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 对评论提出建议之后.

After Ivan Stoev suggestion on comments.

您是否尝试过(仅出于测试目的)使用硬编码的3和35而不是customerId变量?

我没有收到任何错误，查询的速度和SSMS一样快.

I didn't get any error and queries are fastest as on SSMS.

更新(20/04/2016):真正的问题是参数嗅探.当我包含参数或将参数更改为可为空时，实际上我已经创建了另一个查询和另一个查询计划.我与有125条记录的客户一起创建了一些计划，而与这4条查询有170k条记录的客户一起创建了一些计划.这就是为什么我得到不同的结果.

Update (20/04/2016): The real problem is Parameter Sniffing. When I included or changed parameter to nullable, actually I have created another queries and another query plans. I created some plans with customer who has 125 records, and the other ones with customer who has 170k records of these 4 queries. That's why I got different results.

推荐答案

您遇到的是所谓的 EntityFramework LINQ查询计数失败，但查询返回结果.如何优化LINQ查询?.

What you are experiencing is a result of so called Parameter Sniffing Problem. I don't know a simple general solution so far, so usually suggest a workaround by eliminating some of the SQL query parameters by manually binding constant values inside the expressions, like in EntityFramework LINQ query count fails but query returns result. How to optimize LINQ query?.

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

For your scenario, I would suggest the following custom extension method:

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);
    }
}

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

and then update your snippet like this

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();
}

这篇关于Ef Linq查询超时，但在SSMS上相同的查询少于1秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！