Interesting article on the perceived value of stored procedures

Question

Post reply

Interesting article on the perceived value of stored procedures

stevehurcombe

Newbie

Points: 8
More actions
November 3, 2004 at 6:31 am

#361386

Hi all,
<dont shoot the messenger>
This makes interesting reading:
http://www.codinghorror.com/blog/archives/000121.html
The original article:
http://www.codinghorror.com/blog/archives/000117.html
Extract:
"For modern databases and real world usage scenarios, I believe a Stored Procedure architecture has serious downsides and little practical benefit. Stored Procedures should be considered database assembly language: for use in only the most performance critical situations. There are plenty of ways to design a solid, performant data access layer without resorting to Stored Procedures; you'll realize a lot of benefits if you stick with parameterized SQL and a single coherent development environment. "
Best regards
Steve
</dont shoot the messenger>

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

I recently discussed my feelings about this in my blog... You can read them here:

http://sqljunkies.com/WebLog/amachanic/archive/2004/10/26/4823.aspx

... the jist of my argument is that stored procedures may not necessarily be better for performance, but that's hardly the reason I and many others use them. We use them to provide data-layer encapsulation, and to protect compiled code from schema changes.

--
Adam Machanic
whoisactive

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 2

Not pointed out is the fact that a stored procedure is optimized once, then reused from the cache. A SQL query may be optimized to be as fast as a stored procedure, but it will be re-optimized every time it is run. There is no caching of execution plans for ad hoc queries.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

Russell, you can use parameterized queries for that purpose. Performance gains of stored procedures over parameterized queries are really not an issue at this point (IMO) ... unfortunately, people tend to focus on performance and forget about all of the other important aspects of software engineering: modularity, reuse, ecapsulation, etc. All of which stored procedures help us achieve.

--
Adam Machanic
whoisactive