sql - ejemplos - SP toma 15 minutos, pero la misma consulta cuando se ejecuta devuelve resultados en 1-2 minutos

select top oracle

Question

Así que básicamente tengo este procedimiento almacenado relativamente largo. El flujo de ejecución básico es que SELECTS INTO algunos datos en tablas temporales declaradas con # signo # y luego ejecuta un cursor a través de estas tablas y genera un ''total acumulado'' en una tercera tabla temporal que se crea usando CREATE . Luego, esta tabla de temperatura resultante se une con otras tablas en la base de datos para generar el resultado después de alguna agrupación, etc. El problema es que este SP se ha estado ejecutando bien hasta ahora, y ha dado resultados en 1-2 minutos. Y ahora, de repente, está tomando 12-15 minutos. Si extraigo la consulta del SP y la ejecuto en el estudio de administración configurando manualmente los mismos parámetros, se obtienen resultados en 1-2 minutos, pero el SP tarda mucho tiempo. Cualquier idea de lo que podría estar pasando. Intenté generar los planes de ejecución reales tanto de la consulta como del SP, pero no se pudo generar debido al cursor. ¿Alguna idea de por qué el SP tarda tanto tiempo mientras que la consulta no?

Answer 1

1) Cuando ejecuta la consulta por primera vez, puede llevar más tiempo. Un punto más es si está utilizando alguna sub consulta relacionada y si está codificando los valores, se ejecutará solo una vez. Cuando no lo está codificando y ejecutando a través del procedimiento y si está tratando de derivar el valor del valor de entrada, puede llevar más tiempo.

2) En raras ocasiones, puede deberse al tráfico de la red, también donde no tendremos coherencia en el tiempo de ejecución de la consulta para los mismos datos de entrada.

Answer 2

Es debido a la detección de parámetros. En primer lugar, declare la variable temporal y establezca el valor de la variable entrante en variable temporal y use la variable temporal en toda la aplicación. A continuación, se muestra un ejemplo.

ALTER PROCEDURE [dbo].[Sp_GetAllCustomerRecords]
@customerId INT 
AS
declare @customerIdTemp INT
set @customerIdTemp = @customerId
BEGIN
SELECT * 
FROM   Customers e Where
CustomerId = @customerIdTemp 
End

prueba este enfoque

Answer 3

Esta es la huella de la detección de parámetros. Vea aquí para otra discusión al respecto; El rendimiento del plan de ejecución del procedimiento almacenado de SQL es deficiente - análisis de parámetros

Hay varias soluciones posibles, incluida la adición de WITH RECOMPILE a su procedimiento almacenado, que funciona aproximadamente la mitad del tiempo.

La solución recomendada para la mayoría de las situaciones (aunque depende de la estructura de su consulta y de su sproc) es NO usar sus parámetros directamente en sus consultas, sino almacenarlos en variables locales y luego usar esas variables en sus consultas.

Answer 4

Intente recompilar el sproc para deshacerse de cualquier plan de consulta almacenado

exec sp_recompile ''YourSproc''

A continuación, ejecute su sproc teniendo cuidado de utilizar parámetros sensibles.

También compare los planes de ejecución reales entre los dos métodos de ejecución de la consulta.

También podría valer la pena volver a calcular las estadísticas.

Answer 5

Para empezar, no suena como que el SQL va a funcionar demasiado bien de todos modos, basado en el uso de varias tablas temporales (podrían guardarse en la memoria, o persistir en tempdb, cualquiera que sea el SQL Server que decida que es mejor), y Uso de cursores.

Mi sugerencia sería ver si puede reescribir el sproc como una consulta basada en conjuntos en lugar de un enfoque de cursor que dará un mejor rendimiento y será mucho más fácil de ajustar y optimizar. Obviamente, no sé exactamente lo que hace tu sproc, para darte una idea de lo fácil / viable que es para ti.

En cuanto a por qué el SP está tomando más tiempo que la consulta, es difícil decirlo. ¿Hay la misma carga en el sistema cuando intenta cada enfoque? Si ejecuta la consulta cuando hay una carga ligera, será mejor que cuando ejecuta el SP durante una carga pesada.

Además, para garantizar que la consulta sea realmente más rápida que el SP, debe descartar el almacenamiento en caché del plan de datos / ejecución, lo que hace que una consulta sea más rápida para las ejecuciones posteriores. Puedes borrar el caché usando:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Pero solo haga esto en un servidor dev / test db, no en producción. Luego ejecute la consulta, registre las estadísticas (por ejemplo, desde el generador de perfiles). Borrar el caché de nuevo. Ejecutar el SP y comparar estadísticas.

Answer 6

Por lo general, empiezo a solucionar problemas de este tipo usando "print getdate () + ''- step''". Esto me ayuda a reducir lo que lleva más tiempo. Puede comparar desde donde lo ejecuta desde el analizador de consultas y definir dónde está el problema.

Answer 7

Supongo que podría ser hasta caché. Si ejecuta el procedimiento almacenado dos veces, ¿será más rápido la segunda vez?

Para investigar más a fondo, puede ejecutar desde el estudio de administración el procedimiento almacenado y la versión de la consulta con la opción de mostrar el plan de consulta activada en el estudio de administración, luego comparar qué área está demorando más en el procedimiento almacenado y luego ejecutar una consulta.

Alternativamente, puede publicar el procedimiento almacenado aquí para que las personas sugieran optimizaciones.

Answer 8

También me gustaría mirar en el parámetro sniffing. Podría ser el proc necesita para manejar los parámetros ligeramente diferente.

Answer 9

Yo sugeriría que el problema está relacionado con el tipo de tabla temporal (el # prefijo). Esta tabla temporal contiene los datos para esa sesión de base de datos. Cuando lo ejecuta a través de su aplicación, la tabla temporal se elimina y se vuelve a crear.
Puede encontrar que cuando se ejecuta en SSMS mantiene los datos de la sesión y actualiza la tabla en lugar de crearla. Espero que ayude :)

Answer 10

Yo también enfrenté un problema en el que tuvimos que crear algunas tablas temporales y luego manipularlas tuvo que calcular algunos valores basados ​​en reglas y, finalmente, insertar los valores calculados en una tercera tabla. Todo esto si se ponía en un solo SP tomaba alrededor de 20-25 min. Así que para optimizar aún más, dividimos el sp en 3 sp diferentes y el tiempo total que se toma ahora es de aproximadamente 6-8 minutos. Solo identifique los pasos que están involucrados en todo el proceso y cómo dividirlos en diferentes sp. Seguramente al utilizar este enfoque, se reducirá el tiempo total que toma todo el proceso.