Ef Linq requests timeout, but same requests less than 1 second on SSMS

Question

First, I tried ARITHABORT OFF on SSMS for less than 1 second.

I am using EntityFrameWork: 6.1.3 and Azure Sql S1 (I will try with Level 3 and let you know if something changes.)

I am using EF Profiler to get the generated sql from linq. I requested all linqs that I shared, they are all less than 1 second on SSMS.

I have 3 million recommendations in the AuditLog table. One client with identifier 3 has 170 thousand records, another client with ID 35 has 125 records. I will hide 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 request:

  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 a client that has lines of 170 thousand lines, it gives a timeout exception. If I try with a client that has 125 entries, that’s fine.

The second request . The same with the first I just turn on clients.

 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 the opposite of the first query. if I try with a client that has lines of 170 thousand, this is normal. If I try with a client that has 125 entries, it gives a timeout exception.

Third query: Same with first query, but am I matching long? 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 the opposite of the first query. if I try with a client that has lines of 170 thousand, this is normal. If I try with a client that has 125 entries, it gives a timeout exception.

Fourth query: Same with second query, but am I matching long? 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 the opposite of the second query. if I try with a client that has 170 thousand lines, this gives a timeout exception. If I try with a client that has 125 entries, that’s fine.

I'm really confused. Why is the inner join or change the matching parameter to long? change the results? And why do all requests run less than 1 second on SSMS and give an error on ef linq?

Error:

{System.Data.SqlClient.SqlException (0x80131904): timed out. The wait period expires before the operation is completed or the server does not respond. ---> System.ComponentModel.Win32Exception (0x80004005): wait operation calculated on System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Update (04/19/2016):

After Ivan Stoev sentence for comments.

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

I did not receive any errors and requests are faster than on SSMS.

Update (04/20/2016): The real issue is the Sniffing option . When I turned on or changed the parameter to nullable, I actually created other queries and other query plans. I created several plans with a client who has 125 records, and others with a client who has 170 thousand records of these 4 requests. That is why I got different results.

Answer 1

What you are experiencing is the result of the so-called parameter determination problem . Until now, I don’t know a simple general solution, so I usually propose a workaround, eliminating some parameters of the SQL query, manually binding constant values ​​inside the expressions, for example, EntityFramework LINQ query count fails, but the query returns the result. How to optimize LINQ query? .

For your scenario, I would suggest the following 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 as follows

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