Ef Linq queries timed out, but same queries less than 1 second on SSMS

Question

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

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

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.

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 Model:

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

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

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

First query:

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

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

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.

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

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.

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

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.

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 ?

Error:

{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)

Update (19/04/2016):

After Ivan Stoev suggestion on comments.

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

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

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.

Answer 1

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