What is the downside of using stored procedures all the time?

Question

I read a book on SQLServer 2008. Within this book the author stated that although stored procedures are mostly the solution, you should avoid using them all the time.
I know that stored procedures are pre-compiled which as a result makes them run faster than normal commands. Also because they use parameters for passing data, they're far safer than normal SQL commands in case of SQL injection attacks.
So what I don't understand is: Why not always use stored procedures?

Possibly a duplicate of this question? http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code — spike, Sep 07 '11 at 14:24

remi bourgarel · Accepted Answer · 2011-09-07T16:14:42.960

A good article on the subject

http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html

So I think you should do what you prefer. There is no performance difference (for msot of the query you'll have to run).

I'd say go for no stored procedure : stored procedure are a pain in th a.. :

no overloading : If you want to add a parameter you'll have to update all your calls (or create a new SP)
no complex type : with dynamic sql you can build all your sql filter like you want depending on your complex objects
securiy is not a reason : if your sql query are sql injection proof and your database is not available for everybody, you can handle your data access security policy at the application level (any dba would kill me saying this, but any dev would agree... I guess)

SP are "pre-compiled" (at the first execution, the database server will find the best execution plan, for SQL server), BUT in our time we can forget about it, the "compilation" time is really little so we don't have to worry about it. I never saw a case when I thought "OMG the compilation time is my application bottleneck", most of the time your application bottleneck will be the query itself, so don't worry about performance when you don't have to.

And this "pre-compilation" depends on the parameters you send to the SP (on the first call), so sometimes you can have a lot of performance problem (called "parameter sniffing") with SPs (see here :http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html).

Most of the cons listed in the article are the same with inline code. Like #4. Plus, What about security? What I same query in different places and I suddenly need to changed that query? — atoMerz, Sep 07 '11 at 14:55
@AtoMerz : you create a librairy with all your methods. What if you want to add a parameter ? you'll have to update all your calls. With #4 you can create your filters with your complex object so you don't have to find a way to pass them. Security ? If you protect your self against sql injection and your server (= parametrized query) is not accessible from the outside, why would you want to limit a database user access to a specific query ? — remi bourgarel, Sep 07 '11 at 15:02
Can you please add what you said in comment to you answer, so people can find it easier (And if you agree, as @jack Merchetti said SPs are not pre-comepiled). I'll accept it as answer. — atoMerz, Sep 07 '11 at 15:52

juergen d · Answer 2 · 2012-02-01T15:20:30.707

2

Stored procedures tend to get big sometimes.
And they are really hard to debug.
If you got a SP containing hundreds lines of code it difficult to find bugs in there.

edited Feb 01 '12 at 15:20

answered Sep 07 '11 at 14:26

juergen d

201,996
37
293
362

1

But isn't that just an argument for breaking your stored procedure into smaller units? And if this isn't possible then you are still going to have the same issue if you submit the batch as an adhoc query too. – Martin Smith Sep 07 '11 at 14:32
How is it easier to debug big codes? Don't I have to look for bugs in them too? – atoMerz Sep 07 '11 at 14:33
1

debugging embedded text, I would imagine, is considerably more difficult than an sp... – Jeremy Holovacs Sep 07 '11 at 14:33
debugging an application with a debugger like eclipse or visual studio is a comfortable thing. you can step each line and see all variable states. but if you call a SP you just get to see the result. – juergen d Sep 07 '11 at 14:54
@Juergen, I can step through my variables even when using SP. And VS is great when you're writing C# code, when it comes to T-SQL, inline code are as bad as SP. – atoMerz Sep 07 '11 at 15:02

score 2 · Answer 3 · edited Sep 07 '11 at 14:32

2

I haven't written a stored procedure in almost 18 months because all my SQL calls are basically done with LINQ using the ADO.NET Entity Framework.

The benefits of using a SPROC as opposed to using LINQ for example are that, simple changes to a SPROC don't require you to recompile and publish a new build.

SPROCs aren't the most "readable". What I mean by that is, if you have a call to: "GetData" in your code, you have to actually go open SQL Server and look at what GetData is doing, as opposed to just looking at the LINQ code to see exactly what data is being returned.

Also, don't ever let anyone tell you that SPROCS are faster because they are compiled or pre-compiled. It's a myth. They aren't.

edited Sep 07 '11 at 14:32

John Topley

113,588
46
195
237

answered Sep 07 '11 at 14:30

Jack Marchetti

15,536
14
81
117

I'm not really sold on LINQ. It's better than concatenating text in possibly-broken ways, certainly, but is still a layering violation. – sorpigal Sep 07 '11 at 14:37
@Jack Marchetti, Thanks about that pre-compilation tip, I googled and it seems you're right. So now I'm wondering "Are stored procedures faster?", since there's the hassle of going through parameter passing. – atoMerz Sep 07 '11 at 14:48
1

"It's a myth. They aren't" - cite references. What I would say is that most performance tuning is to do with proper location of data and design of queries; Typically the compile time is a tiny proportion of overall execution time and thus becomes largely irrelevant for most everyday use relational DBs. – redcalx Sep 07 '11 at 14:51
1

They are only "faster" because their query plan gets cached I believe. not compiled. I might be wrong there as well. – Jack Marchetti Sep 07 '11 at 15:10
I haven't had to use LINQ on anything with super high performance yet, but it has served me well over the past few years. – Jack Marchetti Sep 07 '11 at 15:11

score 2 · Answer 4 · answered Sep 07 '11 at 14:35

If you for some reason really have to dynamically generate SQL queries it can be easier to do that from client code than from within a stored procedure.

Some parts of your application should not go in to stored procedures; you can usually tell because you spend more time writing code to prepare and send meta-data about your application's state to the SP than you do writing the SP--but this is just poorly thought out design.

I think what the author may have been meaning is that you should not just use stored procedures for everything, not that you should use inline SQL instead of stored procedures. I certainly never recommend inline SQL.

I agree, I too think whereever dynamic sql queries are required then it should be written on client code reason being we need not have to add another overhead of storing stored proc compiled that will ultimately be generating an uncompiled query. And benifit of stored procedure gets defeated over here. For queries that can be stored in a compiled form, we can use stored procedur. But still stored procedure makes DB seperate from Client code. I do not prefer inline queries. Thumb up for this answer :) — Shantanu Gupta, Sep 07 '11 at 14:46

score 1 · Answer 5 · edited Sep 07 '11 at 14:32

1

I would say SPs are almost always the way to go, but it's possible that the stored procedure is not written well for an intended task. My counter-answer would be to write a different stored procedure for the intended task.

The only time I've had issues writing stored procedures for stuff is administrative tasks that could involve dynamic sql or multiple batches.

edited Sep 07 '11 at 14:32

John Topley

113,588
46
195
237

answered Sep 07 '11 at 14:24

Jeremy Holovacs

22,480
33
117
254

score 1 · Answer 6 · answered Sep 07 '11 at 14:28

1

I don't use SPs for really simple queries. For example, if I want to show the user all the categories being used in our application, I'll just write SELECT CategoryName FROM Categories rather than make an SP just for that 4 word query.

However, anything that takes any input whatsoever is an SP, no matter how simple it is.

answered Sep 07 '11 at 14:28

Jason

1,325
6
14

What if this simple query is to be executed many times? Wouldn't SP be a much better option since it's pre-compiled? – atoMerz Sep 07 '11 at 14:31
1

While your answer is definitely legitimate, I would argue that you are now employing two mechanisms in your client, which makes things messier than necessary. – Jeremy Holovacs Sep 07 '11 at 14:32
1

@AtoMerZ Yes, probably. That table of mine also has about 50 rows and grows at a rate of about 5 per year. I used to make an SP for everything, and then I just found myself with dozens of SPs that are literally `SELECT column1, column2 FROM table`. I once had 4 SPs that took a different combination of columns with no `WHERE` clause from a 4 column table and thought that was a bit silly. – Jason Sep 07 '11 at 14:40
More over, if I am not using inline query at all. then this will assure me where should i go for my database related query rather than looking on both the places. – Shantanu Gupta Sep 07 '11 at 14:50
@Shantanu - Yes, that's definitely another issue. But we're a small shop with only 1 webapp and 1 desktop app. It's easy to fire up Visual Studio and do a find to see if any table we are changing is being used in an in-line query. If we ever grow to have a dozen apps, I probably would not use this method anymore. – Jason Sep 07 '11 at 15:00

score 1 · Answer 7 · answered Sep 07 '11 at 14:28

They are very good for a lot of processing - they are sometimes faster, although not always, depending on the structure of your data and processing. For loading data, or changing data based on user input, SPs are the preferred route, without a doubt.

However, there are situations where you need to do some data processing, that does not involve user data, that may involve a lot of work writing SPs to do things that you might be able to generate dynamic SQL better.

So I would say that SPs should be most of the time. But do not make it an unbending rule, because there are times when it isn't the correct solution.

score 1 · Answer 8 · answered Sep 07 '11 at 19:34

I prefer to use Stored Procedures. Especially because some of mine are very complicated. You can set optional parameters by using where (@PARAM IS NULL OR [FIELD] = @PARAM), so that helps clear that issue, and create dynamic queries. I like debugging queries in SQL as well. I understand the rest of the cons though.

Use what you're comfortable with, and what gets the job done! If you want to use all SP's, then do it. If you want to hard code them--do it... but good luck.

score 0 · Answer 9 · answered Sep 07 '11 at 14:27

None really.

If you have a situation where you don't want the plan to be cached because of parameter sniffing you can use the RECOMPILE hint on the procedure or individual statements.

Only case I can think of would be a particularly complex dynamic search condition query that may have too many permutations to make separate procedures or statements for and might be easiest to generate in your client language rather than generating in TSQL and using EXEC / sp_executesql

Or obviously you wouldn't bother creating stored procedures for entirely adhoc queries.

What is the downside of using stored procedures all the time?

9 Answers9