When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it doesn't".
I made a sudden pause, I was afraid to ask him why. After the interview session, I searched a lot on the Internet but I found the point I said was there but no explanation for it. So I asked myself and made some assertions:
Since a stored procedure is precompiled, i.e. an execution plan has already been created for it, it increases speed of execution.
But for cases where we pass some variables to the SP, those need to go to the server for execution, and should retrieve different results for each variable.
So my point is that the interviewer is correct: network congestion is not reduced, each time the variables have to be passed to the database and the results should be returned, so network is busy with these. Right?
Is my assertion correct?
Best Answer
In SQL Server, the query optimizer goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):
The point is: ad-hoc SQL and stored procedures are treatly no differently.
If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.
Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.