Pivote de SQL Server vs. unión múltiple

¿Qué es más eficiente de usar en SQL Server 2005: PIVOT o MULTIPLE JOIN?

Por ejemplo, obtuve esta consulta usando dos uniones:

SELECT p.name, pc1.code as code1, pc2.code as code2 FROM product p INNER JOIN product_code pc1 ON p.product_id=pc1.product_id AND pc1.type=1 INNER JOIN product_code pc2 ON p.product_id=pc2.product_id AND pc2.type=2 

Puedo hacer lo mismo usando PIVOT:

 SELECT name, [1] as code1, [2] as code2 FROM ( SELECT p.name, pc.type, pc.code FROM product p INNER JOIN product_code pc ON p.product_id=pc.product_id WHERE pc.type IN (1,2)) prods1 PIVOT( MAX(code) FOR type IN ([1], [2])) prods2 

¿Cuál será más eficiente?

La respuesta será, por supuesto, “depende” pero basada en probar este extremo …

Asumiendo

  1. 1 millón de productos
  2. product tiene un índice agrupado en product_id
  3. La mayoría de los productos (si no todos) tienen la información correspondiente en la tabla product_code
  4. Índices ideales presentes en product_code para ambas consultas.

La versión PIVOT idealmente necesita un índice product_code(product_id, type) INCLUDE (code) mientras que la versión JOIN necesita un índice product_code(type,product_id) INCLUDE (code)

Si estos están en su lugar dando los planes a continuación

Planes

entonces la versión JOIN es más eficiente.

En el caso de que los type 1 y type 2 sean los únicos en la tabla, la versión PIVOT tiene un margen leve en términos de cantidad de lecturas ya que no tiene que buscar en product_code dos veces, pero eso es más que compensado por el valor adicional sobrecarga del operador agregado de la secuencia

PIVOTE

 Table 'product_code'. Scan count 1, logical reads 10467 Table 'product'. Scan count 1, logical reads 4750 CPU time = 3297 ms, elapsed time = 3260 ms. 

UNIRSE

 Table 'product_code'. Scan count 2, logical reads 10471 Table 'product'. Scan count 1, logical reads 4750 CPU time = 1906 ms, elapsed time = 1866 ms. 

Si hay registros de type adicionales que no sean 1 y 2 la versión JOIN boostá su ventaja, ya que fusiona las uniones en las secciones relevantes del type,product_id índice de type,product_id mientras que el plan PIVOT usa product_id, type y deberá escanear el filas de type adicionales que se entremezclan con las filas 1 y 2 .

No creo que nadie pueda decirle cuál será más eficiente sin el conocimiento de su indexación y tamaño de la tabla.

Dicho esto, en lugar de hipotetizar sobre cuál es más eficiente, debe analizar el plan de ejecución de estas dos consultas.