No se puede probar la consulta, pero se necesita la mejor estrategia d

No se puede probar la consulta, pero se necesita la mejor estrategia de indexación para la cláusula WHERE OR.. OR.. OR

Una aplicación ejecuta una rutina una vez a la semana. La última ejecución fue extremadamente lenta y sabemos que se procesarán más datos durante la próxima ejecución. La instrucción más lenta de la rutina, con diferencia, une una tabla temporal a una tabla de productos. Profiler muestra un número muy alto de lecturas, lo que sugiere que no está bien indexado. Durante la última ejecución, la tabla Productos tenía 200 000 filas y la tabla temporal tenía 1200.

 update tmp      
 set tmp.col1 = pd.col1, 
     tmp.col2 = pd.col2, 
     tmp.col3 = pd.col3 
 from #temptable tmp
, Products pd with (nolock)      
 where tmp.col2 = pd.col2 
    or tmp.col2 = pd.col3 
    or tmp.col2 = pd.col4       
    or tmp.col2 = pd.col5 

Solo tengo una oportunidad de aplicar una estrategia de indexación. La tabla temporal se genera a partir de datos que solo existen durante un breve período de tiempo y no existe ninguna copia, por lo que no se puede volver a crear antes de la próxima ejecución. El caché del plan no tiene un plan de ejecución.

La consulta debe actualizarse a ANSI-92, pero la trato como se encontró.

La tabla de productos tiene índices en cada una de las columnas col2, col3, col4, col5, pero no cubre compuestos ni INCLUYE para los valores de actualización.

La tabla temporal no tiene indexación.

No he probado nada porque no hay forma de probar antes de la próxima ejecución en vivo.

¿Alguien puede aconsejarme si debo aplicar un índice compuesto que cubra las 4 columnas de Producto o usar cuatro índices, uno para cada columna e INCLUYE para col1, col2 y col3?

Mostrar la mejor respuesta
Avatar Ven

su generador de perfiles debería haberle dado el índice requerido y el impacto promedio a menos que tenga un reinicio programado del servidor. En primer lugar, necesitarías una unión interna

¿Tiene índices separados en products.col2, products.col3, products.col4 y products.col5? el #temptable.col2 también debe indexarse.

Publique también el plan de consulta y los tamaños de la tabla.

Tiene que ser 1 actualización o puedes hacer 4?

@Ven Profiler no tenía activado SHOWPLANXML.

Avatar Ven

¿Su consulta se ejecuta como proceso o simplemente como consulta por sí sola?

Los índices de @RadimBačaSeparate están en Productos cols2-4. Pensé que, a medida que se actualizaba Temptable, escanearía de todos modos. Sin plan de consulta. tamaños 200.000 filas y 1200 mencionados en la pregunta anterior.

@EzequielLópezPetrucci Podrían ser 4 consultas, si eso ayudara.

@Ven La declaración es parte de un procedimiento almacenado.

He corregido los nombres de mis columnas en la publicación.

Realmente no creo que no se pueda replicar en otro sistema, incluso con datos simulados, debería poder replicar los problemas en otros lugares y probarlos correctamente.

@Andrew Solo pensando eso. Estoy intentando construir una tabla con 1200 valores aleatorios que corresponden a una de las 4 columnas de Producto. Pero sería bueno si hubiera una respuesta correcta derivada de la teoría, no de la práctica, para ayudarme a comprender.

Avatar Ven

Su pregunta tiene limitaciones ya que no puede obtener el showplan, prefiero seguir estos enfoques. Nota: esto da una pista de índices y consultas costosas si su servidor no se reinicia desde la última vez que se ejecutó su proceso.

Si utiliza la combinación interna de la tabla temporal con la tabla de productos, mejorará la ejecución de consultas.

Siga estos enfoques

1) Verifique la consulta más costosa por query_stats, use el siguiente script para aislar si ese proceso almacenado está en la lista de consultas costosas

SELECT TOP 5 t.TEXT AS 'SQL Text'
    ,st.execution_count
    ,ISNULL(st.total_elapsed_time / st.execution_count, 0) AS 'AVG Excecution Time'
    ,st.total_worker_time / st.execution_count AS 'AVG Worker Time'
    ,st.total_worker_time
    ,st.max_logical_reads
    ,st.max_logical_writes
    ,st.creation_time
    ,ISNULL(st.execution_count / DATEDIFF(second, st.creation_time, getdate()), 0) AS 'Calls Per Second'
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t
ORDER BY st.total_elapsed_time DESC

2) También verifique los requisitos de índice para cualquier consulta reciente ejecutada en el servidor, este es un script excelente de Pinal que nos brinda la necesidad de índices, si no puede volver a ejecutar la consulta manualmente o no tiene un plan de presentación https://blog.sqlauthority .com/2011/01/03/sql-server-2008-missing-index-script-download/

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Ps: esta solución es solo una indicación de los índices requeridos, principalmente use su intuición

La declaración no apareció en query_stats y los índices en el script de Pinal no estaban relacionados con el problema. De lo contrario, esas son excelentes técnicas que usaré nuevamente.

Sin tener estadísticas sobre los tiempos de ejecución o los planes, y teniendo en cuenta que desea acelerar lo más rápido posible (incluso con un aumento en el tamaño del índice), sugeriría crear 4 índices de cobertura (uno en cada columna)

CREATE NONCLUSTERED INDEX NCI_Products_col2 ON Products (col2) INCLUDE (col1, col3)
CREATE NONCLUSTERED INDEX NCI_Products_col3 ON Products (col3) INCLUDE (col1, col2)
CREATE NONCLUSTERED INDEX NCI_Products_col4 ON Products (col4) INCLUDE (col1, col2, col3)
CREATE NONCLUSTERED INDEX NCI_Products_col5 ON Products (col5) INCLUDE (col1, col2, col3)

Y dividiendo tu actualización en 4 pasos:

-- join by col2 (skip tmp.col2 update)
update tmp set 
    tmp.col1 = pd.col1, 
    tmp.col3 = pd.col3 
from 
    #temptable tmp
    INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col2 


-- join by col3
update tmp set 
    tmp.col1 = pd.col1, 
    tmp.col2 = pd.col2, 
    tmp.col3 = pd.col3 
from 
    #temptable tmp
    INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col3

-- join by col4
update tmp set 
    tmp.col1 = pd.col1, 
    tmp.col2 = pd.col2, 
    tmp.col3 = pd.col3 
from 
    #temptable tmp
    INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col4

-- join by col5
update tmp set 
    tmp.col1 = pd.col1, 
    tmp.col2 = pd.col2, 
    tmp.col3 = pd.col3 
from 
    #temptable tmp
    INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col5

Entonces, al eliminar las uniones OR, cada actualización debería usar su índice de cobertura. Es posible que deba ajustar la transacción si necesita que la operación se maneje en su totalidad.

Verifique el orden de actualización, ejecutar en un orden diferente puede generar resultados diferentes según la naturaleza de su consulta original (si tmp col2 se puede encontrar en Product col3 y col5 para registros diferentes, los valores para actualizar pueden ser diferentes).

Gracias. Hice un tentable simulado y lo apliqué sin los índices. Corrió al instante. El plan de ejecución mostró que el original estaba atascado en un Nested Loop (97%). Pondré mis hallazgos en una actualización más tarde.