procedimientos - ¿Es un procedimiento almacenado SQL dinámico algo malo para muchos registros?

sql dinamico sql

Question

Tengo una tabla con casi 800,000 registros y actualmente estoy usando SQL dinámico para generar la consulta en el back-end. La interfaz es una página de búsqueda que toma alrededor de 20 parámetros y, dependiendo de si se eligió un parámetro, agrega un "Y ..." a la consulta base. Tengo curiosidad sobre si el sql dinámico es el camino correcto (no lo parece porque funciona lento). Estoy pensando en crear una tabla desnormalizada con todos mis datos. ¿Es esta una buena idea o debería simplemente construir la consulta todos juntos en lugar de compilarla pieza por pieza utilizando el sql dinámico? Lo último, ¿hay alguna manera de acelerar el sql dinámico?

Answer 1

Como respuesta anterior, verifique sus índices y plan.

La pregunta es si estás usando un procedimiento almacenado. No es obvio por la forma en que lo redactaste. Un procedimiento almacenado crea un plan de consulta cuando se ejecuta y mantiene ese plan hasta que se vuelva a compilar. Con SQL variable, es posible que tenga un mal plan de consulta. Puedes hacer varias cosas:

1) Agregue WITH RECOMPILE a la definición del SP, lo que causará que se genere un nuevo plan con cada ejecución. Esto incluye algunos gastos generales, que pueden ser aceptables.

2) Use SP separados, dependiendo de los parámetros proporcionados. Esto permitirá un mejor almacenamiento en caché del plan de consulta

3) Usar SQL generado por el cliente. Esto creará un plan de consulta cada vez. Si utiliza consultas parametrizadas, esto puede permitirle usar planes de consultas en caché.

Answer 2

Es más probable que su indexación (o la falta de ella) esté causando la lentitud que el SQL dinámico.

¿Cómo se ve el plan de ejecución? ¿Es la misma consulta lenta cuando se ejecuta en SSMS? ¿Qué pasa cuando está en un procedimiento almacenado?

Si su tabla es un montón no indexado, tendrá un rendimiento bajo a medida que crezca la cantidad de registros, independientemente de la consulta, y una consulta dinámica puede funcionar mejor a medida que la naturaleza de la tabla cambia porque es más probable que una consulta dinámica tenga su consulta plan reevaluado cuando no está en la memoria caché. Normalmente, esto no es un problema (y no lo clasificaría como una ventaja de diseño de las consultas dinámicas) excepto en las primeras etapas de un sistema cuando los SP no se han recompilado, pero las estadísticas y los planes de consulta están desactualizados, pero el volumen de los datos han cambiado drásticamente.

No es el estático todavía. Tengo con la consulta dinámica, pero no da ninguna optimización. Si lo ejecutaba con la consulta estática y daba sugerencias, ¿la aplicación afectaría a la consulta dinámica? - Xaisoft (hace 41 minutos)

Sí, la consulta dinámica (EXEC (@sql)) probablemente no se analizará a menos que haya analizado un archivo de carga de trabajo. - Cade Roux (hace 33 minutos)

Cuando tiene una consulta de búsqueda en varias tablas unidas, las columnas con índices deben ser las columnas de búsqueda, así como la clave principal / clave externa, pero depende de la cardinalidad de las diversas tablas. El analizador de sintonización debería mostrar esto. - Cade Roux (hace 22 minutos)

Answer 3

La única diferencia entre SQL "dinámico" y "estático" es la fase de análisis / optimización. Una vez hecho esto, la consulta se ejecutará de manera idéntica.

Para consultas simples, esta fase de análisis más el tráfico de red resulta ser un porcentaje significativo del tiempo de transacción total, por lo que es una buena práctica intentar reducir estos tiempos.

Pero para consultas grandes y complicadas, este procesamiento es insignificante en comparación con la ruta real elegida por el optimizador.

Me centraría en la optimización de la consulta en sí, incluida la desnormalización si crees que es apropiada, aunque no lo haría en un primer momento.

En ocasiones, la desnormalización se puede realizar en "tiempo de ejecución" en la aplicación utilizando tablas de búsqueda almacenadas en caché, por ejemplo, en lugar de mantenerla en la base de datos.

Answer 4

No es un fanático de Sql dinámico, pero si está atrapado con él, probablemente debería leer este artículo: http://www.sommarskog.se/dynamic_sql.html Él realmente profundiza en las mejores formas de utilizar SQL dinámico y los problemas usarlo puede crear.

Como otros han dicho, la indexación es el culpable más probable. En la indexación, una cosa que la gente a menudo olvida hacer es poner un índice en los campos FK. Como un PK crea un índice automáticamente, muchos suponen que también lo hará un FK. Lamentablemente, crear un FK no permite crear un índice. Por lo tanto, asegúrese de que los campos en los que se une estén indexados.

Puede haber mejores formas de crear su SQL dinámico, pero sin ver el código es difícil de decir. Al menos buscaría si está utilizando subconsultas y las reemplazaría con combinaciones de tablas derivadas. Además, cualquier SQl dinámico que use un cursor está destinado a ser lento.

Answer 5

Si los parámetros son opcionales, un truco que se usa a menudo es crear un procedimiento como este:

CREATE PROCEDURE GetArticlesByAuthor (
    @AuthorId int,
    @EarliestDate datetime = Null )
AS
   SELECT  * --not in production code!
   FROM Articles
   WHERE AuthorId = @AuthorId
   AND (@EarliestDate is Null OR PublishedDate < @EarliestDate)

Answer 6

Solo me gustaría señalar que si usa este estilo de parámetros opcionales:

AND (@EarliestDate is Null OR PublishedDate < @EarliestDate)

El optimizador de consultas no tendrá idea de si el parámetro está allí o no cuando produce el plan de consulta. He visto casos en los que el optimizador toma malas decisiones en estos casos. Una mejor solución es construir el sql que use solo los parámetros que necesita. El optimizador hará el plan de ejecución más eficiente en estos casos. Asegúrese de utilizar consultas parametrizadas para que sean reutilizables en la memoria caché del plan.

Answer 7

He tenido cierto éxito (en un número limitado de casos) con la siguiente lógica:

CREATE PROCEDURE GetArticlesByAuthor (    
    @AuthorId int,    
    @EarliestDate datetime = Null 
    ) AS   

SELECT SomeColumn
FROM Articles   
WHERE AuthorId = @AuthorId   
AND @EarliestDate is Null
UNION
SELECT SomeColumn
FROM Articles   
WHERE AuthorId = @AuthorId   
AND PublishedDate < @EarliestDate

Answer 8

Si está tratando de optimizar por debajo del rango de 1s, puede ser importante medir aproximadamente cuánto tiempo lleva analizar y compilar el sql dinámico relativo al tiempo de ejecución de la consulta real:

 SET STATISTICS TIME ON;

y luego ejecuta la cadena SQL dinámica "estáticamente" y verifica la pestaña "Mensajes". Me sorprendieron estos resultados de una consulta sql dinámica de ~ 10 líneas que devuelve dos filas de una tabla de filas de 1M:

 SQL Server parse and compile time: 
    CPU time = 199 ms, elapsed time = 199 ms.

 (2 row(s) affected)

 SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 4 ms.

La optimización del índice moverá mucho la barrera de 199 ms (excepto quizás debido a algún análisis / optimización incluido dentro del tiempo de compilación).

Sin embargo, si el SQL dinámico usa parámetros o se repite, los resultados de la compilación se pueden almacenar en caché de acuerdo con: Consulte Caching Query Plans que eliminaría el tiempo de compilación. Sería interesante saber cuánto tiempo viven las entradas de caché, el tamaño, las sesiones compartidas, etc.

Answer 9

Aquí hay algunos buenos ejemplos de consultas con criterios de búsqueda opcionales: ¿Cómo creo un procedimiento almacenado que opcionalmente buscará columnas?

Answer 10

Como se señaló, si realiza una consulta masiva, los índices son el primer cuello de botella que se debe tener en cuenta. Asegúrese de que las columnas muy consultadas estén indexadas. Además, asegúrese de que su consulta verifique todos los parámetros indexados antes de que verifique los parámetros no indexados. Esto asegura que los resultados se filtren usando índices primero y luego realiza la búsqueda lineal lenta solo si es necesario. Entonces, si col2 está indexado pero col1 no, debería verse de la siguiente manera:

WHERE col2 = @col2 AND col1 = @col1

También puede estar tentado de exagerar con los índices, pero tenga en cuenta que demasiados índices pueden causar escrituras lentas y un uso masivo del disco, así que no se vuelva demasiado loco.

Evito consultas dinámicas si puedo por dos razones. Primero, no guardan el plan de consulta, por lo que la declaración se compila cada vez. El otro es que son difíciles de manipular, probar y solucionar. (Ellos simplemente se ven feos).

Me gusta la respuesta de Dave Kemp anterior.