Edición de «Taller Opt 1er Cuat 2022 Turno 1»

De Cuba-Wiki
Advertencia: no has iniciado sesión. Tu dirección IP se hará pública si haces cualquier edición. Si inicias sesión o creas una cuenta, tus ediciones se atribuirán a tu nombre de usuario, además de otros beneficios.

Puedes deshacer la edición. Antes de deshacer la edición, comprueba la siguiente comparación para verificar que realmente es lo que quieres hacer, y entonces publica los cambios para así efectuar la reversión.

Revisión actual Tu texto
Línea 1: Línea 1:
<h2>EXAMEN OPTIMIZACIÓN - TURNO 1 <i>(10/06/2022)</i></h2>
Deben ejecutarse las consultas sobre la Base de Datos AdventureWorks utilizando Azure Data Studio o SQL Server Management Studio. Deben compararse las diferentes consultas dentro de cada ejercicio y analizar los planes de ejecución explicando las diferencias y las elecciones del optimizador. <br/> La aprobación requiere de 4 ejercicios correctos.
<p> Deben ejecutarse las consultas sobre la Base de Datos <i>AdventureWorks</i> utilizando <i>Azure Data Studio</i> o <i>SQL Server Management Studio</i>. Deben compararse las diferentes consultas dentro de cada ejercicio y analizar los planes de ejecución explicando las diferencias y las elecciones del optimizador. <i>La aprobación requiere de <b>4 ejercicios correctos</b>.</i></p>


<ol>
1)
<li>
<div>
<div>
<b>SELECT</b> * <br/>
<b>FROM</b> Purchasing.ShipMethod <br/>
<b>ORDER BY</b> Name
</div>
<div>
<b>SELECT</b> * <br/>
<b>FROM</b> Purchasing.ShipMethod <br/>
<b>WHERE</b> Name <b>IS NOT</b> null
</div>
<div>
En este caso La segunda query es mucho más rápida. Para empezar el atributo Name es no nulo, por lo que el pedido en la segunda query de que el atributo sea nulo es innecesario y automáticamente optimizado por el motor ya que no modifica el output de la consulta.


Por otro lado la primer consulta requiere que los atributos están ordenados por el campo Name (recordemos que ShipMethod tiene su índice cluster ordenado por ShipMethodID) por lo que usa el índice non clustered ordenado por Name y con eso consigue ordenarlo rápidamente. Como ese índice solo tiene dicho atributo de orden y las claves para buscar en el índice cluster, utiliza Key Lookup en el Cluster para obtener el resto de atributos, y unirlos con Nested Loops para devolver la solución final Ordenada.
select * from <br/>
</div>
Purchasing.ShipMethod <br/>
</div>
order by Name
</li>
<li>
<div>
<div>
<b>SELECT</b> CardType <br/>
<b>FROM</b> Sales.CreditCard <br/>
<b>GROUP BY</b> CardType
</div>
<div>
<b>SELECT</b> CardNumber <br/>
<b>FROM</b> Sales.CreditCard <br/>
<b>GROUP BY</b> CardNumber
</div>
<div><p>
Es claro ver que a pesar que la primera query tiene pocos outputs, debido a que no hay un índice para dicho atributo, el motor optimiza de la mejor forma que tiene, viéndose obligado a usar el único índice que tiene CardType (el cluster) a pesar de su mayor tamaño. Procede a un Hash Match donde matchea los outputs según su card type para responder a la query.


Esto resulta muy costoso simplemente por verse obligado a usar el índice Cluster que es muy grande con datos que no estamos usando para esta query.


En la segunda query, si bien su output es mucho mayor, existe un índice ordenado por dicho campo (AK_CreditCard_CardNumber), el cual simplemente extrayendo las entradas del índice se obtiene la solución buscada. Por tanto el motor decide escanear dicho índice retornando sus entradas. Como dicho índice es más chico (tiene menos columnas), el tiempo de ejecución es bastante menor.
select * from <br/>
</p>
Purchasing.ShipMethod <br/>
</div>
where Name is not null;
</div>
</li>


<li>
2)
<div>
<div>
<b>SELECT</b> * <br/>
<b>FROM</b> sales.SalesOrderDetail <br/>
<b>WHERE</b> UnitPrice > ALL (<br/>
  <b>SELECT</b> UnitPrice <br/>
  <b>FROM</b> Sales.SalesOrderDetail <br/>
  <b>WHERE</b> OrderQty >12<br/>
)
</div>
<div>
<b>SELECT</b> * <br/>
<b>FROM</b> sales.SalesOrderDetail <br/>
<b>WHERE</b> UnitPrice > (<br/>
<b>SELECT</b> MAX(UnitPrice) <br/>
<b>FROM</b> Sales.SalesOrderDetail <br/>
<b>WHERE</b> OrderQty >12<br/>
)
</div>
<div>
<p>
Si bien ambas queries dan la misma respuesta (en la primera pide los que son mayores a todos los que cumplen tener más de 12 ventas, en la segunda se pide que sea solo mayor al máximo de todos ellos, claramente es lo mismo pero el motor no se da cuenta de esto), la segunda usa una propiedad que el motor no logra inferir y que logra aumentar el performance considerablemente.


En la primera debe guardar en una tabla temporal en memoria todos los UnitPrice lo que perjudica enormemente el performance, pero con el objetivo de optimizar el Nested Loop donde va a tener que comparar la tabla con todos los UnitPrice que cumplen, la propiedad. El resultado es que el Nested Loop en ambos casos tiene el mismo costo, pero en la primer query conseguir guardar todos los UnitPrice que cumplen en una tabla en memoria perjudica enormemente el performance mientras que en la primera solo se hace una función de agregación de poco costo, y se compara con dicho valor en un nested loop más chico.
select CardType from Sales.CreditCard group by CardType <br/>
</p>
select CardNumber from Sales.CreditCard group by CardNumber
</div>
</div>
</li>


<li>
3)
<div>
<div>
<b>SELECT</b> AddressID, City, StateProvinceID, ModifiedDate <br/>
<b>FROM</b> Person.Address <br/>
<b>WHERE</b> StateProvinceID = 32 <br/>
</div>
<div>
<b>SELECT</b> AddressID, City, StateProvinceID, ModifiedDate <br/>
<b>FROM</b> Person.Address <br/>
<b>WHERE</b> StateProvinceID = 20
</div>
<div>
El motor tiene estadísticas de ambas respuestas, y estima que para la primera query solo va a encontrar un resultado (osea una cantidad que no es perjudicial para hacer key lookups), esto quiere decir que buscar en un índice por estado y posteriormente hacer un key look up le es más barato.


Esto no vuelve a suceder con el estado 20. Es así porque el motor estima que recibirá más de 300 respuestas para la segunda query. Si bien hacer key lookups son mucho más baratos que hacer un completo scan del índice clustered, para este caso en particular solo es 40 veces más rápido, y la mitad de dicho costo corresponde al key look up, por lo que podemos suponer que en este caso en particular, a partir de los 80 key lookups es preferible hacer un scan antes de hacer tantos key lookups (y particularmente hubiera terminado haciendo 300 key look ups, por lo que por estadísticas decidió el otro camino).
select * from <br/>
</div>
sales.SalesOrderDetail <br/>
</div>
where UnitPrice > all (select UnitPrice from <br/>
</li>
Sales.SalesOrderDetail where OrderQty >12)


<li>
select * from <br/>
<div>
sales.SalesOrderDetail <br/>
<div>
where UnitPrice > (select Max(UnitPrice) from <br/>
<b>SELECT</b> COUNT(UnitPrice) <br/>
Sales.SalesOrderDetail where OrderQty >12)
<b>FROM</b> sales.SalesOrderDetail <br/>
</div>
<div>
<b>SELECT</b> SUM(UnitPrice) <br/>
<b>FROM</b> sales.SalesOrderDetail
</div>
<div>
<p>
UnitPrice es un atributo no nulo. Esto quiere decir que para la primera query la consulta es equivalente a devolver el número de filas que tiene la tabla. Debido a esto, cualquier índice que al menos sea completo (tenga todas las filas) va a ser suficiente para resolver la consulta. En particular el motor toma el más chico ya que al tener menos columnas, recorrerlo le será más rápido que por ejemplo si recorriera el cluster index. En particular esto hace que la respuesta se devuelva en un tercio del tiempo.


En la segunda query, lo que hicimos antes no es posible, porque requerimos sumar el valor exacto de cada uno de los campos. Para peor, no existe ningún indice en SalesOrderDetail que contenga al precio más que el cluster, por lo que se ve obligado a tomar el índice más grande. Esto hace que la respuesta sea algo más lenta que en el anterior caso, pero es necesario para conseguir una respuesta correcta.
4)
</p>
</div>
</div>
</li>


<li>
select AddressID, City, StateProvinceID, ModifiedDate <br/>
<div>
from Person.Address <br/>
<div>
where StateProvinceID = 32 <br/>
<b>SELECT</b> * <br/>
<b>FROM</b> Person.Person <br/>
<b>WHERE</b> LastName Like 'Duffy%'
</div>
<div>
<b>SELECT</b> * <br/>
<b>FROM</b> Person.Person <br/>
<b>WHERE</b> LastName Like '%Duffy'
</div>
<div>
<p>
Si bien ambas consultas son similares, recorren el mismo índice, y ejecutan un plan con algoritmos muy similares (ambos hacen Nested Loops y requieren hacer Key Look Up para recuperar el resto de campos de la tabla y cuestan lo mismo), mientras que la primera query es SARGeable, la segunda no lo es debido a que 'Duffy%' se puede buscar alfabéticamente (las palabras que sigan ese criterio estarían juntas en el índice), '%Duffy' no (las palabras que cumplen el segundo criterio podrían encontrarse en cualquier parte del índice). Esto obliga al motor en el segundo caso a hacer una búsqueda total del índice haciendo un scan completo, de palabras que cumplan, costoso.


En cambio el DBMS puede en el primer caso hacer un seek sencillo que usa la estructura del índice y llega a las hojas que cumplan hasta llegar a la última coincidencia, las cuales se encontrarán todas juntas en bloques de página contiguos.
select AddressID, City, StateProvinceID, ModifiedDate <br/>
</p>
from Person.Address <br/>
</div>
where StateProvinceID = 20
</div>
 
</li>
5)
</ol>
 
select count(UnitPrice) from sales.SalesOrderDetail; <br/>
select sum(UnitPrice) from sales.SalesOrderDetail;
 
6)
 
select * from Person.Person where LastName Like 'Duffy%' <br/>
select * from Person.Person where LastName Like '%Duffy'
Ten en cuenta que todas las contribuciones a Cuba-Wiki pueden ser editadas, modificadas o eliminadas por otros colaboradores. Si no deseas que las modifiquen sin limitaciones, no las publiques aquí.
Al mismo tiempo, asumimos que eres el autor de lo que escribiste, o lo copiaste de una fuente en el dominio público o con licencia libre (véase Cuba-Wiki:Derechos de autor para más detalles). ¡No uses textos con copyright sin permiso!

Para editar esta página, responde la pregunta que aparece abajo (más información):

Cancelar Ayuda de edición (se abre en una ventana nueva)
Obtenido de «https://www.cubawiki.com.ar/index.php/Taller_Opt_1er_Cuat_2022_Turno_1»