Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
Which DBMS are you talking about? Oracle? SQL Server? PostgreSQL? – a_horse_with_no_name Oct 27 '12 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 '12 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 '12 at 14:32

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
,so you are saying sproc doesn't decrease network congestion – Biju jose Oct 27 '12 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 '12 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 '12 at 14:52
1  
@Bijujose: access to data through stored procedures, instead of having direct access to the tables - yes. – marc_s Oct 27 '12 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. – Mark Storey-Smith Oct 28 '12 at 1:16
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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