Do we need to promote stored procedures anymore?

Question

5.00/5 (1 vote)

See more:

Once upon a time I was under the influence of vendor marketing and used stored procedures extensively, now I am against them altogether, so that's where I stand.

The vendors have you believe that :
1) They are more secure. Implying that you access your data through them nstead of direct table access.
2) They are faster.
3) You can customize them outside your application compiled logic.

My case against them is this :
1) They are infinitely more complex to write and debug.
2) They are not faster
3) They are not needed with the new programming models.
4) They lock you down to a vendor.
5) Customizable yes but a maintenance nightmare.

1)More complex
I guess no one would argue that they are more complex to write and debug even with our new tools.
The point here is that once you have done this, no one dares to touch them again.

I consulted on a project for a Core Banking vendor which had about 1 million lines in PL/SQL on Oracle, and the system was only 50% done. They couldn't get people to continue the work as it was so convoluted.

2)Not Faster
This is a shocker, they are not faster at least in my tests. I had an old project which was built on SQL Server and I wanted to have it portable to MySql, SQLite and other DB's. The app used 3 stored procs and 4 table functions
which are not available on the other systems.

I decided what the hell, I will take the queries in the procs and write them as strings in my DAL layer.
The shocking thing was that the code in the DAL performed 20% faster than the procs on the same platform.
Better still I was able to optimize the strings queries (which I dared not in SP) and make them another 10% faster and 50% smaller (character count) [4 functions and 1 SP in one dynamic query string].

All the while it worked on all the DB's I wanted.

3)Not Needed in New Models
Once upon a time we did application integration at a data layer, and that's where all the SP's and Function need originated from, to ensure data integrity and access control.

Nowadays, no one in their right mind would expose their database to the world. They build application layers which abstract the data layer and offer that as API's etc.

So most of the benefits of SP security is handled at a higher level than the database anyway.

4)Vendor Lock down
No surprise here, although many people might not mind.

5)Customizable
This I partially agree on, and in certain cases it does help you get a quick fix done on site.

But the problem is if you are writing and selling products as opposed to one-off solutions, this becomes a maintenance nightmare as production changes overwrite the SP and mess up the users expectations.

Your thoughts please.

Posted 24-Sep-11 22:14pm

Mehdi Gholam

Add a Solution

Comments

DaveAuld 25-Sep-11 4:37am

Would this no be better in the forums and not the Q&A as you are initiating a discussion and not a straight forward question? The likely forum would of course be Database

Mehdi Gholam 25-Sep-11 4:47am

Thanks, I posted a link in the database forum, frankly after all the questions I have answered here I thought a more substantial one was needed here. :)

Open to suggestions.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sander Rossel · Accepted Answer · 2011-09-24T23:03:00

I partly agree. I do not quite get your "SP's are not faster" point. You make Command Objects with query strings? You pass to them Parameter Objects so you have a parameterized query? If you go at it that way I can imagine there is no speed penalty, since that approach simply executes the query string as you would normally execute an SP. But if you go about it that way I don't see how that would be any less difficult than an SP, since you still need the query string. And then I prefer a formatted SP over an all red String in .NET.

I think the real DB access revolutions (or evolution?) are caused by stuff such as LINQ, which makes it quite easy to write readable code that is easy to debug and gets you the result you want. However, LINQ is not capable of doing just anything you might want (or maybe I am just not skilled enough with LINQ).

I am currently working with Entity Framework 4 and I still need SP's for certain tasks. Usually because they are faster and I can integrate them in EF, unlike Command Objects.

I agree with you that SP's are hard to read, write and debug (but I am not doing it daily either). But what really bugs me is that your application logic is scattered between code and DB. Sometimes I am reading/debugging code and I can simply follow every little step the code makes before it comes to some final value I need. And then, all of a sudden, I come across an SP... What does it do? Need to startup SQL Server, login, find the SP (no small task in our DB...) and finally read the whole friggin thing, memorize variables and their values argh!... And if it is wrong that is even worse! Can I alter it? Where is it used? Is it only wrong in the context I have just debugged or is it wrong in other places too? There is no easy way to find out.

Now the upsides. If you have a bug in code and it needs to be fixed you need to build a new version, put it live, call the customer that they restart their application. If the bug is an SP you can simply replace the old SP with the new one and the customer will never notice.
My company also has a customer that has some (self-proclaimed X| ) DBA's that want to be able to get to the database. They demand some functions are written as an SP so they can view, and if necessary, alter them later. Luckily they usually call us before doing anything and if they don't it's their own risk. But it makes SP's a necessary evil for us.

I am no fan of SP's either, but taking into account the pro's and cons I cannot conclude, but that they are a necessary evil (customer demands, certain DAL approach (EF and LINQ), legacy, and sometimes even boss' wishes).
Perhaps in some far away future we do not need SP's at all anymore, but untill then I guess we have to learn to live with them.