Il miglior metodo per implementare una ricerca filtrata

Question

Il miglior metodo per implementare una ricerca filtrata

#1 da (9 voti)
#2 da (4 voti)

13

Vorrei chiederti, la tua opinione quando si tratta di implementare un modulo di ricerca filtrato. Immaginiamo il seguente caso:

1 grande tabella con molte colonne
Potrebbe essere importante dire che questo SQL Server

Devi implementare un modulo per cercare i dati in questa tabella e in questo modulo avrai diverse caselle di controllo che ti permetteranno di personalizzare questa ricerca.

Ora la mia domanda qui è quale dei seguenti deve essere il modo migliore per implementare la ricerca?

Crea una stored procedure con una query all'interno. Questa stored procedure controllerà se i parametri sono forniti dall'applicazione e nel caso in cui non viene fornito un carattere jolly verrà inserito nella query.
Crea una query dinamica, che viene costruita in base a ciò che viene fornito dall'applicazione.

Lo sto chiedendo perché so che SQL Server crea un piano di esecuzione quando viene creata la procedura memorizzata, al fine di ottimizzare le sue prestazioni, tuttavia creando una query dinamica all'interno della stored procedure si sacrifica l'ottimizzazione ottenuta dal piano di esecuzione?

Per favore dimmi quale sarebbe l'approccio migliore nella tua opinione.

sql database methodology sql-server development-approach

posta j0N45 08.08.2012 - 17:47

fonte

2 risposte

Leggi altre domande sui tag sql database methodology sql-server development-approach

La programmazione coppie rimuove la necessità di revisioni del codice in un progetto di programmazione Extreme (XP)? Statico è male, ma per quanto riguarda il modello di fabbrica?

score 9 · Answer 1

Potresti voler dare una risposta a questa domanda simile qui: link

Abbiamo scoperto che uno SPROC che contiene un numero di parametri facoltativi e implementa il filtro in questo modo:

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)

memorizzerà nella cache il primo piano di esecuzione con cui viene eseguito (ad esempio @optionalParam1 = 'Hello World', @optionalParam2 = NULL ), ma poi eseguirà miseramente se passiamo un diverso insieme di parametri facoltativi (ad esempio @optionalParam1 = NULL, @optionalParam2 = 42 ). (E ovviamente vogliamo le prestazioni del piano memorizzato nella cache, quindi WITH RECOMPILE è out)

L'eccezione qui è che se c'è un ALTO almeno un filtro MANDATORY sulla query che è ALTAMENTE selettivo e correttamente indicizzato, oltre ai parametri facoltativi, allora la PROC sopra descritta andrà bene.

Tuttavia, se TUTTI i filtri sono opzionali, la verità piuttosto terribile è che sql dinamico parametrizzato effettivamente funziona meglio (a meno che non si scriva N! PROCS statici differenti per ogni permutazione dei parametri opzionali).

SQL dinamico come il seguente creerà e memorizzerà nella cache un piano diverso per ogni permutazione dei parametri Query, ma almeno ogni piano sarà 'adattato' alla query specifica (non importa se si tratta di una PROC o di Adhoc SQL: purché siano query parametrizzate, verranno memorizzate nella cache)

Quindi, la mia preferenza per:

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

ecc. Non importa se passiamo i parametri ridondanti in sp_executesql - vengono ignorati. Vale la pena notare che gli ORM come Linq2SQL e EF utilizzano parametri dinamici con parametri parametrizzati in modo simile.

score 4 · Answer 2

Inizia con ciò che pensi sia più facile da implementare (suppongo che l'opzione 2). Quindi misura le prestazioni per i dati del mondo reale. Inizia a ottimizzare solo quando necessario, non prima.

A proposito, a seconda della complessità dei filtri di ricerca, l'attività potrebbe non essere facilmente risolta senza SQL dinamico. Quindi, anche quando usi una stored procedure, probabilmente non aumenterai le prestazioni, come già sospetti. D'altra parte, se aiuta, ci sono diversi tipi di suggerimenti (vedi link ) è possibile aggiungere a una query SQL, dinamica o meno, per aiutare il server SQL a ottimizzare il proprio piano di esecuzione.