El mejor método para implementar una búsqueda filtrada

Question

El mejor método para implementar una búsqueda filtrada

#1 de StuartLC (9 votos)
#2 de Doc Brown (4 votos)

13

Me gustaría preguntarle su opinión cuando se trata de implementar un formulario de búsqueda filtrado. Imaginemos el siguiente caso:

1 mesa grande con muchas columnas
Podría ser importante decir que este servidor SQL

Debe implementar un formulario para buscar datos en esta tabla, y en este formulario tendrá varias casillas de verificación que le permitirán personalizar esta búsqueda.

Ahora mi pregunta aquí es ¿cuál de las siguientes debería ser la mejor manera de implementar la búsqueda?

Crea un procedimiento almacenado con una consulta dentro. Este procedimiento almacenado verificará si la aplicación proporciona los parámetros y, en caso de no recibirlos, se colocará un comodín en la consulta.
Cree una consulta dinámica, que se construya de acuerdo con lo que proporciona la aplicación.

Lo pregunto porque sé que SQL Server crea un plan de ejecución cuando se crea el procedimiento almacenado, para optimizar su rendimiento. Sin embargo, al crear una consulta dinámica dentro del procedimiento almacenado, sacrificaremos la optimización obtenida por plan de ejecución?

Por favor, dígame cuál sería el mejor enfoque en su opinión.

sql database methodology sql-server development-approach

pregunta j0N45 08.08.2012 - 17:47

2 respuestas

Lea otras preguntas en las etiquetas sql database methodology sql-server development-approach

¿Debo refactorizar mis pruebas unitarias cuando extraigo una clase del Sistema a prueba? La estática es mala, pero ¿qué pasa con el patrón de fábrica?

score 9 · Answer 1

Es posible que desee ver la respuesta a esta pregunta similar aquí: enlace

Hemos encontrado que un SPROC que toma un conjunto de parámetros opcionales e implementa el filtro de esta manera:

CREATE PROC MyProc (@optionalParam1 NVARCHAR(50)=NULL, @optionalParam2 INT=NULL)
AS 
...
SELECT field1, field2, ... FROM [Table]
WHERE 
  (@optionalParam1 IS NULL OR MyColumn1 = @optionalParam1)
  AND (@optionalParam2 IS NULL OR MyColumn2 = @optionalParam2)

almacenará en caché el primer plan de ejecución con el que se ejecuta (por ejemplo, @optionalParam1 = 'Hello World', @optionalParam2 = NULL ), pero luego ejecutará miserablemente si le pasamos un conjunto diferente de parámetros opcionales (por ejemplo, @optionalParam1 = NULL, @optionalParam2 = 42 ). (Y obviamente queremos el rendimiento del plan almacenado en caché, por lo que WITH RECOMPILE está fuera)

La excepción aquí es que si TAMBIÉN hay al menos un filtro OBLIGATORIO en la consulta que es ALTAMENTE selectivo e indexado correctamente, además de los parámetros opcionales, entonces el PROC anterior funcionará bien.

Sin embargo, si TODOS los filtros son opcionales, la terrible verdad es que el sql dinámico parametrizado funciona mejor (a menos que escriba N! PROCS estático diferente para cada permutación de parámetros opcionales).

El SQL dinámico como el siguiente creará y almacenará en caché un plan diferente para cada permutación de los parámetros de la Consulta, pero al menos cada plan se 'adaptará' a la consulta específica (no importa si es un PROC o Adhoc SQL: siempre que sean consultas parametrizadas, se almacenarán en caché)

Por lo tanto, mi preferencia por:

DECLARE @SQL NVARCHAR(MAX)        

-- Mandatory / Static part of the Query here
SET @SQL = N'SELECT * FROM [table] WHERE 1 = 1'

IF @OptionalParam1 IS NOT NULL        
    BEGIN        
        SET @SQL = @SQL + N' AND MyColumn1 = @optionalParam1'    
    END        

IF @OptionalParam2 IS NOT NULL        
    BEGIN        
        SET @SQL = @SQL + N' AND MyColumn2 = @optionalParam2'    
    END        

EXEC sp_executesql @SQL,        
    N'@optionalParam1 NVARCHAR(50), 
      @optionalParam2 INT'
    ,@optionalParam1 = @optionalParam1
    ,@optionalParam2 = @optionalParam2

etc. No importa si pasamos parámetros redundantes a sp_executesql, se ignoran. Vale la pena señalar que los ORM como Linq2SQL y EF utilizan un sql dinámico parametrizado de manera similar.

score 4 · Answer 2

Comience con lo que crea que es más fácil de implementar (supongo que la opción 2). Luego, mida el rendimiento de los datos del mundo real. Solo comience a optimizar cuando sea necesario, no de antemano.

Por cierto, según la complejidad de los filtros de búsqueda, es posible que su tarea no se resuelva fácilmente sin un SQL dinámico. Entonces, incluso cuando usa un procedimiento almacenado, lo más probable es que no aumente el rendimiento, como ya sospecha. Por otro lado, si ayuda, hay varios tipos de sugerencias (consulte enlace ) puede agregar a una consulta SQL, dinámica o no, para ayudar al servidor SQL a optimizar su plan de ejecución.