Generación EF6 SQL para <where nullable columns equals>

entity-framework entity-framework-6

Pregunta

Al intentar actualizar de EF5 a EF6, encuentro una significativa brecha de rendimiento en la tabla de búsqueda por columna anulable. Aquí hay una muestra:

public class Customer
{
    public int Id { get; set; }
    public int? ManagerId { get; set; }
    //public virtual Manager Manager { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connstring): base(connstring){}
    public DbSet<Customer> Customers { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var db = new MyContext("CONNSTRING");
        var managerId = 1234;
        var q = from b in db.Customers
                where b.ManagerId == managerId
                select b.Id;
        var s = q.ToString();
    }
}

Cuando EF6 genera SQL, agrega algo de lógica para el manejo nulo:

public class Customer
{
    public int Id { get; set; }
    public int? ManagerId { get; set; }
    //public virtual Manager Manager { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connstring): base(connstring){}
    public DbSet<Customer> Customers { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var db = new MyContext("CONNSTRING");
        var managerId = 1234;
        var q = from b in db.Customers
                where b.ManagerId == managerId
                select b.Id;
        var s = q.ToString();
    }
}

Note que el mismo linq produjo un SQL más simple bajo EF5:

public class Customer
{
    public int Id { get; set; }
    public int? ManagerId { get; set; }
    //public virtual Manager Manager { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connstring): base(connstring){}
    public DbSet<Customer> Customers { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var db = new MyContext("CONNSTRING");
        var managerId = 1234;
        var q = from b in db.Customers
                where b.ManagerId == managerId
                select b.Id;
        var s = q.ToString();
    }
}

Puedo entender el punto que los desarrolladores intentaron lograr: si proporciona nulo como parámetro, la consulta donde managerId = null no seleccionará ninguna línea. Aprecio el cuidado, pero el 99.9% de las veces que la lógica de búsqueda está separada: una base de datos busca where ManagerId == null , otra busca una identificación específica where ManagerId == managerId

El problema está en un gran impacto en el rendimiento: MS SQL no utiliza el índice en ManagerId y se produce el escaneo de la tabla. Mi proyecto tiene cientos de búsquedas similares y en el tamaño de la base de datos sobre 100 GB de rendimiento general después de la actualización a EF6 reducido en 10 aproximadamente.

La pregunta es: ¿alguien sabe algún tipo de configuración o convención para deshabilitar este obstáculo en EF6 y generar un SQL simple?

EDITAR:

Revisé una docena de selecciones similares en mi proyecto y encontré que:

  • En algunos casos, SQL SERVER usa el índice especificado para el campo que busco. E incluso en estos casos hay una ligera pérdida de rendimiento: utiliza el índice dos veces: la primera vez que busca el valor que especifiqué en el parámetro, la segunda vez que busco el valor nulo
  • EF6 incluso comprueba el valor nulo cuando la constante se especifica exactamente como no nula, por ejemplo:

    public class Customer
    {
        public int Id { get; set; }
        public int? ManagerId { get; set; }
        //public virtual Manager Manager { get; set; }
    }
    
    public class MyContext : DbContext
    {
        public MyContext(string connstring): base(connstring){}
        public DbSet<Customer> Customers { get; set; }
    }
    
    class Program
    {
        static void Main(string[] args)
        {
            var db = new MyContext("CONNSTRING");
            var managerId = 1234;
            var q = from b in db.Customers
                    where b.ManagerId == managerId
                    select b.Id;
            var s = q.ToString();
        }
    }
    

genera SQL

public class Customer
{
    public int Id { get; set; }
    public int? ManagerId { get; set; }
    //public virtual Manager Manager { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connstring): base(connstring){}
    public DbSet<Customer> Customers { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var db = new MyContext("CONNSTRING");
        var managerId = 1234;
        var q = from b in db.Customers
                where b.ManagerId == managerId
                select b.Id;
        var s = q.ToString();
    }
}

que no utiliza mi índice en el portador. La version EF5 tenia

public class Customer
{
    public int Id { get; set; }
    public int? ManagerId { get; set; }
    //public virtual Manager Manager { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connstring): base(connstring){}
    public DbSet<Customer> Customers { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var db = new MyContext("CONNSTRING");
        var managerId = 1234;
        var q = from b in db.Customers
                where b.ManagerId == managerId
                select b.Id;
        var s = q.ToString();
    }
}

que lo utilizó

Observe la condición ('ALLTEL' = [Extent1].[Carrier]) AND ([Extent1].[Carrier] IS NOT NULL) . La segunda parte siempre es falsa, pero agregar esta parte abandona el índice.

Mi importación de rutina de alrededor de 1.7M de registros (que usualmente tomó alrededor de 30 minutos) ha aumentado durante 3 horas y el progreso es de alrededor del 30%.

Respuesta aceptada

Conjunto

db.Configuration.UseDatabaseNullSemantics = true;

para obtener el comportamiento que tenías en EF5. Este artículo de trabajo describe cuál es la diferencia entre true y false y lo ayudará a decidir si está de acuerdo con el comportamiento anterior o no.


Respuesta popular

Respuesta muy diferente .

Si está utilizando un varchar (xxx), el LNQ to SQL escupe nvarchar (4000), lo que rompe los índices y las conversiones de forma masiva y sopla su plan de SQL. En mi caso, encontré esta pregunta debido al extraño comportamiento nulo, pero ese no era el problema. La respuesta a continuación resuelve ambos problemas nulos y nvarchar. El plan SQL pasó de ~ 11 a .006.

public class InterestingRow
{
    [Key]
    public int interesting_row_id { get; set; }

    [StringLength(255), Required, Column(TypeName = "varchar")]
    public string public_guid { get; set; }
}

(Sí, hay muchas razones para usar varchar, como si estuviera almacenando un guid expuesto públicamente)




Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué