Hlog

Se armó la discusión…
Si usan esto en nombre de la agilidad no me cuenten por favor…

Consulta generada por la consulta LINQ mostrada por José en la capacitación del sábado (Capturada con el SQL Profiler):

SELECT 
1 AS [Mesa], 
[Project5].[C1] AS [C2], 
[Project5].[C2] AS [C3], 
[Project5].[C3] AS [C4], 
[Project5].[C4] AS [C5]
FROM ( SELECT 
      [Project4].[C1] AS [C1], 
      [Project4].[C2] AS [C2], 
      [Project4].[C3] AS [C3], 
      (SELECT 
            COUNT(cast(1 as bit)) AS [A1]
            FROM [dbo].[Padron] AS [Extent5]
            WHERE (([Extent5].[Mesa] = [Project4].[Mesa]) OR (([Extent5].[Mesa] IS NULL) AND ([Project4].[Mesa] IS NULL))) AND ([Extent5].[VotoValor] IS NULL)) AS [C4]
      FROM ( SELECT 
            [Project3].[Mesa] AS [Mesa], 
            [Project3].[C1] AS [C1], 
            [Project3].[C2] AS [C2], 
            (SELECT 
                  COUNT(cast(1 as bit)) AS [A1]
                  FROM [dbo].[Padron] AS [Extent4]
                  WHERE (([Extent4].[Mesa] = [Project3].[Mesa]) OR (([Extent4].[Mesa] IS NULL) AND ([Project3].[Mesa] IS NULL))) AND (0 = [Extent4].[VotoValor])) AS [C3]
            FROM ( SELECT 
                  [Project2].[Mesa] AS [Mesa], 
                  [Project2].[C1] AS [C1], 
                  (SELECT 
                        COUNT(cast(1 as bit)) AS [A1]
                        FROM [dbo].[Padron] AS [Extent3]
                        WHERE (([Extent3].[Mesa] = [Project2].[Mesa]) OR (([Extent3].[Mesa] IS NULL) AND ([Project2].[Mesa] IS NULL))) AND (1 = [Extent3].[VotoValor])) AS [C2]
                  FROM ( SELECT 
                        [Distinct1].[Mesa] AS [Mesa], 
                        (SELECT 
                             COUNT(cast(1 as bit)) AS [A1]
                             FROM [dbo].[Padron] AS [Extent2]
                             WHERE ([Extent2].[Mesa] = [Distinct1].[Mesa]) OR (([Extent2].[Mesa] IS NULL) AND ([Distinct1].[Mesa] IS NULL))) AS [C1]
                        FROM ( SELECT DISTINCT 
                             [Extent1].[Mesa] AS [Mesa]
                             FROM [dbo].[Padron] AS [Extent1]
                        )  AS [Distinct1]
                  )  AS [Project2]
            )  AS [Project3]
      )  AS [Project4]
)  AS [Project5]

Consulta que puede hacer un desarrollador junior con unas poquitas clases de SQL:

SELECT 
Mesa, 
COUNT(*),
SUM(CASE WHEN [VotoValor] = 1 THEN 1 ELSE 0 END),
SUM(CASE WHEN [VotoValor] = 0 THEN 1 ELSE 0 END),
SUM(CASE WHEN [VotoValor] IS NULL THEN 1 ELSE 0 END)
FROM Padron
GROUP BY Mesa
ORDER BY Mesa

Análisis de ambas consultas con SET STATISTICS IO ON

Consulta LINQ:

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Padron'. Recuento de exámenes 25, lecturas lógicas 35943, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Consulta SQL:

Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Tabla 'Padron'. Recuento de exámenes 5, lecturas lógicas 10449, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Un 30% de páginas leídas respecto de la consulta generada por LINQ.
Ni hablar que si esto lo ponemos en un procedimiento almacenado tenemos la consulta precompilada y optimizada.

Evidentemente todavía hay un largo trecho por recorrer…