x

Stored procedures vs a business logic layer in code

Almost certainly going to get flamed on this one - probably in the same league as 'Emacs or vi?'...

In this podcast, the team that are behind StackOverflow basically say that they think that the days of stored procedures are coming to an end. Now, obviously they're coming at it from a developer perspective, and I'm wondering if there are counter-arguments from a DBA perspective.

more ▼

asked Oct 15, 2009 at 07:29 AM in Default

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(comments are locked)
10|1200 characters needed characters left

12 answers: sort voted first

It breaks down this way, you can build a well-designed front-end application that uses well built parameterized queries and get just about every bit of performance and plan reuse that you can get out of stored procedures. There are two issues that will probably keep stored procedures alive into the future. The first is security. You can do a lot more granular manipulation of security through stored procedures than you can through parameterized queries. That's not needed for lots of applications, but it is for others. The other issue is deployment. I know that some developers and some shops have been able to get deployment down to a fine art so that they can make lots of small code changes and get them into production quickly and easily, so if query tuning is needed in these shops, changes to the code that generates the queries can be quickly rolled out into production. But lots of shops are not able to do this. So having an added layer, through stored procedures, allows for quick deployment and modifications that don't require complete code builds and all the associated testing required.

All this assumes that you're not talking about straight ad hoc SQL. That's still a frightening abomination, performance bottleneck, security hole, and all around problem. I know that lots of developers are in the "deliver the code and leave" mode of consultants. But for those that work full time and have to maintain applications over an extended period of time (I'm working on apps that are over 10 years old and there are 20+ year old apps in the company I work for) making poor design choices, such as ad hoc SQL, have enormous long term repercussions and ultimately have to be replaced with better applications.

more ▼

answered Oct 15, 2009 at 09:17 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

The other assumption is that the developers writing the front end application understand how to code SQL for performance. I've seen too many instances where this was not the case.
Oct 23, 2009 at 09:40 PM Bob Hovious
Excellent point, Bob. I believe in separation of responsibility based on areas of expertise. There are front-end image/style experts, front-end coding experts, business rules experts, database experts, etc. Asking one individual to handle multiple areas often (but not always) leads to less-than-ideal work in one of them. In my opinion, there is no such thing as a "developer expert." There are just too many areas to become an expert at all of them.
Nov 13, 2009 at 02:01 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

I will always fall on the side of using stored procedures, because they are a much much cleaner interface to the database than queries dotted through source code. For example, if I know that stproc_ValidateUserLogin takes a login name and a password hash, and gives me back a user level - then do I really need to know whether that procedure accesses tblCfgUsers or tblCfgUsersNew underneath?

The other main key for me is in terms of producing reports. The way that I engineer things is to get as much of the job done as close to the source as possible. If I can return 15 rows of report to a client app, instead of 100,000 rows of source data, then that is a good thing. I am also more than willing to admit that SQL Server will do a better job of handling and aggregating that data than I would ever do in my own code...

The optimisation argument is probably tired now, seeing as the query optimiser now does a much better job of making plan re-use apply to ad-hoc queries as well as stored procedures.

more ▼

answered Oct 15, 2009 at 08:57 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

some ad hoc queries, but not that many, unless you mean parameterized queries.
Oct 15, 2009 at 09:18 AM Grant Fritchey ♦♦
I am a big fan of component-based programming. Using your example, if the application needs to get a user level based on the login name and password hash, I would expect the application developer to put that request into the database development team. That team would then respond with a nice, neat procedure call or similar method. Of course, in some organizations, one person is the application developer and the database developer and the tester and ... Even then, I prefer the black box approach as I find it makes unit testing and future maintenance easier.
Nov 13, 2009 at 02:07 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

I tend to agree with Grant's analysis (And voted it up). Timothy has a good comment as well.

I think the performance benefits are small at this point. The optimizer is very good, and even the ORM code is getting better. So is there a reason to use them?

For most people I would say yes. Timothy's premise of centralization, or code reuse, is a big one. You can get some of this be encapsulating the calls in a single method in an OOP client, but is that any better? Why not just write the procedure? I think you are splitting hairs here, and in that case, stored procedures are tuneable, and if you had to go cross platform, you could write the efficient procedure needed on each platform, without changing the client.

Security is the big one for me. Better control, and if you avoid dynamic SQL, you can avoid lots of SQL Injection.

Stack Overflow, and specifically Jeff Atwood and Joel's programmers are very, very disciplined, and very smart. I think what works for them doesn't necessarily work for others.

more ▼

answered Oct 22, 2009 at 08:21 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

Just as a general comment, this type of question might be better at the main SSC site then here. I doubt there is any clear answer to this question.

With that out of the way, I do see ORM's such as Microsoft's Linq and SQLAlchemy for Python reducing the prominence of stored procedures, especially as they get better at what they do.

With that said, I think stored procedures have advantages that will keep them around for the forseeable future.

  1. As mentioned by Grant Fritchey, they can give you an added layer of security. In some situations this is of no concern at all, but in some, especially if people from outside the company need access to write certain types of code, this can be of great significance.

  2. At least for the time being, a hand written stored procedure is often more effecient than an ORM. This is especially true for more complex queries. This may change in the future as ORMs and the database engine evolve, but it will remain true for the forseeable future.

  3. There are some tasks that are strongly set based that are simply better handled in the set based SQL than they are in Object-oriented/procedural/functional languages (though functional is somewhat closer). You can of course simple compose the SQL directly and have the application send that on to the database engine, but then you lose many of the advantages of SQL development tools like SSMS.

  4. Centralization. If a set of logic to be used with SQL is needed by multiple applications it often makes most sense to have it centralized within the DB itself. You can mimic this by putting that code to generate that SQL in a shared library of course, but that becomes awkward if the applications are in multiple languages. By using stored procedures you have one centralized place for that shared logic.

more ▼

answered Oct 16, 2009 at 03:56 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

I find another reason to use stored proc's is dependency. If you have to make a change to a table or view, is sure is nice to know what objects depend on the table or view. With sql in the application, it is much more difficult to know what you have to test when making that kind of change.

I am pushing to put a policy in place to make all datasources for reporting service be stored procedure based because of this problem.

more ▼

answered Oct 23, 2009 at 05:14 PM

Paul Ward gravatar image

Paul Ward
109

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x412
x4
x2
x1

asked: Oct 15, 2009 at 07:29 AM

Seen: 9355 times

Last Updated: Jan 25 at 09:32 AM