EXECUTION PLAN Versión 1.0 2.0 2.1 3.0 4.0

Cambios Cómo se usa y que se mide Agregado: tipos de índices, principales operadores Se agregan más indicaciones por operador y cómo leer el execution plan Se agregan correcciones a pedido de mariana Se agrega ejemplo de optimización de una consulta

Fecha

26 abr 2011 13 may 2011

CONTENIDO como usar este documento ...........................................................................................................................................3 Entender el execution plan ............................................................................................................................................3 Los elementos gráficos ..............................................................................................................................................3 tipos de operadores ...................................................................................................................................................3 Tool tip(se despliega al pasar el puntero)..................................................................................................................3 leer el execution plan ................................................................................................................................................4 operadores más usados .................................................................................................................................................4 tipos de índices ..........................................................................................................................................................5 operadores unarios....................................................................................................................................................5 table scan ...............................................................................................................................................................5 clustered index scan ..............................................................................................................................................5 clustered index seek ..............................................................................................................................................6 non-clustered index seek .......................................................................................................................................6 key lookup/ RID lookup .........................................................................................................................................6 Nested Loop ...........................................................................................................................................................6 compute scalar ......................................................................................................................................................7 sort .........................................................................................................................................................................7

operadores binarios ...................................................................................................................................................7 hash match (join) ...................................................................................................................................................7 nested loop join .....................................................................................................................................................8 merge join ..............................................................................................................................................................8 operadores agregados ...............................................................................................................................................8 hash match (aggregate) .........................................................................................................................................8 filter .......................................................................................................................................................................8 consideraciones sobre insert, update y delete ..............................................................................................................8 ejemplo ..........................................................................................................................................................................9

COMO USAR ESTE DOCUMENTO Este documento se puede leer para aprender a usar el execution plan, o también como referencia cuando se está tratando de optimizar una consulta (o batch de consultas) en particular. Esto último quiere decir que estamos en la fase de prueba y queremos cerciorarnos de que la consulta sea eficiente (y tenemos un entorno de prueba apropiado en cantidad y complejidad de datos) o ya tenemos identificada una consulta problemática en nuestro sistema en producción (si aún no sabemos cuál es la consulta que está dando problemas, el documento sobre SQL profiler puede ser útil).

ENTENDER EL EXECUTION PLAN i LOS ELEMENTOS GRÁFICOS Los íconos: representan operadores Las flechas: representan datos que se pasan entre operadores. El grosor representa la cantidad de filas que se pasan. Al apoyar el mouse despliega la cantidad de filas. Porcentaje bajo los íconos: es el peso que tiene la operación respecto de la consulta completa. Costo: medido en tiempo.

TIPOS DE OPERADORES Azules: representan una ejecución de una consulta o instrucción DML, o sea INSERT, DELETE, SELECT, etc. Amarillos: operadores de cursor. Verdes: operadores de TransactSQL, por ejemplo WHILE, IF.

TOOL TIP(SE DESPLIEGA AL PASAR EL PUNTERO)

Estimated operator cost: es el peso que tiene la operación respecto de la consulta completa (el mismo valor que aparece bajo el ícono). Estimated Subtree Cost: es el costo acumulado entre esta operación y todas las del subárbol que está a la derecha (todas las que deben ser ejecutadas previamente). Estimated Number of Rows: la cantidad estimada de filas.

I/O cost: costo (en tiempo) de entrada salida. CPU cost: es el tiempo de procesador. Es razonable que este sea significativamente más bajo que el I/O cost, pero igual hay que estar alertas a valores elevados respecto de otros CPU cost de otras consultas. Esto quiere decir que va a demandar mucho procesador. Ordered: indica si se utilizó algún orden o no. Output list: la lista de columnas involucradas en los datos de salida Seek predicate: muestra como se hizo la búsqueda (como hace concordar).

LEER EL EXECUTION PLAN En primer lugar, se debe de identificar las consultas más pesadas dentro del batch que estamos leyendo. También estar atento a las consultas que no son tan pesadas respecto del batch pero si son pesadas respecto a lo que hacen (si son muy simples pero sin embargo ocupan un porcentaje no descartable del batch, por ejemplo en un batch con 5 consultas, una que sea simple pero ocupe más de 5%). Una vez que identificamos la consulta mirar cuales son los operadores que tienen mayor porcentaje y ver cómo se pueden mejorar con la sección operadores más usados. En particular estar atento a los siguientes operadores:     

Table scan Key scan Order Filter RID LookUP

OPERADORES MÁS USADOS Estos son los operadores más usados. Acá están documentados los que tienen fondo pintado. Select(result)

Sort

Clustered index seek

Clustered index scan and

Non clustered index scan

Non-clustered index seek

Table scan

Rid lookup

Key lookup

Hash match

Nested loops

Merge join

Top

Compute scalar

Constant scan

Filter

Lazy spool

Spool

Eager spool

Stream

Distribute streams

Repartition streams

Gather streams

Bitmap

Split

Algunos operadores requieren memoria disponible. Por lo tanto puede ser que esperen antes de arrancar a ejecutar. Algunos de estos operadores son: SORT, HASH MATCH, HASH JOIN

TIPOS DE ÍNDICES Hay varios tipos de índices. Los distintos tipos son: Clustered: un índice es clustered si los datos se guardan junto con el índice. De manera informal podemos decir que un índice es clustered si el nodo del árbol contiene además del valor de la columna por la que se está indizando, los valores de las restantes columnas. Un índice es non clústered si el árbol contiene solamente el contenido de la columna indizada y la pk. Unique o non-unique: si el conjunto de atributos por los que están indizando son una clave (no se pueden repetir valores). Covering (depende de la query): un índice es covering si la búsqueda se realiza a través de un índice que contiene todas las columnas que se necesitan para la consulta. Si un índice no lo es, se exige para completar la búsqueda que se busque el resto del contenido necesario (las otras columnas). Para esto último por lo general se recorre el índice clustered (normalmente la PK) a través de un key lookup. Siempre es bueno tener en cuenta que cuando se le hacen casteos o operaciones complicadas en la clausula where, puede impedir que se use un índice que contiene la columna que se está filtrando. Para evitar esto, seguir los criterios descritos en las recomendaciones.

OPERADORES UNARIOS TABLE SCAN

busca fila por fila en toda la tabla. Si la tabla es chica (pocas filas), es razonable que haga un table scan, porque es más lento calcular un plan mejor (el costo de computar el execution plan) que ya traerse la tabla y punto.

El table scan no es un elemento malo en sí, es malo cuando se realiza sobre una consulta frecuente o prioritaria y la cantidad de resultados devueltos desde el table scan es amplia. Para mejorar el rendimiento, lo primero que hay que evaluar es si ya existe un índice que podría ser usado y no lo es. En ese caso, hay que revisar las clausulas where y evaluar si pueden ser más restrictivas (devolver la mínima cantidad de filas necesarias para la consulta), también hay que evaluar si se pueden traer menos columnas en la clausula select. Si no hay un índice útil para esta consulta, considerar si se puede agregar uno, siempre teniendo en cuenta, que cada índice que se agregue va a degradar el rendimiento de las actualizaciones de la tabla, por lo que no es una buena práctica agregar índices sin pensar.

CLUSTERED INDEX SCAN

es similar a un table scan (con la salvedad de que se recorre un indice clusterizado): se recorre fila por fila buscando los datos y se devuelve un gran porcentaje de los mismos o la totalidad. Un index scan ocurre cuando un índice existe pero la cantidad de datos que se deben obtener es tan grande que es más eficiente directamente escanear los datos que usar las claves. Para mejorar el rendimiento: realizar los mismos pasos que para el table scan, ya sabiendo que existe un índice en la tabla.

CLUSTERED INDEX SEEK

A diferencia del index scan, el index seek sí saca partido de los índices. Los valores en los índices son utilizados rápidamente para distinguir las filas que integrarán la salida devuelta. Este tipo de operadores se utiliza cuando se indican condiciones que involucran a un índice clusterizado. Este operador es de los más eficientes operadores de búsqueda que hay.

NON-CLUSTERED INDEX SEEK

Es similar al clustered index seek pero, al índice no estar clusterizado, pueden necesitarse operaciones de entrada salida adicionales si el índice es non-covering (ya que no están contiguos al índice). Este tipo de operador es utilizado cuando las condiciones involucran a un indice no clusterizado. Este operador no representa un problema, salvo que sean muchas las filas devueltas, en ese caso ver si se puede refinar la clausula where para hacerla más restrictiva y reducir el número de columnas del select.

KEY LOOKUP/ RID LOOKUP

Es la búsqueda que se hace como resultado de la búsqueda por un índice non-covering para traer el resto de las columnas(del índice non covering me traje las columnas que tiene y ahora le tengo que agregar la información que me falta). Usa un clustered index (key lookup) o un identificador de fila (RID lookup) para traer los contenidos que no se pueden obtener con el index seek. Un key lookup puede estar indicando que se puede obtener beneficio de incluir un indice covering, o mejor aún de de tener menos columnas devueltas en el select. Pero cuidado que agregar índices degrada el rendimiento de las inserciones, modificaciones y eliminaciones.

NESTED LOOP

Un nested loop join combina los resultados de 2 operaciones, por ejemplo el resultado de una key lookup y un non-covering index seek. Esto no se optimiza, en su lugar se miran los lookup y clustered seek que está uniendo.

COMPUTE SCALAR Hace una cuenta con el contenido de una o varias columnas, linea por linea (por ejemplo multiplicar una columna de precios por 2). Para mejorar esto, ver si la cuenta se puede hacer más simple.

SORT

Ordena una tabla según el criterio en el campo order by del tool tip window. Si un sort ocupa más del 50% de una query, entonces hay que rever si el where está ajustado (no ordenar cosas que no sirven) y si el orden es realmente necesario o si no se puede ordenar por otra columna que ya tenga un índice. SQL intenta ordenar usando memoria, pero si los datos que hay que ordenar son muchos, necesita ordenar usando el disco y esto enlentece la ejecución (cuando esto ocurre el SQL profiler tira un sort warning event). Siempre hay que tratar de que un sort sea aplicado sobre un conjunto chico de filas.

OPERADORES BINARIOS En esta sección veremos los operadores que se utilizan cuando hacemos joins de tablas. Estos involucran dos tablas de entrada.

HASH MATCH (JOIN) Une dos tablas en un join. Hace una tabla de hash temporal para almacenar el contenido de la tabla más chica y la función de hash se aplica sobre el contenido de la(s) columna(s) a través de las cuales se va a hacer el join. Para cada fila de la tabla grande se busca (aplicando la función de hash al contenido de las columnas que se usan en el join) una fila de la tabla de hash temporal (hecha a partir de la tabla chica) para unir. Es útil: si una tabla es mucho más chica que la otra. Si las tablas no están ordenadas por la(s) columna(s) involucradas en el join. Si no hay índices utilizables. Puede indicar

que falta un índice o que se está usando mal Que falta una clausula where

que la clausula where contiene un casteo que impide la búsqueda por un índice existente. Si es alguno de estos el caso, las opciones son: Modificar la condición where para que se pueda usar el índice: sacar el nombre de columna para afuera de las funciones que se están aplicando. Por ejemplo cambiar 22 = (nombreColumna / 7) por nombreColumna =22*7. Agregar o modificar la clausula where para que sea lo más restrictiva posible. Agregar un índice (nonclustered) por la/s columnas que se está buscando.

NESTED LOOP JOIN Para cada fila del resultado del operador de arriba, escanea las filas del de abajo para ver cuál(es) sirve(n). Útil: si la cantidad de filas de la salida del de arriba es pequeña y las filas del de abajo están indizadas según lo que se va a machear. Si este no es el caso, se puede intentar tener menos filas en la de arriba (tunear la clausula where), agregar un índice en la de abajo o en las dos.

MERGE JOIN Un merge join se utiliza sobre tablas con las columnas en cuestión ordenadas por algo. Aprovechando el orden, no tiene que recorrer toda una tabla para cada fila de la otra, sino que va recorriendo las dos en orden y va juntando. Útil: cuando las tablas vienen con los datos ordenados (si no vienen ordenados el optimizador puede ordenarlas primero y despues hacer un merge join pero eso es muy ineficiente y puede ser mejor un hash join, pero esto es poco frecuente).

OPERADORES AGREGADOS HASH MATCH (AGGREGATE) Es lo mismo que un hash match hecho para un join (ver más atrás). Se usa para un group by.

FILTER

Tiene lugar cuando hay una clausula having. Este tipo de operación empeora el rendimiento ya que el filtro tiene lugar luego de aplicar los operadores agregados. Para mejorar la performance: ver si having se puede remplazar (al menos parcialmente) por una clausula where.

CONSIDERACIONES SOBRE INSERT, UPDATE Y DELETE

Como ya hemos anticipado, la mayor parte del trabajo a la hora de una inserción está en ubicar el elemento apropiadamente según los índices que puedan existir en una tabla (mucho más que el chequeo de restricciones o de integridades referenciales como puede ser una FK). Cuando hacemos updates lo que lleva más tiempo es encontrar los datos a actualizar en cuestión y lo segundo actualizar los índices si fue modificado algún campo indizado. Por lo tanto, hay que intentar que los índices sean hechos en columnas que no son plausibles de cambiar frecuentemente (por ejemplo una columna de estado).

EJEMPLO Vamos a ver un ejemplo de un sp hecho para generar un reporte. En este caso el reporte busca tener información de todos los clientes que figuran en la base de datos. En la tabla ContactImportNameValueData se encuentran los contactos que fueron importados. Los campos de la importación no se guardan en las columnas, sino que cada valor se guarda junto con su categorización. Es decir, en lugar de tener una fila (juan, 25, arquitecto) tenemos dos filas (juan, edad, 25) (juan, ocupación, arquitecto) Se busca que figuren en el reporte tanto los contactos que ya han sido llamados (que ya están ingresados en la tabla de gestión propia de esta campaña) como los que no lo han sido (que figuran solamente en la tabla ContactImportNameValueData). Para mostrar uniformemente estos dos tipos de contactos se hace un union entre dos tablas temporales. Esto nos exige “convertir” los contactos que figuran en contactImportNameValueData, al formato de la tabla de gestión donde toda la información correspondiente a un contacto se almacena en una sola fila. Nosotros nos concentraremos ahora en la tabla que corresponde a los que aún no han sido contactados, ya que son los más complicados de obtener y esta tabla fue la que mayores problemas ocasionaba en cuanto al rendimiento. La consulta que se había generado inicialmente era la siguiente (el código del procedimiento almacenado está disponible adjunto a este documento). Select @Campaign @BatchId isNull(ContactId,'') isNull(ID_REGISTRO.Value,'') isNull(CIC.Name,'') isNull(DIRECCION.Value,'') isNull(T_VIA.Value,'') isNull(CALLE.Value,'') isNull(NUMERO.Value,'') isNull(LOCALIDAD.Value,'') isNull(COD_POSTAL.Value,'') isNull(PROVINCIA.Value,'') isNull(TELEFONO_1.Value,'')

'Campaign', 'Lote', 'ContactId', 'ID_REGISTRO', 'NOMBRE', 'DIRECCION', 'T_VIA', 'CALLE', 'NUMERO', 'LOCALIDAD', 'COD_POSTAL', 'PROVINCIA', 'TELEFONO_1',

isNull(TELEFONO_2.Value,'') 'TELEFONO_2', isNull(TELEFONO_3.Value,'') 'TELEFONO_3', isNull(RAZON_1.Value,'') 'RAZON_1', isNull(ID_CLIENTE.Value,'') 'ID_CLIENTE', isNull(PAQUETE.Value,'') 'PAQUETE', isNull(MIGA.Value,'') 'MIGA', isNull(NODO.Value,'') 'NODO', isNull(CLASIFICACION_CLIENTE.Value,'') 'CLASIFICACION_CLIENTE', isNull(TECNOLOGIA.Value,'') 'TECNOLOGIA', isNull(EMPRESA.Value,'') 'EMPRESA', isNull(FECHA.Value,'') 'FECHA', isNull(NIF_CIF.Value,'') 'NIF_CIF', isNull(FECHA_FIN.Value,'') 'FECHA_FIN', '' 'CUALIFICACION', '' 'OPERADOR_ACTUAL', '' 'OTROS_OPERADORES', '' 'INTERNET', '' 'TV', '' 'EMAIL', '' 'RESULTADO1', '' 'RESULTADO2', '' 'RESULTADO3', '' 'RESULTADO4', '' 'PERMANENCIA' Into #TablaNoGestionados From MMProDat..OutboundProcessContact OPC (Nolock) Join MMProDat..ContactImportContact CIC (Nolock) On (OPC.ContactId = CIC.Id) Left Join MMProDat..ContactImportNameValueData ID_REGISTRO (Nolock) On (OPC.ContactId = ID_REGISTRO.ID And ID_REGISTRO.Name = 'ID_REGISTRO') Left Join MMProDat..ContactImportNameValueData DIRECCION (Nolock) On (OPC.ContactId = DIRECCION.ID And DIRECCION.Name = 'DIRECCION') Left Join MMProDat..ContactImportNameValueData T_VIA (Nolock) On (OPC.ContactId = T_VIA.ID And T_VIA.Name = 'T_VIA') Left Join MMProDat..ContactImportNameValueData CALLE (Nolock) On (OPC.ContactId = CALLE.ID And CALLE.Name = 'CALLE') Left Join MMProDat..ContactImportNameValueData NUMERO (Nolock) On (OPC.ContactId = NUMERO.ID And NUMERO.Name = 'NUMERO') Left Join MMProDat..ContactImportNameValueData LOCALIDAD (Nolock) On (OPC.ContactId = LOCALIDAD.ID And LOCALIDAD.Name = 'LOCALIDAD') Left Join MMProDat..ContactImportNameValueData COD_POSTAL (Nolock) On (OPC.ContactId = COD_POSTAL.ID And COD_POSTAL.Name = 'COD_POSTAL') Left Join MMProDat..ContactImportNameValueData PROVINCIA (Nolock) On (OPC.ContactId = PROVINCIA.ID And PROVINCIA.Name = 'PROVINCIA') Left Join MMProDat..ContactImportNameValueData TELEFONO_1 (Nolock) On (OPC.ContactId = TELEFONO_1.ID And TELEFONO_1.Name = 'TELEFONO_1') Left Join MMProDat..ContactImportNameValueData TELEFONO_2 (Nolock) On (OPC.ContactId = TELEFONO_2.ID And TELEFONO_2.Name = 'TELEFONO_2') Left Join MMProDat..ContactImportNameValueData TELEFONO_3 (Nolock) On (OPC.ContactId = TELEFONO_3.ID And TELEFONO_3.Name = 'TELEFONO_3') Left Join MMProDat..ContactImportNameValueData RAZON_1 (Nolock) On (OPC.ContactId = RAZON_1.ID And RAZON_1.Name = 'RAZON_1') Left Join MMProDat..ContactImportNameValueData ID_CLIENTE (Nolock) On (OPC.ContactId = ID_CLIENTE.ID And ID_CLIENTE.Name = 'ID_CLIENTE') Left Join MMProDat..ContactImportNameValueData PAQUETE (Nolock) On (OPC.ContactId = PAQUETE.ID And PAQUETE.Name = 'PAQUETE')

Left Join MMProDat..ContactImportNameValueData MIGA (Nolock) On (OPC.ContactId = MIGA.ID And MIGA.Name = 'MIGA') Left Join MMProDat..ContactImportNameValueData NODO (Nolock) On (OPC.ContactId = NODO.ID And NODO.Name = 'NODO') Left Join MMProDat..ContactImportNameValueData CLASIFICACION_CLIENTE (Nolock) On (OPC.ContactId = CLASIFICACION_CLIENTE.ID And CLASIFICACION_CLIENTE.Name = 'CLASIFICACION_CLIENTE') Left Join MMProDat..ContactImportNameValueData TECNOLOGIA (Nolock) On (OPC.ContactId = TECNOLOGIA.ID And TECNOLOGIA.Name = 'TECNOLOGIA') Left Join MMProDat..ContactImportNameValueData EMPRESA (Nolock) On (OPC.ContactId = EMPRESA.ID And EMPRESA.Name = 'EMPRESA') Left Join MMProDat..ContactImportNameValueData FECHA (Nolock) On (OPC.ContactId = FECHA.ID And FECHA.Name = 'FECHA') Left Join MMProDat..ContactImportNameValueData NIF_CIF (Nolock) On (OPC.ContactId = NIF_CIF.ID And NIF_CIF.Name = 'NIF_CIF') Left Join MMProDat..ContactImportNameValueData FECHA_FIN (Nolock) On (OPC.ContactId = FECHA_FIN.ID And FECHA_FIN.Name = 'FECHA_FIN') Where OPC.VCC = @VirtualCC And OPC.BatchId = @BatchId And OPC.ContactId Not In ( Select ContactId From #TablaGestionados (Nolock) )

Lo primero que observamos en el execution plan (que también está disponible adjunto a este documento) fue el valor de subtree cost asociado al operador raíz (el select principal). El mismo era de 599, que coincide con que la consulta ejecutara lentamente. Luego constatamos que no había operadores que concentraran alto porcentaje de costo, en su lugar encontramos muchos operadores index scan con costo 4%. A continuación vemos uno de ellos. No incluimos el execution plan completo por una razón de tamaño.

Luego de cada index scan se realizaba un outer join. El index scan se realizaba con el fin de buscar los candidatos para el join. Por ejemplo en el tooltip que vemos abajo que corresponde a uno de estos index scan, se buscan todos los pares (id, valor) de la tabla ContactImportNameValueData que correspondieran al campo id_registro de la importación.

Es decir para cada campo buscado se recorría una vez la tabla ContactImportNameValueData, lo cual resultó ser muy ineficiente. En su lugar debíamos conseguir que se recorriera una sola vez la tabla y se trajeran todos los elementos necesarios para hacer todos los joins. Para evitar multiples recorridas de la tabla completa, una primera opción sería hacer una sola recorrida y guardar los valores necesarios (nombre, id, valor) en una tabla temporal indizada por (nombre, id) para luego hacer los joins como antes y que se ejecuten rápidamente porque tienen un índice apropiado. Esta opción recorre una sola vez la tabla pero sigue siendo muy ineficiente porque insertar ordenadamente en la tabla temporal (que es necesario por la existencia del índice) una cantidad grande de filas consume muchos recursos y se ejecuta muy lentamente. Sin embargo, existe otra posibilidad que es directamente eliminar la gran cantidad de joins. Esto es posible utilizando una tabla derivada, como describimos enseguida. La intención es armar una columna por campo y llenarlas con los valores que figuran en la tabla. Así, una fila de la tabla derivada se forma a partir de varias filas de la tabla contactImportNameValueData (tantas como columnas tengo). O sea, queremos que la tabla derivada contenga todos los datos necesarios, pero en lugar de ser una subtabla de la tabla contactImportNameValueData, la crearemos de tal forma que ya tenga los datos dispuestos en columnas. Lo que haremos es replicar la columna valor varias veces (tantas como campos tengo) y obtener algo

algo de esta forma, que luego “aplastaríamos” en un group by. Observe que hay varias filas correspondientes a cada contacto. contacto Juan

Id registro

direccion

Teléfono

…

1

Juan

Rivera 1234

Juan

29001234

Martín Martín Martín

2 Av. Italia 5678 26005678

…….. Para lograr que aparecieran vacíos los valores que corresponden (los que no están en la diagonal), usamos una expresión case, poniendo el string ‘ ‘ si el valor no corresponde a la columna que estamos llenando. CASE WHEN isNull(CINVD.NAME,'') = 'ID_REGISTRO' THEN isNull(CINVD.Value,'') ELSE '' END Luego, para que al realizar el group by se tomen los valores que corresponden (elegir entre la celda que tiene sentido entre las demás de la columna que son vacías) usamos la función agrupada max. MAX(CASE WHEN isNull(CINVD.NAME,'') = 'ID_REGISTRO' THEN isNull(CINVD.Value,'') ELSE '' END) Esta es la consulta modificada completa (que también se encuentra adjunta). @Campaign 'Campaign', @BatchId 'Lote', isNull(ContactId,'') 'ContactId', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'ID_REGISTRO' THEN isNull(CINVD.Value,'') ELSE '' END) 'ID_REGISTRO', MAX(isNull(CIC.Name,'')) 'NOMBRE', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'DIRECCION' THEN isNull(CINVD.Value,'') ELSE '' END) 'DIRECCION', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'T_VIA' THEN isNull(CINVD.Value,'') ELSE '' END) 'T_VIA', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'CALLE' THEN isNull(CINVD.Value,'') ELSE '' END) 'CALLE', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'NUMERO' THEN isNull(CINVD.Value,'') ELSE '' END) 'NUMERO', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'LOCALIDAD' THEN isNull(CINVD.Value,'') ELSE '' END) 'LOCALIDAD', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'COD_POSTAL' THEN isNull(CINVD.Value,'') ELSE '' END) 'COD_POSTAL', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'PROVINCIA' THEN isNull(CINVD.Value,'') ELSE '' END) 'PROVINCIA',

MAX(CASE WHEN isNull(CINVD.NAME,'') = 'TELEFONO_1' THEN isNull(CINVD.Value,'') ELSE '' END) 'TELEFONO_1', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'TELEFONO_2' THEN isNull(CINVD.Value,'') ELSE '' END) 'TELEFONO_2', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'TELEFONO_3' THEN isNull(CINVD.Value,'') ELSE '' END) 'TELEFONO_3', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'RAZON_1' THEN isNull(CINVD.Value,'') ELSE '' END) 'RAZON_1', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'ID_CLIENTE' THEN isNull(CINVD.Value,'') ELSE '' END) 'ID_CLIENTE', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'PAQUETE' . THEN isNull(CINVD.Value,'') ELSE '' END) 'PAQUETE', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'MIGA' THEN isNull(CINVD.Value,'') ELSE '' END) 'MIGA', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'NODO' THEN isNull(CINVD.Value,'') ELSE '' END) 'NODO', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'CLASIFICACION_CLIENTE' THEN isNull(CINVD.Value,'') ELSE '' END) 'CLASIFICACION_CLIENTE', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'TECNOLOGIA' THEN isNull(CINVD.Value,'') ELSE '' END) 'TECNOLOGIA', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'EMPRESA' THEN isNull(CINVD.Value,'') ELSE '' END) 'EMPRESA', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'FECHA' THEN isNull(CINVD.Value,'') ELSE '' END) 'FECHA', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'NIF_CIF' THEN isNull(CINVD.Value,'') ELSE '' END) 'NIF_CIF', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'FECHA_FIN' THEN isNull(CINVD.Value,'') ELSE '' END) 'FECHA_FIN', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'CUALIFICACION' THEN isNull(CINVD.Value,'') ELSE '' END) 'CUALIFICACION', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'OPERADOR_ACTUAL' THEN isNull(CINVD.Value,'') ELSE '' END) 'OPERADOR_ACTUAL', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'OTROS_OPERADORES' THEN isNull(CINVD.Value,'') ELSE '' END) 'OTROS_OPERADORES', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'INTERNET' THEN isNull(CINVD.Value,'') ELSE '' END) 'INTERNET', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'TV' THEN isNull(CINVD.Value,'') ELSE '' END) 'TV', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'EMAIL' THEN isNull(CINVD.Value,'') ELSE '' END) 'EMAIL', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'RESULTADO1' THEN isNull(CINVD.Value,'') ELSE '' END) 'RESULTADO1', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'RESULTADO2' THEN isNull(CINVD.Value,'') ELSE '' END) 'RESULTADO2', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'RESULTADO3' THEN isNull(CINVD.Value,'') ELSE '' END) 'RESULTADO3', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'RESULTADO4' THEN isNull(CINVD.Value,'') ELSE '' END) 'RESULTADO4', MAX(CASE WHEN isNull(CINVD.NAME,'') = 'PERMANENCIA' THEN isNull(CINVD.Value,'') ELSE '' END) 'PERMANENCIA' Into #TablaNoGestionados From MMProDat..OutboundProcessContact OPC (Nolock) Join MMProDat..ContactImportContact CIC (Nolock) On (OPC.ContactId = CIC.Id) Left Join MMProDat..ContactImportNameValueData CINVD (Nolock) On (OPC.ContactId = CINVD.ID)

Where OPC.VCC = @VirtualCC And OPC.BatchId = @BatchId And OPC.ContactId Not In ( Select ContactId From #TablaGestionados (Nolock) ) GROUP BY OPC.ContactId El execution plan de esta consulta se encuentra adjunto a este documento. Si miramos el estimated subtree cost del select principal, veremos que reducimos diez veces el costo: pasó de 599 a 58. En lugar de los muchos index scan que observábamos anteriormente, obtuvimos un único table scan con un costo apenas mayor que uno sólo de los index scan anteriores: 26 contra 24. Claramente se utilizó más procesamiento y memoria, al tener que realizar un hash join en lugar de un merge join (porque no teníamos ningún orden para aprovechar en la tabla derivada) pero esto no repercutió en un alto costo porque como ya mencionamos, la consulta se realiza 10 veces más rápido. Para acelerar aún más esta consulta podríamos considerar la inclusión o modificación de índices, pero esto es imposible ya que la tabla ContactImportNameValueData pertenece a la solución general de inconcert, y no se debe modificar.

i

Tomado de http://www.simple-talk.com/sql/performance/execution-plan-basics/ y pertenece al libro Dissecting SQL Server Execution Plans. http://technet.microsoft.com/en-us/library/ms189858%28SQL.90%29.aspx como ver la fragmentación de los índices