-2

What difference between stored procedure and query in application. Performance , network load, server load and speed Thank you very much

closed as too broad by mustaccio, Colin 't Hart, Erik Darling, Shanky, Vérace Jan 8 at 13:36

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Will depend very much onwhat the sp does. You can use them to reduce network traffic,but first, determine where your resourcesare bottle-necked, then try to pick the low-handing fruit! – Vérace Jan 8 at 13:18
2

You typically have less "chatty" communication between the client and SQL Server when you use stored procedures since you encapsulate several operations in the proc, something that would typically result in several interactions between the client and the server.

It is also IMO far easier to instrument the SQL code from a performance perspective when using stored procedures. Capture them in a trace (or some other method), read through the SQL with a sense of context (which you have less of when you only see SQL submitted from the app), try different things in the SQL, add hints like RECOMPILE if neccesary without having to do changes in the client app. Etc.

Not the answer you're looking for? Browse other questions tagged or ask your own question.