LINQ vs. DBA junior

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…

Published 07-21-2009 18:37 por horaison
Archivado en:
Ofrecido por Community Server (Non-Commercial Edition)