ak said,

Also, using stored procedures means instant vendor lock-in: once you use them, it's really hard to port your application to another database.

Jeffrey Howell said,

Nicely done. Makes perfect sense.

Jiff
www.privacy-center.be.tc

Alex said,

Dude, you got it all wrong. and I'm sad for whatever company you work for.

yeah, they're not optimized. okay. but they ARE cached in memory.
sure, slightly smaller direct queries can be faster, but what are you doing that saves a cpu tick?
Developers shouldn't write sQL. they suck at it. hardcoded inline SQL is impossible to fix and tune without recompiling and redeploying a DLL or MSI. A proc can be done anytime.
You obviously are not a team player and don't know that DBA's are not the problem.
Version control for a SP? You mean things like VSS, subversion, Borland?

I hope you open your eyes a bit and see that you missed a lot.

a real developer said,

LOL

I'm with you Alex, this guy doesn't have a clue.

Miron said,

I'm with Alex too, and I feel sorry for the "developers" who think like you!

better than the real developer and miron combined said,

R.E.P.E.A.T after me:
there is no silver bullet.

Both techniques have their merits, althought SP guys are losing a bit by bit if you look at the fashion scene. Maybe SPs will rock again in 5 years, maybe not.

brianstewey said,

I agree with the author on a number of points. I also consider myself a "a real developer" too.

Wayne said,

Dude, even if you somehow got through the interview process at my organization, you wouldn't last.

jeo said,

Two of your problems are organizational ones. Stored procedures are easy to change if you don't have a bureaucracy preventing it. And you can put them in a subversion directory just like anything else.

atc said,

Your abuse of apostrophes offends me.

Furthermore, stored procedures are a great way to standardise and keep consistency across large databases and multiple development streams.

For Oracle, using the shared pool for bind variables means that things are very fast in comparison to building variables on the fly. My point is that there's ways to optimise SPs, and they'll be cached (as previously mentioned by 'Alex') as opposed to hard-coded SQL strings that will be dealt with on-the-fly. SPs can be optimised to the hilt, and it also creates a layer of abstraction between the database and the application/domain code. Splitting things up like this allows for experts in each field to concentrate on what they know best, instead of a Jack-Of-All-Trades affair as you might have when a .Net developer is coding both aspects of a project.

DaveTheKnave said,

Um, there's one key problem, which would mean an appliction that turns it's nose at stored procedures wouldn't scale.

It's a lot more concise to send "sp_DoSomeAdvanced task @param1" - across a network than sending 5-8k of text (a large query), on each request.
Also, if you have a SQL statement longer thank 8k, I think that's still the upper-limit on the size of a dynamic SQL query.

we have MANY reports which simply cant be abstracted to anything smaller than 10k, for example.

I disagree with you completely.

Mant said,

Parameterised queries are also cached in memory which is part of the reason why they are just as fast.
This isn't about hardcoded in line SQL which everyone knows sucks, but generated SQL such as from LINQ to SQL, LINQ to Entities or NHibernate. Developers don't write it, they just set up the mapping rules between the objects and the database.

That said the big advantage of stored procedures is not that they optimise queries but that they let you optimise queries. For complex queries generated code is not always efficient enough. Still for basic selects and CRUD operations you can do away with the sprocs these days.

Danny said,

Your entire argument falls apart when you consider that DBAs work with stored procedures to optimize performance.

If you have every "Joe Developer" write LINQ queries to access the Database, over time you will have a difficult to maintain and optimize system. That's why DBAs always insist that all data access go through stored procedures.

LINQ-to-SQL is nothing more than an easy to code inline query.

If you really want to use LINQ, which is really what this post is about, you can still use LINQ over stored procedures.

monkey_fuel said,

lol whut.

OP is a lame troll.

Matt said,

Scott,

Don't let these guys make you second guess your opinions. Your article makes some very good points and there are arguments for and against both sides. The important thing is that you're writing about it while these commenter's are just taking 2 seconds to skim your article and then bash you.

I've worked in large organizations that swore by stored procedures. The result was a collection of interconnected SP's so large that it was unmaintainable. Then when scalability became an issue and database replication became a necessity, SP's became even more of a nightmare. I've written large applications that rely on a fair amount of SP's and now that I am maintaining them and re purposing code from these apps, I regret using stored procedures.

They certainly have their place, but these days developers have more options to weigh.

MW said,

I think you're misunderstanding the author.

He's not talking about embedding slow "hard-coded sql queries". He's referring to building parametrized queries. These might be built with a great ORM tool like NHibernate or MS's Entity Framework, so there is no maintenance, you just set up your mappings and let your domain load as needed. This allows for much faster development and easier to maintain application code.

Also, transactional development does not include report writing. Go ahead, use a stored proc when you write a report, no one is arguing that point. Using a sql generation tool even requires to write a stored proc sometimes when it makes sense, but most of the time a generated parametrized query runs at the same speed as a sp and it's much easier to maintain the code.

As far as the 5-8k argument. I can kind of see the point of this one, but only for extremely high performance applications. The kind that you probably shouldn't be using .NET for. Most of the time your database server will be on the same network as your webserver connected at at least a 1Gbs line if not faster. Sending a huge 8k query could effect performance in a noticeable way, but it's very dependent on the situation you're using it for.

Danny Trevor said,

Your entire argument falls apart when you consider that DBAs work with stored procedures to optimize performance.

If you have every "Joe Developer" write LINQ queries to access the Database, over time you will have a difficult to maintain and optimize system. That's why DBAs always insist that all data access go through stored procedures.

LINQ-to-SQL is nothing more than an easy to code inline query.

If you really want to use LINQ, which is really what this post is about, you can still use LINQ over stored procedures.

JKSQL said,

I have seen it so many times where a developer misinterprets a table definition and it is used incorrectly. SP's force developers to adhere to rules and optimization techniques that DBA's know about. SP's for the most part are faster since Developers will write the same query 6 different ways. Try to index that with statistics. SP's are designed to optimize parts of SQL which inline fails to do. The shop where I work uses both...Let me just tell you as bus logic changes in-line sql fails. We removed a field two weeks ago and the developers are still looking for the code errors. SP's would have been done in two days. Also versioning: TFS, and VS Database developer. So you need to come to the dark side and realize that developers do not hold all the keys.

Tom Pester said,

I'm not that old but when I read about the transition from assembler to a more procedural language or from hierarchic databases to relational databases it reminds me of the dicussion we currently have.

The hierarchic database folks said that the relational databases could not match their performance and they were right at a certain point and I still think that it'a valid argument although marginal.
In the long run the incredible power of relational dotabases (ad hoq querying if I'm not mistaken is the biggest diff) convinced a lot of people to change the way they did things and the implementaions/tools/etc soon followed.

Same goes for the Assembly/produral/OO analogy although here it's a comparision thats maybe better suited. I heard that certain parts of programs written in .NET/Java/etc still go to the metal and call routines writte in assembly just because when every millisecond counts (user interfaces) they are _the_ tool to use.

A lot of the older programmers still can write good SQL but the newer generations are not starting out with this skill. I acutaly would like to start out learning our profession with a blank mind gets fed good OO design. At some point however they will see that their solutions don't scale and than its unavoidable that they leave their better abstraction and go a layer deeper.

I also think that some people are doing "job protection" but their is no need for this. Good DBA's will still be necessary but it will become a job that's more focused on infrastrucure than on programming : backups, putting the right index in place to optimze that sub optimal Linq query a dev worte, etc.

In the end added power and flexibility will win from performance. The performance argument is valid only temporarily IMO.
But if assambly is in order then go write that blazing fast sproc!

Alex J. said,

Nowadays, there are more options for the developer to consider. That means that in some cases, a dev should now choose to use something other than stored procedure where he might have used one in the past. That said, I think most of your criticisms are off base, for reasons which others have mentioned. One benefit that stored procedures will almost always have, is that they save round trips between the rdbms and the next tier up in your application. This is especially important in performance sensitive situations where you can't use a single set operation to make the change.

Apostrophe Police said,

Excellent article! Just a heads-up; it's SPs, not SP's.

Casey said,

I just have few things I would like to point out without taking any sides. These are more just things to consider.

1) Not all projects have a DBA to write SPs for them. So, you either have developers writing the SPs or writing queries in code. My preference would be to keep the developers out of the database as much as possible to reduce the risk of a screw-up.

2) If you don't use SPs, it's easier to migrate from one DB to another or ensure that your app will work with multiple back-ends. Granted, in my experience this isn't much of a problems as people rarely do it, but if I was creating a web-based reporting tool that was supposed to work on multiple databases, I would probably stay away from SPs as much as possible unless I had a good way to maintain the SPs across the different databases.

3) The good piece of advice I ever heard on SPs basically stated that you should only use SPs when they save you bandwidth. I believe this was in regards to doing business logic in an SP. Basically you don't want to do too much in an SP so that it causes your DB to bog down. Sometimes it's just better to do more work in a higher-level than at the database level. You probably won't know which way is truly better until you benchmark.

4) My favorite reason for SPs is not performance, but isolating the application from the database schema. With an SP, you can hide the details about how the information is stored and if the design of the schema changes, the app doesn't always have to know about it.

Now, as for my personal tastes, I don't know which I prefer. I've done it both ways and both seem logical to me. Not using SPs is much easier to do and faster since it's one less environment I have to develop in. However, I see the benefits of being able to keep database logic out of the code.

but why said,

hardcoded inline SQL is impossible to fix and tune without recompiling and redeploying a DLL or MSI. A proc can be done anytime.

Nothing says sql has to be hardcoded into a dll. SQL could be read from text files whenever the application loads...

When all you have is a hammer...

Brianary said,

MAJOR OMISSION: You can revoke all permissions from a table, and only allow access via sprocs to more precisely control permissions, even controlling access to specific records. You simply cannot do this with LINQ or "RAD development".

Also: Moving the database logic outside the database means you have to rely on the developers to re-use the same database logic layer, or constantly re-implement it. LINQ tends to foster a re-implementation habit.

And: DBAs have to implement indexes on tables based on the queries used for the tables. Take them out of the query process entirely at your own peril.

Plus: Apostrophes are for possession and contractions, not pluralization.

I have been a DBA and a programmer, and I like (and use) LINQ, but it's important to realize you are trading some benefits for the power LINQ provides.

0x00bc said,

Before LINQ I put stored procedures in source control. You just make a runnable .sql file with a DROP and CREATE statement.

Jeremy said,

Many databases allow you to set permissions for each procedure or view. As a developer and NOT a DBA, I prefer using indexed views and SPs. I make a simple call out to the database to get the result of some view. The database is clean, optimized and normal, but my interface with the database changes. If I have an application which uses data in a number of different ways, I can treat each SP or view as a different table. To me, they aren't different than getting the result of an in-line SQL call. But, the DBA can add hints, optimize the tables, and do whatever else it is that a DBA does without my having to go back into the code base and change every reference to a table to be somehow different. For those who think that SPs become unmanageable, you're probably not doing it right. In my experience, there are only a few SPs or views which represent data objects. This way, I'm still using the database and DBA as a source of data storage and retrieval. I am not offloading business logic into the database. A little processing is fine with me considering RDBMSs are often optimized for certain manipulations and are typically I/O bound rather than CPU bound whereas application servers are often CPU bound. It just makes sense as a web developer to share the burden rather than paying for a DB server that sits there with a tiny load average.

So, to your points:
1) DBAs can optimize SPs a lot better than they can optimize in-line SQL statements in my code. Further, they are free to normalize or denormalize tables without my having to re-touch every instance in the code base which refers to those tables. So, while there is no "magic" optimization, using views/SPs makes it a heck of a lot easier for a DBA to do his job.

2) Injection, yeah. Arguments both ways. But, if DBAs concentrate on security, access can be granted on a per view/SP basis and so we can severely limit the problems that a bad injection can cause. In-line SQL requires the developer to think about DB security -- and, I'd bet, most developers make one persistent connection to the DB using the most powerful account to which they have access. This limits the DB's ability to stop an action based upon permissions.

3) A lot of that is bureaucracy and finger-pointing. Where I work, developers make in-line SQL during the development process and when they've settled on their needs, they pass that along to the DBA. The DBA then knows what the code gives him and what the coder expects to get back. All other decisions about schema, implementation, permissions, hints, optimization, et cetera are his to make.

4) SPs which are doing a lot of business logic do cause problems. That's why I try to look at it as all in the realm of data storage and retrieval. I'm fairly sure your code doesn't query each table individually and do joins in the code. No, you rely upon SQL to handle that "logic." The same is true with SPs and views. To you as a developer, all you're doing is asking a question to the database through a well-defined API.

5) Again, this idea of business logic. Data should be separate from logic -- which is separate from design. Yes. But what qualifies as logic? As a web developer I'm not querying a SP and expecting HTML. I'm expecting relatively raw data. I say relatively because, yes, I do like my database to manage some things like JOINs -- even conditionals sometimes make sense to offload onto the database. You've never done something like "SELECT CASE WHEN x IS NULL THEN y ELSE x END AS x"? Is that logic?

6) There actually _is_ version control.

Brian said,

MAJOR OMISSION: You can revoke all permissions from a table, and only allow access via sprocs to more precisely control permissions, even controlling access to specific records. You simply cannot do this with LINQ or "RAD development".

This needs to be said again. Security of the data is the largest reason to use sprocs. When you use in-line statements, you lose your ability to apply fine grained security on your data. Now, some projects don't require this, but some do. Understand that sprocs won't always be needed, but they are useful still.

I love things like Active Record, Linq, etc. As a developer, they make my life easier. But there are some projects that just can't leave users with full access to the tables like is needed with in-line queries.

Chad Myers said,

@Everyone:

WHERE clauses are business logic. When you're deciding what or what not to return from a query based on "CRITERIA", that's business logic and is under the strict domain of the application. Aside from all the other glaring anti-productivity problems with SPs, this is the deal-killer for me. Every project I've worked on with SPs, business logic inescapably leaks into the procs and creates a maintenance nightmare. As far as I'm concerned, it's unavoidable when using SPs, so therefore SPs are out. Whatever marginal benefits they may offer here are there, if any (and that's debatable) are totally negated by their ability to grind productivity and maintenance change confidence to a halt, costing way more to the life of the project than their limited-if-no benefits.

Rendy Wendi said,

I think the advantage that stored procedure has is performance, and also if your client has DBAs Stored procedure will be ALOT easier to be maintained.. I Wrote small article about Stored procedure vs Inline SQL statement: http://tinyurl.com/5ew69b.

Chad Myers said,

@Everyone:

First rule of enterprise integration: Never, ever, under ANY circumstances, ever, repeat: NEVER EVER integrate through the database. Databases provide the WORST feature set for integration problems and will ALWAYS end up hosing you. There are other platforms and frameworks that are designed for integration challenges and handle them in infinitely better ways.

If you're not integrating through the database, all these arguments of permissions and versioning go away. The DB is an implementation detail of the application and changes when the app changes and they are kept in sync. Avoiding integration through the database prevents all these other problems.

Practice safe enterprise integration: Don't share connection strings!

Bill said,

I think by now anyone who's paying attention realizes that parameterized queries have the same advantages as stored procedures with regard to parsing/execution plans and SQL injection. The permissions advantages aren't really there anymore, either, since everyone is just connecting with one ID these days and setting up their own security systems. That doesn't mean there aren't still good reasons for using stored procedures:

1. It's frequently a lot less work for the DB server to just return answers or run a small job on its own than for it to return enough information to another tier for it to be done elsewhere. This may be bandwidth, or it may just be knowing when it can find an answer with an index and skip the real table.
2. Similarly, when programming stored procedures, you can just use the data you need, since it's right there, without worrying about what you do and don't need to pull between layers. You also don't have to have to make any distinction between data that's native to the environment you're working in and data that's using an interface to a foreign system (granted, an ORM gets you that benefit, too).
3. While stored procedures do require other people to get involved in a release, they also frequently allow problems to be fixed without rolling out a new version of the entire system.
4. Oracle isn't as good at this, but with PL/SQL, you can frequently get jobs done with a lot less set-based relational code than the equivalent object-oriented approach would require.
5. In a stored procedure language, you always have good support for Nulls.
6. If you put *all* of your business logic in stored procedures, that actually gives you a pretty good MVC breakdown.
7. The database is the only layer that's *always* present, so doing work in stored procedures gives you a lot better resources when you're doing ad-hoc, one-time jobs.
8. If you change front-end or middleware architectures, your procedures are still good.


As for downsides, yes you have vendor lock-in (so stored procedures are largely ruled out if you're writing software for other companies to run at their own sites), and yes, many programmers just aren't very good at SQL. Also, you then have to deal with a database interface (and probably manual code check-ins), as well as your IDE. And automated testing becomes a much more do-it-yourself affair.

Enzo said,

Business logic in SPs don't scale? What? Java idiots always say this plus the 3 tier design crap. I know the arguments for doing so. But, let me give you an Oracle example of doing otherwise.

If you use SPs, you get the advantage of scaling if you are using Oracle RAC as you just scale out the cluster, plus you automatically get disaster recovery by using Data Guard since the SPs are part of the database. Not only that, but they are backed up with the db too. Separating the logic from the data is nonsense. You waste time and resources shipping the data back to the middle tier to apply the logic. This can be done much faster right in the db itself. The Java crowd has fooled you. Every Java site I've ever visited that used a 3 tier architecture is slower than fuck.

asp.net said,

good article

mikeomatic.net said,

Pingback from mikeomatic.net

Mike-O-Matic » A Modest Defense of Stored Procedures

Chris said,

This article is the biggest bunch of FUD and just ignorance I have seen in a while. I'm not for or against stored procs because sometimes I see them and sometimes I don't. But in particular points 5 and 6 are ridiculous.

Stored procedures have version control just like C#. They don't.... You have to make the decision to use or not use version control. C# doesn't do version control, Source Safe is. I believe the most recent versions of visual studio have even better support for editing SQL and hence you can use the visual studio source control options on the IDE to check in or our SQL scripts. Most big companies I have seen develop all SQL into files (especially stored procedures). Then the procedures are created on the database by running the files. And they follow a testing procedure (test on test, then promote to production). This is the same as anything else. The files are typically put into version control. So your point about no version control is completely off base and full of ignorance.

As far as keeping servers synched, how do you keep app servers synched? You probably create build scripts to push the application to multiple servers. Well why wouldn't you do the same with SQL? It takes less than an hour to create a Perl or Python script to read a bunch of SQL files, and execute them against a list of databases. Again I would say this is a lack of programming skills on your part. Now whether you want business logic in the database is another argument which has different answers...but in a lot of cases the answer is yes (like you have multiple apps and use the data for a bunch of other things and need to ensure integrity).

Now point 3 just sounds like you don't really know LINQ. I'm sorry but the syntax for LINQ is basically SQL with some .NET like syntax wrapped up in. If you can't write SQL you really have no business using LINQ. If you can write LINQ then you are just lazy. That's all there is to it. LINQ has select, where, and group by. Additionally to me the syntax seems a bit harder than SQL. As far as embedded SQL in the app. A stored procedure is just a query with some parameters and a create procedure statement wrapped around it. If you can't change a procedure you have no business writing dynamic SQL. If you are paying DBAs, they should probably be writing the SQL to put in the app anyway. If you have access to the database then whether you put the SQL in the code or a stored procedure is a matter of taste. But saying you don't want to muck with SQL because you don't know it seems to indicate you have no business writing dynamic SQL or LINQ.....

Andy Hitchman said,

I believe Chad has cut to the heart of the matter. It's about long-term maintainability. The business logic is the most complex part of your app, so you should make every effort to make it obvious and transparent.

So I guess you either put ALL of your business logic in the application or ALL of your business logic in sprocs. Otherwise you're going to make life very hard for yourself or the unfortunates who inherit your application in the future.

To aspire to an application that has a life beyond it's first release, by which I mean we have a code base amenable to change and extension, I'd choose putting my logic in the application any day and keep the db as simple as possible.

Having said that, I'm creating solutions where the db is (deliberately) nothing more than a repository for data OWNED by the application. I've previously worked at organisations that are desperate for an Enterprise Data Model and one big database. Given the hideous complexity of the schemas in such environments, I'd want to hand off the responsibility of making it work to a DBA.

Andy Hitchman said,

I believe Chad has cut to the heart of the matter. This discussion should be focussing on long-term maintainability. The business logic is the most complex part of your app, so you should make every effort to make it obvious and transparent.

So I guess you either put ALL of your business logic in the application or ALL of your business logic in sprocs. Otherwise you're going to make life very hard for yourself or the unfortunates who inherit your application in the future.

To aspire to an application that has a life beyond it's first release, by which I mean we have a code base amenable to change and extension, I'd personally choose putting my logic in the application any day and keep the db as simple as possible. Having said that, I'm creating solutions where the db is (deliberately) nothing more than a repository for data OWNED by the application.

I've previously worked at organisations that are desperate for an Enterprise Data Model and one big database. Given the hideous complexity of the schemas in such environments, I'd want to hand off the responsibility of making it work to a DBA.

Petar Repac said,

For all of you that think that database is merely a dummy storage:
a) why is then the DB usually the most expensive component of majority of apps
b) if you do not use logic in DB that means that you certainly do not use NOT NULL, PRIMARY KEY, FOREING KEYs and any other type of check constraints, as that is also business logic
c) if you think in terms of time what will most likely change DB, BLL or UI. What MS changes this days: ADO.NET, LINQ2SQL, Entity Framework, WPF, Silverlight, MVC.
d) what is more important: beautiful UI, performance, code reuse, .... or data integrity ? If you corrupt your data anything else does not matter. To me DB is the best place to ensure data integrity.

Regards, Petar

infinitedesigns.org said,

Pingback from infinitedesigns.org

.:Infinite Designs:. » Stored Procedures vs Dynamic (Parameterized) Queries

tukang nggame said,

I do not know about this, but I very interested in this discussion.
Thanks to all for taking the time to share this

Liverpool dentist said,

Alex you are right he has got all this negatively way.

club penguin said,

This isn't about hardcoded in line SQL which everyone knows sucks, but generated SQL such as from LINQ to SQL, LINQ to Entities or NHibernate. Developers don't write it, they just set up the mapping rules between the objects and the database.