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?

  • Which DBMS are you talking about? Oracle? SQL Server? PostgreSQL?
    – user1822
    Oct 27, 2012 at 8:01
  • @a_horse_with_no_name the question i raised doesn't concerned to a particular database ,if your explanation requires a dbms then sql server will be good
    – Biju jose
    Oct 27, 2012 at 10:21
  • A stored procedure - at least in SQL Server (assuming that's what you're talking about - SQL is just the language, not a product...) - doesn't increase performance. A well crafted parametrized T-SQL query is just as fast, just as pre-compiled after the first execution, stays in plan cache just as long as a stored procedure execution plan - that's not a benefit of a stored procedure at all.
    – marc_s
    Oct 27, 2012 at 14:32

2 Answers 2


In SQL Server, the query optimizer goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

  1. syntactically check the query
  2. if it's okay - it checks the plan cache to see if it already has an execution plan for that query
  3. if there is an execution plan - that plan is (re-)used and the query executed
  4. if there is no plan yet, an execution plan is determined
  5. that plan is stored into the plan cache for later reuse
  6. the query is executed

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.

  • ,so you are saying sproc doesn't decrease network congestion
    – Biju jose
    Oct 27, 2012 at 14:40
  • @Bijujose: it might - but that would be insignificant at best. That's certainly not one of the big benefit of stored procedures ...
    – marc_s
    Oct 27, 2012 at 14:47
  • 1
    @Bijujose: as I mentioned in my response - mostly that you create another layer of security. Your database users will only be allowed to execute the stored procedures, but they don't need direct table-level access; which means, even if they manage to connect to your database using Excel or Access, they won't be able to hack around in your tables.
    – marc_s
    Oct 27, 2012 at 14:52
  • 1
    @Bijujose: access to data through stored procedures, instead of having direct access to the tables - yes.
    – marc_s
    Oct 27, 2012 at 15:03
  • 2
    @Bijujose Consider stored procedures as the interface to your database. Marc makes a relevant point that they provide an additional security layer but above and beyond that, they abstract the underlying mechanics of the database from consumers. Oct 28, 2012 at 1:16

If there is a single SQL statement of moderate size (so it fits in a single network packet) then executing that statement from the application or packaging it as an SP and executing the SP will be identical network-wise.

However, a pattern I often see is that an application function requires several SQL statements to produce a result. Let's say we are writing an order to the DB. First we validate the customer (1) then the product code (2) then the inventory level (3) then write the order header (4) returning the ID which is used as a foreign key in the order line (5). If each call is made separately from the appllication this is five round trips to the DB server. If the values are passed as parameters to an SP it is a single request with a single response - one round trip.

Because of this I would argue that packaging the SQL-intensive part of business functions as storded procedures does indeed reduce the network load in typical scenarios.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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