Comparando Agrupamientos, Subconsultas y FDU

Comparando agrupamientos, Subconsultas y FDU en SQL Server

Una consulta que tiene agrupamientos se puede extraer usando también subconsultas y funciones definidas por el usuario. 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

Ejercicios
Usando la base de datos Northwind
Use Northwind
go

Ejercicio Nº 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, note el costo estimado de subárbol de: 0.0199542

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, note
el costo estimado de subárbol de: 0.0127294

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,
note el costo estimado de subárbol de: 0.0032916

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 Nº 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
El plan de ejecución estimado es como 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
El plan de ejecución estimado es como se muestra en la figura

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
El plan de ejecución estimado es como 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.