La consulta SQL generada Linq selecciona todas las columnas cuando se selecciona cualquier propiedad de entidad relacionada

Resumen

Estoy investigando algunas declaraciones SQL generadas por Linq en nuestra aplicación .NET Core 3.1.3, y encontré que cada vez que uso Take en mi consulta, se genera una declaración SQL interna que selecciona todas las columnas de la tabla [cars], incluso columnas que no estoy interesado en nada. También hay un selecto externo que selecciona las columnas que realmente quiero. Tenemos varios usos que buscan mucho de propiedades de entidad relacionadas con el uso de DTOs, por lo que esta es la razón por la que estoy investigando este comportamiento.

No estoy muy experimentado con SQL, pero intuitivamente, se siente mal que el SELECT TOP interior seleccione todas las columnas cuando no es realmente necesario.

  • ¿Por qué es esto, y puede causar problemas de rendimiento cuando el número de propiedades de entidad relacionadas crece?
  • ¿Es mi consulta en el ejemplo 3 realmente mejor desde un punto de vista de rendimiento que el ejemplo 2?

[UPDATE]: Así que aparentemente este comportamiento se puede evitar si pones el Take después de la Select en lugar de antes:

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName
                               })
                               .Take(10)
                               .ToListAsync();

Lo que genera lo siguiente:

SELECT TOP(@__p_0) [c].[regNr] AS [Licenseplate], [c].[carName] AS [CarName], [c0].[name] AS [CustomerName]
      FROM [cars] AS [c]
      LEFT JOIN [salesInfoes] AS [s] ON [c].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      WHERE [c].[carID] = 72763

Ejemplos

Ejemplo 1. La siguiente expresión Linq resulta en cada propiedad desde que se selecciona el coche en la declaración interna SELECT TOP de la consulta SQL.

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Take(10)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName
                               })
                               .ToListAsync();

SQL (columnas enmascaradas con x en el SELECT interior):

SELECT [t].[regNr] AS [Licenseplate], [t].[carName] AS [CarName], [c0].[name] AS [CustomerName]
      FROM (
          SELECT TOP(@__p_0) [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x]
          FROM [cars] AS [c]
          WHERE [c].[carID] = 72763
      ) AS [t]
      LEFT JOIN [salesInfoes] AS [s] ON [t].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]

Sin Take, el SELECT interior desaparece y la consulta se ve mucho más ligera:

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName
                               })
                               .ToListAsync();

SQL generado:

 SELECT [c].[regNr] AS [Licenseplate], [c].[carName] AS [CarName], [c0].[name] AS [CustomerName]
      FROM [cars] AS [c]
      LEFT JOIN [salesInfoes] AS [s] ON [c].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      WHERE [c].[carID] = 72763

Ejemplo 2. Si agrego otra propiedad de entidad relacionada, obtengo otro TOP SELECT anidado en la consulta con todas las columnas, de nuevo:

var cars = await db.Cars.Where(c => c.Id == 72763)
                               .Take(10)
                               .Select(c => new
                               {
                                   c.Licenseplate,
                                   c.CarName,
                                   CustomerName = c.Sale.Customer.FullName,
                                   FacilityName = c.FacilityNow.Name
                               })
                               .ToListAsync();

SQL generado

SELECT [t0].[regNr] AS [Licenseplate], [t0].[carName] AS [CarName], [c0].[name] AS [CustomerName], [f].[name] AS [Name]
      FROM (
          SELECT TOP(@__p_0) [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x], [t].[x]
          FROM (
              SELECT TOP(@__p_0) [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x], [c].[x]
              FROM [cars] AS [c]
              WHERE [c].[carID] = 72763
          ) AS [t]
      ) AS [t0]
      LEFT JOIN [salesInfoes] AS [s] ON [t0].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      INNER JOIN [facilities] AS [f] ON [t0].[facilityNowID] = [f].[facilityID]

Ejemplo 3. Esto es lo que creo que la consulta debe parecer no buscar datos que no necesito.

SELECT [t0].[regNr] AS [Licenseplate], [t0].[carName] AS [CarName], [c0].[name] AS [CustomerName], [f].[name] AS [Name]
      FROM (
          SELECT TOP 100 [t].[carID], [t].[regNr], [t].[carName], [t].[facilityNowID]
          FROM (
              SELECT TOP 100 [c].[carID], [c].[regNr], [c].[carName], [c].[facilityNowID]
              FROM [cars] AS [c]
              WHERE [c].[carID] = 72763
          ) AS [t]
      ) AS [t0]
      LEFT JOIN [salesInfoes] AS [s] ON [t0].[carID] = [s].[salesID]
      LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
      INNER JOIN [facilities] AS [f] ON [t0].[facilityNowID] = [f].[facilityID]

Pregunta hecha hace 3 años, 4 meses, 13 días - Por daniel1

2 Respuestas:

  • El comportamiento que estás observando en la generación de consultas SQL con Linq es debido a la forma en que se estructura tu consulta y cómo se resuelven las relaciones entre entidades.

    En el primer ejemplo que proporcionaste, al usar Take antes de Select, se genera una consulta SQL interna que selecciona todas las columnas de la tabla [cars] antes de aplicar la limitación por Take. Esto puede causar problemas de rendimiento a medida que aumente el número de propiedades de entidad relacionadas, ya que se seleccionarán todas las columnas de la entidad principal incluso cuando no sean necesarias.

    En el segundo ejemplo, al agregar otra propiedad de entidad relacionada, se produce un anidamiento adicional de SELECT TOP en la consulta, lo que también puede impactar negativamente en el rendimiento, ya que seleccionará todas las columnas innecesarias antes de restringir los resultados por Take.

    La solución que proporcionaste en el UPDATE es correcta. Al colocar Take después de Select, la consulta SQL se optimiza y selecciona solo las columnas necesarias, evitando el anidamiento innecesario de SELECT TOP y mejorando el rendimiento de la consulta.

    Por lo tanto, tu consulta en el ejemplo 3, donde colocas Take después de Select, es más eficiente desde un punto de vista de rendimiento en comparación con el ejemplo 2 donde Take se encuentra antes de Select. Es importante tener en cuenta la estructura de tus consultas para minimizar la selección de columnas innecesarias y optimizar el rendimiento de tus consultas SQL generadas por Linq.

    Respondida el Dec 18, 2020 a las 11:19 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • 1. ¿Por qué ocurre esto?

    Cuando usas Take antes de Select, LINQ genera una subconsulta para limitar el número de resultados. Esta subconsulta interna utiliza una cláusula SELECT <strong>, que selecciona todas las columnas de la tabla, incluso las que no se utilizan en la consulta externa. Esto se debe a que LINQ no puede saber qué columnas se necesitarán en la consulta externa hasta que se ejecute la subconsulta.

    2. ¿Puede causar problemas de rendimiento?

    Sí, esto puede causar problemas de rendimiento si la tabla tiene muchas columnas, ya que la subconsulta interna tiene que leer todas ellas, incluso las que no necesita la consulta externa. Esto puede ralentizar significativamente la consulta, especialmente cuando se trata de conjuntos de datos grandes.

    3. ¿Es mejor el ejemplo 3 que el ejemplo 2?

    Sí, el ejemplo 3 es mejor que el ejemplo 2 desde un punto de vista de rendimiento. En el ejemplo 3, la subconsulta interna solo selecciona las columnas que necesita la consulta externa, lo que reduce la cantidad de datos que se deben leer y mejora el rendimiento.

    Cómo evitar este comportamiento

    Puedes evitar este comportamiento colocando Take después de Select. Esto asegurará que la subconsulta interna solo seleccione las columnas que necesita la consulta externa.

    Aquí tienes un ejemplo actualizado del ejemplo 1 utilizando el Take después del Select:

    csharp
var cars = await db.Cars.Where(c => c.Id == 72763)
    .Select(c => new
    {
        c.Licenseplate,
        c.CarName,
        CustomerName = c.Sale.Customer.FullName
    })
    .Take(10)
    .ToListAsync();

    Esto generará la siguiente consulta SQL, que solo selecciona las columnas necesarias:

    sql
SELECT [c].[regNr] AS [Licenseplate], [c].[carName] AS [CarName], [c0].[name] AS [CustomerName]
FROM [cars] AS [c]
LEFT JOIN [salesInfoes] AS [s] ON [c].[carID] = [s].[salesID]
LEFT JOIN [customers] AS [c0] ON [s].[customerId] = [c0].[customerId]
WHERE [c].[carID] = 72763

    Conclusión*

    Colocar Take después de Select es una práctica recomendada para mejorar el rendimiento de las consultas LINQ que utilizan Take. Esto asegura que la subconsulta interna solo seleccione las columnas necesarias, lo que reduce la cantidad de datos que se deben leer y mejora el rendimiento general.

    Respondida el Dec 18, 2020 a las 11:22 - por Gemini

    Votos positivos: 0 | Votos negativos: 0