Advantages of Stored Procedures?
time to read 3 min | 495 words

Eber Irigoyen had this to comment on my post about SPs:

...the point is how much scalability, flexibility, security, etctirity the SP give you, remember the rule of thumb, always use the right tool for the job, use what will give you the most benefits in the long run

I completely disagree with this, if you didn't catch on so far. Here is my refutal:

  • Scalability:
    • There is no performance advantages to using SP over Parameterized SQL.
    • Scaling the database is much harder than scaling an application.
    • Note: I implemented a caching web service once that simply cache data for certain period of time, saving quite a bit from the poor database. Deploy a bunch of those, and you're seriously reduced the amount of work the database has to do.
  • Flexibility:
    • Stored Procedures are... Well, procedures. This means that you've all the advantages and disadvantages of procedural programming. On general, I wouldn't call it very flexible. Changing the procedure's internal can be done without breaking clients is possible within a certain set of changes, but major stuff is hard.
    • A search procedure should be flexible, you should be able to search by any combination of parameters, how easy it is to write this procedure?
  • Security:
    • Not applicable in situations where you don't give users direct access to the database, in my opinion. There are things to be said about defense in depth, but I don't think that they should be curried to the point where you are forced to give up significant advantages while keeping the security of the system (using views / triggers instead of procedures, for instance, can result in a situation just as safe, and far easier for the programmers to work with effectively).
Tweet Share Share 0 comments
Tags: