Quando dovrei usare le stored procedure?

Hai già un paio di scenari perfettamente buoni.

Ci sono molti altri motivi. EF è davvero bravo in CRUD e in report abbastanza semplici. A volte, tuttavia, EF non è lo strumento perfetto. Alcuni altri motivi (scenari) da considerare l'utilizzo di stored procedure in combinazione con Entity Framework includono:

• Hai unità di lavoro complesse, che potrebbero coinvolgere molte tabelle, che non possono essere facilmente racchiuse in una transazione utilizzando le funzionalità di EF.
• Il tuo database non gioca bene con EF perché non riesce a sfruttare l'integrità referenziale dichiarativa (vincoli di chiave esterna). Di solito questo è uno scenario sbagliato in cui ci si trova, ma a volte esistono scenari appropriati, come i database utilizzati per i processi ETL.
• Devi lavorare con i dati che attraversano i confini del server con i server collegati.
• Esistono scenari di recupero di dati molto complessi in cui è necessario l'SQL "bare metal" per garantire prestazioni adeguate. Ad esempio, hai join complessi che necessitano di suggerimenti per le query per funzionare bene nella tua situazione specifica.
• L'applicazione non dispone di autorizzazioni CRUD complete su una tabella ma è possibile eseguire l'applicazione in un contesto di sicurezza ritenuto attendibile dal server (ad esempio identità dell'applicazione, piuttosto che identità dell'utente). Questo può verificarsi in situazioni in cui i DBA limitano l'accesso alla tabella ai proc memorizzati solo perché dà loro un controllo più granulare su chi può fare cosa.

Sono sicuro che ce ne sono molti altri oltre a questi. Il modo per determinare il percorso migliore in qualsiasi circostanza particolare è utilizzare un po 'di buon senso e concentrarsi sull'obiettivo primario, che dovrebbe essere quello di scrivere codice di alta qualità e facilmente gestibile. Seleziona lo / gli strumento / i che ti dà questo risultato in ogni caso.

Forse ha senso, per rivolgere la domanda e trovare casi in cui le procedure memorizzate solo potrebbero fare, cosa vuoi ottenere. Forse ci sono davvero casi d'uso, in cui le stored procedure risaltano.

If it makes a difference, I am using MSSQL and Entity Framework.

La mia conoscenza di EF è limitata, ma per quanto posso vedere, EF è ( just ) un ORM come qualsiasi altro; e fortunatamente è in grado di utilizzare SQL raw .

Se prendo i tuoi due punti principali:

A complicated report that was taking minutes to run (this was a page in a web app). I found I could write SQL that was much more efficient (only taking seconds to run) than what LINQ was providing.

LINQ / EF non era all'altezza, quando si eseguiva un rapporto. E come hai notato, era SQL molto più veloce, rispetto all'utilizzo di un ORM. Ma parla a favore delle stored procedure o solo contro l'uso di ORM per tutto?

Il tuo problema potrebbe ovviamente essere risolto con SQL . Se questa query è stata archiviata e la versione controllata nel codice base fa - secondo il tuo esempio - almeno nessuna differenza .

A web application needed to read and write to a few tables on a separate database which contained a lot of other, sensitive information that was irrelevant to the application. Rather than giving it access to everything, I used a stored procedure that only does what is needed, and only returns limited information. The web application could then be given access to this stored procedure only, without access to any tables etc.

La stessa cosa qui: una semplice stringa di connessione e e UPDATE e il tuo problema è stato risolto. Questo problema può essere risolto anche con un ORM : semplicemente usa un webservice di fronte all'altro DB e lo stesso compartementalization / isolamento è raggiunto.

Quindi niente da vedere qui.

Osservando alcuni punti che altri hanno fatto:

You have complex units of work, perhaps involving many tables, that cannot be wrapped in a transaction easily using the features of EF.

Ma SQL può farlo. Nessuna magia coinvolta qui.

Your database does not play well with EF

Ancora: usa EF quando appropriato.

You need to work with data that crosses server boundaries with linked servers

Non vedo, come aiutano le stored procedure. Quindi non posso vedere un vantaggio delle stored procedure; ma forse qualcuno fa luce su questo.

You have very complex data retrieval scenarios where "bare metal" SQL is needed in order to ensure adequate performance

Ancora: "Limiti di EF ".

Your application does not have full CRUD permissions on a table but your application can be allowed to run under a security context that your server trusts

Va bene. Vado con un forse .

Finora è stato effettuato un solo punto mezzo a favore delle stored procedure.

Forse ci sono considerazioni sulle prestazioni, che parlano a favore delle stored procedure.

1) La memorizzazione di query ha il vantaggio di una chiamata semplice alla stored procedure che incapsula la complessità. Poiché la query planer conosce la query, è "più semplice" ottimizzare. Ma i salvataggi sono con l'attuale piallatore di query sofisticato _minimal.

Inoltre, anche se c'è un leggero costo usando le query ad hoc , se i tuoi dati sono ben strutturati e accuratamente indicizzati, il database è semplicemente il < em> bottleneck della tua applicazione. Quindi, anche se c'è un piccolo delta, è trascurabile considerando altri fattori.

2) Tuttavia è stato discusso per la memorizzazione di query complesse in un DB. Ci sono due cose da considerare:

a) le query complesse fanno un uso massiccio dell'infrastruttura DB, che aumenta i tempi di risposta per ogni altra query. Non è possibile eseguire molte query costose in parallelo . Questo non parla né di pro né di contra stored procedure, ma di query complesse .

b) Se la query richiede comunque del tempo, perché preoccuparsi di vincere a bassa velocità di una stored procedure.

tl; dr

Nulla parla direttamente contro stored procedure. Quindi usare le stored procedure è ok - se ti rende felice.

Ma d'altra parte: non potrei immaginare un use case appropriato che parli inequivocabilmente pro.

When should I use stored procedures?

La risposta corretta è: Ogni volta che ti piace . Ma ci sono altre opzioni.

Per il tuo caso specifico, dal momento che stai usando l'entità framework, si dovrebbe considerare l'uso di stored procedure quando il framework di entità non soddisfa un requisito o preoccupazione.

Il framework entità fa un lavoro decente e le prestazioni saranno vicine o uguali all'utilizzo di stored procedure. Hai scelto il framework di entità perché non volevi occuparti di SQL e lascerai che il framework generi l'SQL per te.

Ma potrebbe esserci un caso limite in cui la struttura dell'entità è insufficiente e non può soddisfare le tue esigenze. In questo caso si dovrebbe scrivere l'SQL manualmente usando una stored procedure o una query parametrizzata e quindi utilizzare il framework entità per chiamare direttamente la stored procedure / istruzione SQL.

Quindi, non vorrei spostare nulla su una stored procedure a meno che il framework che viene impiegato non possa soddisfare il requisito.

Penso che la cosa peggiore che può accadere è che uno sceglie di utilizzare il framework di entità e quindi scrive tutte le stored procedure. Ora uno ha un livello in più che non aggiunge valore.

Avere un bisogno tecnico non è la scelta più probabile. I programmatori preferiscono i loro strumenti e di solito sono più adatti a risolvere i loro problemi con loro. Mettere la logica in uno sproc sarà l'eccezione. Dovrebbe essere preso in considerazione il debito tecnico e gli sviluppatori futuri che devono impiegare del tempo a lavorare con un'eccezione. Potrebbe esserci un aumento delle prestazioni nel tuo RDBMS particolare che è solo più facile da implementare in uno sproc o forse anche un altro oggetto db come una vista indicizzata.

Ci saranno momenti in cui hai bisogno di dati da un database e non riesci a ottenerli dal codice dell'applicazione. In molte grandi aziende / situazioni aziendali, le esigenze aziendali possono superare la portata del reparto IT. Le aziende vengono comprate e vendute e le loro applicazioni vanno e vengono con loro. Le agenzie e le banche che regolano non si preoccupano se non puoi costruire la tua app altamente scalabile e ben codificata. Possono rendere la vita difficile per il business, quindi devi farlo.

Ho incontrato situazioni in cui applicazioni di terze parti o strumenti di scrittura di report non consentono di ottenere il codice. Nessun codice open source, nessuna API, nessuna interfaccia web e nessun servizio. L'unica cosa che hanno è il proprio strumento di scrittura di report speciali che funziona solo con gli oggetti del database: tabelle, viste, sprocs, funzioni definite dall'utente, ecc. Metti la logica ovunque tu possa ottenere.

Se hai un DBA che è molto bravo a scrivere stored procedure, puoi sfruttare quel talento in alcune situazioni. Potresti voler attivare un backup dalla tua app perché hai intenzione di apportare importanti modifiche ai dati, quindi perché non utilizzare ciò che usa il dba?

Alcune richieste ad hoc sono semplicemente più facili da scrivere un proc finché non è possibile ottenere tutte le funzionalità nella tua app. Lo odiamo. Respingiamo, ma lo spettacolo deve continuare.

Consiglio vivamente di non inserire alcuna logica aziendale in una stored procedure. Tuttavia, potrebbe esserci un caso (hai elencato due buoni esempi) in cui è preferibile posizionare la logica accesso ai dati .

In generale, se ti senti tentato di utilizzare SP, è un segno (un odore di codice) che suggerisce che il tuo modello di dati non è adatto alle sue esigenze.

Modifica: Quando gli sviluppatori mi chiedono informazioni sulla logica di accesso business / dati, dico che la logica aziendale riguarda il modo in cui i dati si comportano e interagiscono, mentre la logica di accesso ai dati riguarda il modo in cui i dati vengono archiviati e recuperati.

Ad esempio: nel tuo modello di dominio potresti avere le entità "Studente" e "Insegnante", entrambe derivate da "Persona". (Non dicendo che questo è preferito, solo un esempio). Questo può essere memorizzato in un database relazionale come una, due o tre tabelle. La scelta dipende da quante proprietà condividono e dai requisiti di prestazione in lettura / scrittura.

Nella logica aziendale non dovrebbe importare in che modo tali entità siano archiviate. (o anche se risiedono in un RDB). La logica di accesso ai dati è incentrata su come sono archiviati fisicamente.

Quindi, per quanto riguarda la domanda originale: se una stored procedure rende la memorizzazione e il recupero dei dati più efficienti (o più robusti), si ha un caso. Se la procedura memorizzata è solo per l'imposizione di una regola valida a prescindere dalla modalità di archiviazione dei dati, evitarla. Rende il tuo codice più strettamente accoppiato al database.

Penso che ci siano tre problemi qui.

1. La logica aziendale in SQL è cattiva. Anche se viene eseguito più rapidamente individualy, non scala.

2. Tradizionalmente gli SPROC devono sempre essere utilizzati per tutti gli accessi ai dati come livello di astrazione. Abilitazione di un DBA per introdurre le prestazioni o altre modifiche del datalayer senza influire sulle applicazioni che consumano.

3. EF non gioca bene con sprocs. Perderai molte delle "buone" caratteristiche e guadagni di produttività passando dalla generazione di query dinamica di Linq.

Quindi il mio consiglio generale sarebbe

• Utilizza SPROC per tutte le query SQL,

• Non inserire logica aziendale o alcun tipo di loop in essi,

• Non utilizzare EF.