Linq-SQL query is very slow when using a gateway variable compared to hard integer code

Question

I have a query that takes about 10 seconds.

The strange thing is that if I replaced the machineNo parameter with a hardcoded integer (e.g. 3), the request will take a second. This is the most unusual performance issue I've encountered so far.

public static IEnumerable<vwJobAppointment> GetAllJobs(int machineNo)
{
    var db = new DbContext();
    IEnumerable<vwJobAppointment> list;
    list = db.vwJobAppointments.Where(a => a.ResourceId == (machineNo)).AsNoTracking().ToList();
    return list;
}

The request takes 10 seconds +

public static IEnumerable<vwJobAppointment> GetAllJobs(int machineNo)
{
    var db = new DbContext();
    IEnumerable<vwJobAppointment> list;
    list = db.vwJobAppointments.Where(a => a.ResourceId == (3)).AsNoTracking().ToList();
    return list;
}

This request takes less than a second.

Any ideas? I am using Entity Framework 5 with SQL 2008 database

Answer 1

EF SQL. SQL . , , . , , . , . :

• ,

Answer 2

sniffing; .. , , , , , . , - , 3 , 300 000 . , OPTION (OPTIMIZE FOR ... UNKNOWN) , , ORM , , SQL.

Answer 3

. . :

// Execution time: 00:00:00.0140118
public static IEnumerable<myClass> GetAllJobs(int machineNo)
{
    var db = new DbContext();
    IEnumerable<myClass> list;
    list = db.vwJobAppointments.Where(a => a.ResourceId == (machineNo)).ToList();
    return list;
}

// Execution time: 00:00:00.0019991
public static IEnumerable<myClass> GetAllJobs2(int machineNo)
{
    var db = new DbContext();
    IEnumerable<myClass> list;
    list = db.vwJobAppointments.Where(a => a.ResourceId == 55).ToList();
    return list;
}

// Execution time: 00:00:00.0010013
public static IEnumerable<myClass> GetAllJobs3(int machineNo)
{
    int machineNo2 = machineNo;
    var db = new DbContext();
    IEnumerable<myClass> list;
    list = db.vwJobAppointments.Where(a => a.ResourceId == (machineNo2)).ToList();
    return list;
}