Comparando Joins, Subconsultas y FDU

Comparando agrupamientos, Subconsultas y FDU

Este artículo explica como se debe analizar el resultado en la extracción de datos desde varias tablas, compararemos los valores del «Plan de ejecución estimado» de las siguientes tres maneras:

  1. Usando Joins y agrupamientos
  2. Usando Subconsultas
  3. Usando Funciones definidas por el usuario

Para mas información ver:

Ejercicios
Usando la base de datos Northwind
Use Northwind
go

Ejercicio 1

Listado de Categorias, la cantidad de productos y la cantidad de items en Stock. El resultado para las tres opciones es el que se muestra en la figura

Solución usando Joins a agrupamientos

select C.CategoryID As ‘Cód. Categoría’, C.CategoryName As ‘Categoría’,
COUNT(P.ProductID) As ‘Cantidad Productos’,
Sum(P.UnitsInStock) As ‘Items en Stock’
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
Group by C.CategoryID , C.CategoryName
go

El plan de ejecución estimado es como se muestra en la figura, observe el costo de subárbol estimado.

Solución usando subconsultas

select C.CategoryID As ‘Cód. Categoría’, C.CategoryName As ‘Categoría’,
(select COUNT(P.ProductID) from Products As P
where P.CategoryID = C.CategoryID ) As ‘Cantidad Productos’,
(select Sum(P.UnitsInStock) from Products As P
where P.CategoryID = C.CategoryID ) As ‘Items en Stock’
from Categories As C
go

El plan de ejecución estimado es como se muestra en la figura, observe el costo de subárbol estimado.

Solución usando una función definida por el usuario

Las FDU que retornan la cantidad de productos y la cantidad de productos en stock para cada categoría son como se muestran a continuación
FDU para la cantidad de productos de una categoría
Create function fduRetornaCantidadProductosPorCategoria(@CodigoCategoria int)
returns int
As
Begin
Declare @CantidadProductos int
Set @CantidadProductos = (select COUNT(P.ProductID) from Products As P
where P.CategoryID = @CodigoCategoria)
/* También se puede usar el select de la siguiente manera
select @CantidadProductos = COUNT(P.ProductID) from Products As P
where P.CategoryID = @CodigoCategoria */
Return @CantidadProductos
End
go

FDU para la cantidad de items de cada productos de una categoría

Create function fduRetornaCantidadProductosEnStockPorCategoria(@CodigoCategoria int)
returns Numeric(9,2)
As
Begin
Declare @CantidadProductosEnStock int
Set @CantidadProductosEnStock =
(select Sum(P.UnitsInStock) from Products As P
where P.CategoryID = @CodigoCategoria)
Return @CantidadProductosEnStock
End
go

El resultado requerido usando las FDU
select C.CategoryID As ‘Cód. Categoría’, C.CategoryName As ‘Categoría’,
dbo.fduRetornaCantidadProductosPorCategoria(C.CategoryID) As ‘Cantidad Productos’,
dbo.fduRetornaCantidadProductosEnStockPorCategoria(C.CategoryID) As ‘Items en Stock’
from Categories As C
go

El plan de ejecución estimado es como se muestra en la figura, observe el costo de subárbol estimado.

Comparando los costos
Usando Joins y Agrupamientos: 0.0199542
Usando Subconsultas : 0.0127294
Usando FDU : 0.0032916
Definitivamente para esta consulta la mejor opción es el uso de FDU.

Ejercicio 2

Listado de los empleados y la cantidad de órdenes generadas
El resultado para las tres opciones es el que se muestra en la figura

Solución usando Joins a agrupamientos

select E.EmployeeID As ‘Cód. Empleado’, E.LastName + SPACE(1)+ E.FirstName As ‘Empleado’,
COUNT(O.OrderID) As ‘Cantidad Ordenes’
from Orders As O
join Employees As E on O.EmployeeID = E.EmployeeID
Group by E.EmployeeID, E.LastName + SPACE(1)+ E.FirstName
go
Listado de los empleados y la cantidad de órdenes generadas
El resultado para las tres opciones es el que se muestra en la figura

Solución usando subconsultas

select E.EmployeeID As ‘Cód. Empleado’, E.LastName + SPACE(1)+ E.FirstName As ‘Empleado’,
(select COUNT(O.OrderID) from Orders As O where O.EmployeeID = E.EmployeeID ) As ‘Cantidad Ordenes’
from Employees As E
go
Listado de los empleados y la cantidad de órdenes generadas
El resultado para las tres opciones es el que se muestra en la figura

Solución usando una FDU

Las FDU que retornan la cantidad de órdenes
Create function fduRetornaCantidadOrdenesPorEmpleado(@CodigoEmpleado int)
returns int
As
Begin
Declare @CantidadOrdenes int
Set @CantidadOrdenes = (select COUNT(O.OrderID) from Orders As O
where O.EmployeeID =@CodigoEmpleado)
Return @CantidadOrdenes
End
go
El resultado requerido usando las FDU
select E.EmployeeID As ‘Cód. Empleado’, E.LastName + SPACE(1)+ E.FirstName As ‘Empleado’,
dbo.fduRetornaCantidadOrdenesPorEmpleado(E.EmployeeID) As ‘Cantidad Productos’
from Employees As E
go
Listado de los empleados y la cantidad de órdenes generadas
El resultado para las tres opciones es el que se muestra en la figura

Comparando los costos

Usando Joins y Agrupamientos: 0.0100247
Usando Subconsultas : 0.0123854
Usando FDU : 0.0032928
Definitivamente para esta consulta la mejor opción es el uso de FDU.

Recomendación:
Analizar siempre el resultado usando el Plan de ejecución estimado y seleccionar la que tenga el menor valor en el costo estimado de sub árbol.