SQL Server: Query Parametriche e Variabili

[RB_82]

Buongiorno a tutti
Mi sono re-imbattuto in un problema, che avevo già visto tempo addietro in un'assistenza, tuttavia allora decidetti di soprassedere in quanto il problema non era impellente, ora lo è diventato. Vado con ordine e spiego tutto.
Ho vari progetti con database SQL Server 2008 R2 (in test SP0, in prod SP1), compilati in C# (sia 2.0 che 4.0) e web server IIS 7.5 (ma tutte queste cose sono superflue per il problema che descriverò).
In questo progetto ho vari pannelli con query pesanti (tutte dirette su DB, non DO), con inner o outer join di molte tabelle (da 4 in su, in vari casi con subquery).
In questi pannelli spesso inserisco sopra dei sovra pannelli di filtro, sottesi da imdb monoriga.
Quando INDE traduce la query in cui vi sono parametri da imdb in SQL (da voi consigliati) la scrive inserendo nei campi della where dei parametri.
La query esatta sono andato ad estrarla utilizzando l'sql server profiler ed il tempo di esecuzione della stessa sul programma compilato e su SQL Server Management Studio è compatibile (da 80 a 120 secondi a seconda della query e dei parametri impostati).

Ho sostituito in alcune query di pannello i parametri imdb con variabili di sessione o di videata e mettendo il trio di comandi
Pannello.updateQueries()
Pannello.enterQBEMode()
Pannello.findData()

o il solo
Pannello.updateQueries()
in altri punti a seconda dell'esigenza e del punto.

Ebbene effettuando queste sostituzioni le performance migliorano in maniera ABISSALE, con miglioramenti dal 30-50% a fino anche a 100-2000% (ovvero che query che impiegavano 100-120 secondi stanno ora sotto i 20 secondi, ed in alcuni casi addirittura sotto i 5 !!!!!!!)

Ragionando sul caso ho pensato ad un problema del driver odbc, ma provando col native client v11 (che vi ricordo è stato tolto nella nuova versione di sql 2016) i risultati sono gli stessi.

Il problema ovviamente non è di InDe ma eventualmente del motore di SQL Server in quanto eseguendo entrambe le query su SQL Server Management Studio i risultati sono analoghi. Non so se nelle versioni successive abbiano risolto il problema, tuttavia provano a cercare su vari siti (msdn, stackoverflow ecc) non ho trovato niente.

Vi chiedo: conoscete il problema e avete qualche idea se è possibile aggirarlo o risolverlo?

Proposta: non si potrebbe aggiungere un flag sulle query (almeno quelle di pannello) in modo che InDe in fase di compilazione/esecuzione della query risolva la stessa in modo che i parametri vengano passati direttamente come valori e non come parametri slq senza che io stia a duplicare centinaia di variabili per correggere il tutto?

Vi scrivo qui sul forum, speranzoso di una vostra risposta esaustiva, e non inserendo un progettino di esempio dato che di fatto non è un problema di InDe, ma che InDe potrebbe risolvere o aggirare.
Sono a disposizione per ogni chiarimento/delucidazione, anche ovviamente eventualmente sentendoci in assistenza.

Grazie

[RB_82]

Ho cercato meglio ed ho trovato qualcosa:
http://stackoverflow.com/questions/4459 ... h-variable

http://stackoverflow.com/questions/5102 ... n-paramete

http://stackoverflow.com/questions/1093 ... parameters

Forse è necessario inserire quel parametro OPTION (RECOMPILE) alla fine di una query con parametri... ma dovrebbe in tal senso farlo InDe?

[d.pierangeli]

Segnalazione molto interessante, io personalmente non conoscevo questo problema di SQLServer.

Ho creato l'attività NPQ03217 per tenere conto di questa conversazione. Quando verrà valutata, potrai seguirne l'andamento con Instant Developer Roadmap.

[RB_82]

Grazie Diego ;)
Data l'importanza di questa cosa, spero che un'eventuale correzione possa essere inclusa direttamente in 16.0.
Sono anche a disposizione se fosse necessario, dato che la cosa ci interessa particolarmente.

[d.pierangeli]

Però tieni conto che la soluzione non può essere generalizzata perchè potrebbe portare a cali di performance in altri casi; secondo me si può aggiungere un flag sulla query che se attivato imposta l'opzione corretta, poi sta al programmatore attivarlo quando sospetta un parameter sniffing.

[RB_82]

Si Diego, il flag è quello che anche io avevo scritto nella mia proposta nel post iniziale. Certo che poi devo essere io ad abilitare manualmente l'opzione.
La cosa che più mi preoccupa invece è se sia sufficiente o meno aggiungere l'opzione "OPTION (RECOMPILE)" al termine della query parametrica per farla diventare veloce oppure se sia necessario che la query venga scritta in maniera NON parametrica direttamente da InDe, come se invece delle celle imdb ci fossero le variabili.

Capisco sia una scocciatura da parte vostra dover rieditare una parte di codice che, dal punto di vista della stabilità, è consolidata da anni ed anni di buon utilizzo per un errore che nemmeno è vostro, in ogni caso sappiate che sarà altamente gradito ogni sforzo in tal senso.