Explicación de como funcionan la sentencia force orden y utilidad : Sentencia OPTION FORCE ORDER en Transact-SQL

JuanCastillo 10 noviembre, 2017 0 comentarios

SENTENCIA OPTION FORCE ORDER

EN SQL Server existe la sentencia OPTION FORCE ORDER , la cual enprimer lugar existe entre muchas sugerencias de consulta,además una de las menos menos conocida es FORCE ORDER, por lo cual hablaremos sobre lo que hace esta sugerencia, así que cómo podemos usarla de manera eficiente dentro de nuestras consultas T-SQL,como resultado tendremos

Como funciona la sentencia FORCE ORDER

En primer lugar cuando colocamos esta sentencia en nuestras consultas, le decimos la SQL Server que cuando ejecute la instrucción en el motor de base de datos no cambie el orden de los UNION en la consulta. Unirá las tablas exactamente en el orden que se especifica en la consulta (Literalmente no cambiara nada). Normalmente, el optimizador de SQL Server reorganizará nuestros UNIONS, de tal forma que estén en el orden que cree que serán óptimasnuestras consultas al ejecutarse, por lo tanto el optimizador de consultas cambia totalmente las relaciones sin alterar el resultado, y no siempre queda de la manera correcta, por ende debemos utilizar nuestra sentencia para que respecte nuestro codigo

También debemos tomar ne cuenta que, el Optimizador de consultas lo acelera la mayor parte del tiempo, pero ocasionalmente elige un plan que no es el mejor posible. Puede darle al Optimizador de consultas una mejor idea, al colocar FORCE ORDER, pero esta tiene un riesgo: cualquier opción que fuerce en el Optimizador mediante el uso de sugerencias puede resultar completamente errónea a medida que la base de datos cambia con la adición de datos a lo largo del tiempo.

Pero claro esta si queremos imponer nuestra voluntad al optimizador y, hasta cierto punto, controlar su comportamiento, esto de realiza de la siguiente manera

«El ser humano por naturaleza le gusta imponer su voluntad»

Para utitlizar dicha sentencia, bastara como agregarla al final de nuestras sentencias SQL, crear nuestra sentencia SQL, la cual sera de la base de datos AdventureWorks2012

Consulta SIN la OPTION FORCE ORDER

SELECT P.FirstName        AS Nombre
,      P.LastName         AS Apellido
,      P.BusinessEntityID
,      Prod.Name          AS Producto
,      SOH.SalesOrderID  
,      SOD.ProductID     
,      SOD.UnitPrice     
FROM       Sales.Customer         AS C   
INNER JOIN Person.Person          AS P    ON C.PersonID = P.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader AS SOH  ON C.CustomerID = SOH.CustomerID
INNER JOIN Sales.SalesOrderDetail AS SOD  ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product     AS Prod ON SOD.ProductID = Prod.ProductID
OPTION 	 ( FORCE   ORDER )

SELECT P.FirstName AS Nombre

, P.LastName AS Apellido

, P.BusinessEntityID

, Prod.Name AS Producto

, SOH.SalesOrderID

, SOD.ProductID

, SOD.UnitPrice

FROM Sales.Customer AS C

INNER JOIN Person.Person AS P ON C.PersonID = P.BusinessEntityID

INNER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID = SOH.CustomerID

INNER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID

INNER JOIN Production.Product AS Prod ON SOD.ProductID = Prod.ProductID

OPTION ( FORCE ORDER )

Primeramente el Plan de ejecución real SIN la sugerencia FORCE ORDER.

Consulta CON la OPTION FORCE ORDER

SELECT P.FirstName        AS Nombre
,      P.LastName         AS Apellido
,      P.BusinessEntityID
,      Prod.Name          AS Producto
,      SOH.SalesOrderID  
,      SOD.ProductID     
,      SOD.UnitPrice     
FROM       Sales.Customer         AS C   
INNER JOIN Person.Person          AS P    ON C.PersonID = P.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader AS SOH  ON C.CustomerID = SOH.CustomerID
INNER JOIN Sales.SalesOrderDetail AS SOD  ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product     AS Prod ON SOD.ProductID = Prod.ProductID
ORDER BY P.FirstName ASC 
OPTION 	 ( FORCE   ORDER ) -- <-----------ESTA ES LA LINEA DE CODIGO NUEVA <----------->

SELECT P.FirstName AS Nombre

, P.LastName AS Apellido

, P.BusinessEntityID

, Prod.Name AS Producto

, SOH.SalesOrderID

, SOD.ProductID

, SOD.UnitPrice

FROM Sales.Customer AS C

INNER JOIN Person.Person AS P ON C.PersonID = P.BusinessEntityID

INNER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID = SOH.CustomerID

INNER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID

INNER JOIN Production.Product AS Prod ON SOD.ProductID = Prod.ProductID

ORDER BY P.FirstName ASC

OPTION ( FORCE ORDER ) -- <-----------ESTA ES LA LINEA DE CODIGO NUEVA <----------->

Plan de ejecución real CON la sugerencia FORCE ORDER.

Para qué se usa esto
La sugerencia de consulta FORCE ORDER, solo se usa cuando desea cancelar la forma en que SQL Server ejecuta la consulta en el motor de base de datos. Sin embargo, dejará que SQL Server descubra cómo obtener los datos de la base de datos, aunque el hace un trabajo tan bueno, existen cirscustancias que necesita anular esta funcionalidad, Por lo tanto, SQL Server no descubrirá la forma más óptima de extraer estos datos. Así que, es posible que desee probar esta sugerencia de consulta,Por consiguiente si utitlizas sistemas de datawherehouse que manejan grandes volúmenes de datos, por lo general sera de gran ayuda

SQL Server Query Execution Engine es bastante inteligente para decidir el mejor plan de ejecución con el menor costo, pero si nos creemos capaces de ganarle al optimizador, aqui tiene la sentencia que les ayudara a domar al toro