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:
- Usando Joins y agrupamientos
- Usando Subconsultas
- 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 dbo.Categories As C
join dbo.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 dbo.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 dbo.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 dbo.fduRetornaCantidadProductosPorCategoria(@CodigoCategoria int)
returns int
As
Begin
Declare @CantidadProductos int
Set @CantidadProductos = (select COUNT(P.ProductID) from dbo.Products As P
where P.CategoryID = @CodigoCategoria)
/* También se puede usar el select de la siguiente manera
select @CantidadProductos = COUNT(P.ProductID) from dbo.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 dbo.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 dbo.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 dbo.Orders As O
join dbo.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 dbo.Orders As O where O.EmployeeID = E.EmployeeID ) As ‘Cantidad Ordenes’
from dbo.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 dbo.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 dbo.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.