È buona norma scrivere query distinte in una singola stored procedure basata su parametri di input

Question

È buona norma scrivere query distinte in una singola stored procedure basata su parametri di input

#1 da (1 voti)

1

Sto esaminando le stored procedure scritte nel nostro progetto e vedo che più query distinte vengono scritte all'interno di una singola stored procedure e vengono richiamate passando valori diversi al parametro di input.

Questo non sarà un problema di prestazioni? Se più client chiamano la stessa stored procedure passando valori diversi al parametro di input, il piano di esecuzione della stored procedure dovrà essere ricompilato ogni volta che viene chiamato e si degradano le prestazioni?

L'unica ragione per cui posso comprendere è di salvare dallo scrivere nuovi metodi di servizio per chiamare diverse stored procedure e riutilizzare i metodi di servizio esistenti semplicemente passando diversi parametri.

Qualcuno vede altri vantaggi nel scrivere la stored procedure in questo modo?

Quali potrebbero essere gli altri svantaggi di scrivere in questo modo oltre alle prestazioni?

sql database sql-server stored-procedures

posta m_d_p29 08.07.2015 - 13:20

fonte

1 risposta

Leggi altre domande sui tag sql database sql-server stored-procedures

Modellazione di utenti e ruoli Node.js di Polyglot con Typesafe Akka per modello di attore

score 1 · Answer 1

Sì, ho visto che i problemi di prestazioni significativi derivano da questo tipo di schema. Se si hanno rami nel vostro sproc che si traducono in domande molto diverse, in determinate circostanze questo può causare enormi problemi di prestazioni.

SQL server costruirà un piano di esecuzione per uno sproc quando necessario in base ai valori dei parametri passati in quel sproc nel momento in cui viene chiamato. Quindi quel piano di esecuzione verrà memorizzato nella cache e riutilizzato nelle chiamate successive. A seconda di come viene scritto lo sproc e di quale ramo verrà eseguito con quei parametri, è possibile che il piano di esecuzione che viene memorizzato nella cache sia uno che funziona molto male quando lo sproc viene chiamato con parametri diversi.

I casi peggiori che ho visto di questo sono stati quando c'è un parametro bit che è stato testato in un'istruzione if per restituire risultati diversi, ad esempio restituendo un record e restituendo una pagina di record.

Questo problema può comportarsi in modo molto intermittente - se un particolare ramo dello sproc genera un piano di esecuzione che funziona abbastanza bene per gli altri rami, non si noterà alcun problema. Cioè, fino a quando il piano di esecuzione non viene ricostruito usando un altro ramo.

I problemi di prestazioni con questo sono conosciuti e bene documentata. Esistono varie soluzioni alternative, come l'utilizzo di OPTIMIZE FOR UNKNOWN e OPTIMIZE RECOMPILE , ma una migliore opzione IMHO è di scrivere sproc per evitare questi problemi limitando gli sproc per eseguire attività specifiche ed evitare clausole di ramificazione.