En ocasiones nos pueden pedir optimizar las consultas y puede ocurrir que se necesite reescribir la consulta real, ya que la pérdida de rendimiento normalmente puede ser causada por tener subconsultas anidadas.
Vamos a ver un ejemplo, realizado sobre la base de datos: AdventureWorks2008R2. Veremos formas de mejorar la consulta:
select a.[LoginID],a.BirthDate from [HumanResources].[Employee]as a,[HumanResources].[EmployeeDepartmentHistory] as B
where a.BusinessEntityID=b.BusinessEntityID
and b.ModifiedDate= (select Max(ModifiedDate) from [HumanResources].[EmployeeDepartmentHistory] as c
where a.BusinessEntityID=c.BusinessEntityID)
La anterior consulta nos muestra el plan de ejecución donde os muestro su coste:
Con la clausula OVER el motor optimiza el tiempo de ejecución y los bloqueos de las filas cuando se escribe las consultas con OVER, ya que el motor trabaja sobre los datos ya obtenidos, en vez de volver a recuperarlos de origen.
select v.[LoginID],v.BirthDate from
(select a.[LoginID],a.BirthDate,b.ModifiedDate,Max(b.ModifiedDate) over(partition by b.BusinessEntityID ) as M33n@l from
[HumanResources].[Employee]as a,[HumanResources].[EmployeeDepartmentHistory] as b where
a.BusinessEntityID=b.BusinessEntityID ) as v where v.ModifiedDate=v.M33n@l
La anterior consulta nos muestra el plan de ejecución donde os muestro su coste: (y observamos como disminuye)
Finamente os muestro como puede mejorar la consluta, si trabajamos los JOIN, En el plan vemos como disminuye su costo mas aun:
Select a.[LoginID],a.BirthDate
From [HumanResources].[Employee] A
Inner Join (Select BusinessEntityID,
Max(ModifiedDate) ModifiedDate
From [HumanResources].[EmployeeDepartmentHistory]
Group By BusinessEntityID) B
On A.BusinessEntityID=B.BusinessEntityID
Siempre que puedeas intenta usar JOIN en lugar de Subselct, en mas del 80% de los casos el JOIN te dará mejor rendimiento.Asique, cuando puedas reemplazar las subconsultas por un JOIN el rendimiento subirá enormemente porque no se ejecutara por cada fila la subconsulta, sino que aplicará conjuntos.. En las ocaciones donde tengas que sacar un conjunto de resultados para hacer un cruce con otras tablas o consigo misma y no puedas valerte del with cte u otras formas, es la solucion correcta..
En ocasiones puede volver a escribir una subconsulta para que utilice operadores JOIN y mejorar el rendimiento. La ventaja de crear un operador JOIN consiste en que puede evaluar tablas en un orden distinto del definido por la consulta. La ventaja de usar una subconsulta es que normalmente no es necesario explorar todas las filas de la subconsulta para evaluar la expresión de ésta. Por ejemplo, una subconsulta EXISTS puede devolver TRUE al ver la primera fila que coincida.
Depende mucho en que circunstancia se utiliza las subconsultas, y siempre conviene que te fijes en el plan de ejecución de la query para asegurarte..
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
