Stored Procedures a bad practice at one of worlds largest IT software consulting firms?

Question

I'm working at a project in one of the world's top 3 IT consulting firms, and was told by a DBA that company best practice's state stored procedures are not a "best practice". This is so contrary to everything I've learned.

Stored procedures give you code reuse, and encapsulation (two pillars of software development), security (you can grant/revoke permissions on an individual stored proc), protect you from SQL injection attacks, and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

We're developing a complex app using Agile software development methodology. Can anyone think of good reasons why they wouldn't want to use stored procs? My guess was that the DBAs didn't want to maintain those stored procs, but there seem to be way too many negatives to justify such a design decision.

Answer 1

In my experience working on very large projects, you have to be very clear on where business logic lives. If you allow an environment where individual developers can put business logic in the business object layer or in a stored procedure as they see fit, a large application becomes VERY difficult to understand and maintain.

Stored procedures are great for speeding up certain DB operations. My architectural decision is to leave all logic in the business layer of the application and employ stored procedures in a targeted manner to improve performance where benchmarking indicates it is warranted.

Answer 2

Some Observations

Stored procedures give you code reuse, and encapsulation (two pillars of software development),

Only if you use them correctly in the context in which they are supposed to be used. The same claim can be said about functions (in structured programming) or methods (in object oriented programming), and yet, we see 1K functions and mega-ass objects.

Artifacts don't give you those benefits. The proper usage of those artifacts is what give those benefits.

security (you can grant/revoke permissions on an individual stored proc),

Yes. This is a good point and one of the main reasons I like stored procedures. They provide a finer-granularity access control than what can be typically achieved with just views and user accounts.

protect you from SQL injection attacks,

That is not specific to SPs since you can achieve the same level of protection with parameterized SQL statements and input scrubbing. I would use SPs in addition to those, however, as matter of "security in depth".

and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

This is highly database vendor specific, but in general your DBA is right. SQL statements (either static or parametrized) do get compiled. SPs help if you want/need to aggregate and compute data that you cannot do with simple SQL statements, but are tightly integrated with SQL and does not warrant the round-trip to the app server.

A good example is querying data into a temporary cursor (or cursors) from which to run another SQL itself. You can do it programmatically in the app server, or you can save the multiple round-trips by doing it in the db.

This should not be the norm, however. If you have many of those cases, then that is a sign of bad database design (or you are pulling data from not-so compatible database schemas across departments.)

We're developing a complex app using Agile software development methodology.

Agility has to do with software engineering processes and requirement managements, and not technologies.

Can anyone think of good reasons why they wouldn't want to use stored procs?

Wrong Question

The question is wrong and equivalent to asking "are there any good reasons not to use GOTO"? I side with Niklaus Wirth more than with Dijkstra on this subject. I can understand where Dijkstra's sentiment came from, but I do not believe it is 100% applicable in all cases. Same with store procs and any technology.

A tool is good when used well for its intended purpose, and when it is the best tool for the particular task. Using it otherwise is not an indication that the tool is wrong, but that the wielder doesn't know what he/she is doing.

The proper question is "what type of stored procedure usage patterns should be avoided." Or, "under what conditions should I (or should not) use stored procedures". Looking for reasons not to use a technology is simply putting the blame on the tool as opposed to placing the engineering responsibility squarely where it belongs - in the engineer.

In other words, it is a cop-out or a statement of ignorance.

My guess was that the DBAs didn't want to maintain those stored procs, but there seem to be way too many negatives to justify such a design decision.

What they are doing then is projecting the results of their bad engineering decisions on the tools they used poorly.

What to do in your case?

My experience is, when in Rome, do as the Romans do.

Don't fight it. If the people at your company want to label store procs as a bad practice, let them. Be advised however, that this can be a red flag in their engineering practices.

Typical labeling of things as bad practice is usually done in organizations with tons of incompetent programmers. By black-listing certain things, the organization tries to limit the damage inflicted internally by their own incompetence. I shit you not.

Generalizations are the mother of all screw ups. Saying that stored procs (or any type of technology) are a bad practice, that's a generalization. Generalizations are cop-outs for the incompetent. Engineers do not work with blatant generalizations. They do analysis on a case-by-case basis, do analysis trade-offs and execute engineering decisions and solutions according to the facts at hand, in the context in which they are supposed to solve a problem.

Good engineers do not label things as bad practice in such generalizing ways. They look at the problem, select the tool that are appropriate, make trade-offs. In other words, they do engineering.

My opinion on how not to use them

• Don't put complex logic beyond data gathering (and perhaps some transformations) in them. It is ok to put some data massaging logic in them, or to aggregate the result of multiple queries with them. But that's about it. Anything beyond that would qualify as business logic which should reside somewhere else.

• Don't use them as your sole mechanism of defense against SQL injection. You leave them there in case something bad makes it to them, but there should be a slew of defensive logic in front of them - client-side validation/scrubbing, server-side validation/scrubbing, possibly transformation into types that make sense in your domain model, and finally getting passed to parametrized statements (which could be parametrized SQL statements or parametrized stored procs.)

• Don't make databases the only place containing your store procs. Your store procs should be treated just as you treat your C# or Java source code. That is, source control the textual definition of your store procs. People rant that store procs can't be source controlled - bullcrap, they just don't know what the bloody hell they are talking about.

My opinion in how/where to use them

• Your application requires data that needs to be transposed or aggregated from multiple queries or views. You can offload that from the application into the db. Here you have to do a performance analysis since a) database engines are more efficient that app servers in doing these things, but b) app servers are (sometimes) easier to scale horizontally.

• Fine grain access control. You do not want some idiot running cartesian joins in your db, but you cannot just forbid people from executing arbitrary SQL statements just like that either. A typical solution is to allow arbitrary SQL statements in development and UAT environments, while forbidding them in systest and production environments. Any statement that must make it to systest or production goes into a store procedure, code-reviewed by both developers and dbas.

Any valid need to run a SQL statement not in a store proc goes through a different username/account and connection pool (with the usage highly monitored and discouraged.)

• In systems like Oracle, you can get access to LDAP, or create symlinks to external databases (say calling a store proc on a business partner's db via vpn.) Easy way to do spaghetti code, but that's true for all programming paradigms, and sometimes you have specific business/environment requirements for which this is the only solution. Store procs help encapsulate that nastiness in one place alone, close to the data and without having to traverse to the app server.

Whether you run this on the db as a store proc or on your app server depends on the trade-off analysis that you, as an engineer, have to make. Both options have to be analyzed and justified with some type of analysis. Going one way or another by simply accusing the other alternative as "bad practice", that's just a lame engineering cop-out.

• In situations where you simply cannot scale up your app server (.ie. no budget for new hardware or cloud instances) but with plenty of capacity on the db back-end (this is more typical that many people care to admit), it pays to move business logic to store procs. Not pretty and can lead to anemic domain models... but then again... trade-off analysis, the thing most software hacks suck at.

Whether that becomes a permanent solution or not, that's specific to constrains observed at that particular moment.

Hope it helps.

Answer 3

The rationale is that relying on a stored procedure layer limits portability and ties you to a certain DB. Added maintenance costs are also cited as a reason. I also wanted to comment on this point you made:

(stored procedures) protect you from SQL injection attacks

It's actually the parametrized querying that protects you, which you can easily do in plain text sql querying.

Answer 4

Some of the reasons that I agree stored procs aren't a best practice.

• Business and application logic should be in the code not in the database. Putting logic in the DB is mixing up concerns.
• You can't test stored procs as seamlessly as code in your conventional unit test projects with the rest of the application logic.
• I don't find stored procs as being conducive to test first programming when I am writing code.
• Stored procs aren't as easy to debug as application code when you are debugging your program in your IDE.
• Versionning control / Source control of SP vs. normal code

Answer 5

Stored procedures give you code reuse, and encapsulation (two pillars of software development),

Yes, but at the cost of being able to meet other agile design goals. They're more difficult to maintain, for one thing. If the project I'm on is any indication, you'll likely end up with multiple, incompatible SPs that do essentially the same job, with no benefit.

protect you from SQL injection attacks,

No. They do not. I can't even begin to guess where this idea might have come from, as I hear it said often, and it's simply not true. It may mitigate certain types of SQL injection attacks, but if you're not using parametrized queries in the first place, that won't matter. I can still ';DROP TABLE Accounts; --

and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

They're also typically compiled when you use prepared, parametrized statements (at least with several of the DBs I've used). By the time your application begins executing the query (or especially when you execute the same prepared query multiple times), any performance advantage that you think your SP has is completely moot.

The only reason to use a stored procedure, IMHO, is when you must make a complex, multi-staged query that pulls from multiple collated sources. SPs should not contain low-level decision logic, and they should never simply encapsulate an otherwise simple query. There are no benefits and only many drawbacks.

Listen to your DBA. He knows what's up.

Answer 6

All three of the companies I work for used stored procedures for their application logic with SQL Server. I have not really seen things the other way. But to me they are a big mess. There typically aren't very good error handling facilities or code re-use facilities with stored procedures.

Let's say you have a stored procedure that returns a dataset you want to use, how can you use it in future stored procedures? The mechanisms on SQL Server for that are not very good. EXEC INTO...only works to one or two level)s of nesting (I forget now). Or you have to pre-define a work table and have it process keyed. Or you need to pre-create a temp table and have the procedure populate it. But what if two people call a temp table the same thing in two different procedures that they never planned to use at the same time? In any normal programming language, you can just return an array from a function or point to an object/global structure shared between them (except for functional languages where you'd return the data structure as opposed to just changing a global structure...)

How about code re-use? If you start putting common expressions into UDFs (or even worse sub queries) you will slow the code to a halt. You can't call a stored procedure to perform a calculation for a column (unless you use a cursor, pass the column values in one by one, and then update your table/dataset somehow). So basically to get the most performance, you need to cut/paste common expressions all over the place which is a maintenance nightmare... With a programming language you can create a function to generate the common SQL and then call it from everywhere when building the SQL string. Then if you need to adjust the formula you can make the change in a single place...

How about error handling? SQL Server has many errors that immediately stop the stored procedure from executing and some that even force a disconnection. Since 2005 there is try/catch but there are still a number of errors that cannot be caught. Also the same thing happens with code duplication on the error handling code and you really cannot pass exceptions around as easily or bubble them up to higher levels as easily as most programming languages.....

Also just speed. A lot of operations on datasets are not SET oriented. If you try to do row oriented stuff, either you are going to use a cursor, or you are going to use a "cursor" (when developers often query each row one by one and store the contents into @ variables just like a cursor...Even though this is often slower than a FORWARD_ONLY cursor). With SQL Server 2000 I had something that was running for 1 hour before I killed it. I rewrote that code in Perl and it finished in 20 minutes. When a scripting language that is 20-80x slower than C smokes SQL in performance, you definitely have no business writing row oriented operations in SQL.

Now SQL Server does have CLR integration and a lot of these issues go away if you use a CLR stored procedures. But many DBAs don't know how to write .NET programs or turn off the CLR due to security concerns and stick with Transact SQL.... Also even with the CLRs you still have issues of sharing data between multiple procedures in an efficient way.

Also in general the hardest thing to scale out is the database. If all your business logic is in the database, then when the database becomes too slow you are going to have issues. If you have a business layer you can just add more caching and more business servers to boost performance. Traditionally another server to install windows/linux and run .NET/Java is much cheaper than buying another database server and licensing more SQL Server. SQL Server does have more clustering support now though, originally it did not really have any. So if you do have a lot of money, you can add clustering or even do some log shipping to make multiple read only copies. But overall this will cost much more than just having a write behind cache or something.

Also look at the Transact-SQL facilities. String Manipulation? I'll take the Java String Class/Tokenizer/Scanner/Regex classes any day. Hash Tables/Linked Lists/Etc. I'll take the Java Collection frameworks, etc... And the same for .NET... Both C# and Java are way more evolved languages than Transact SQL...Heck coding with Transact-SQL makes me jealous of C...

On the plus stored procedures are more efficient for working with a big dataset and applying a multiple queries/criteria to shrink it down before returning it to the business layer. If you have to send a bunch of huge datasets to the client application and break down the data at the client it will be much more inefficient than just doing all the work at the server.

Also stored procedures are good for security. You can cut all the access to the underlying tables and only allow access through the stored procedures. With some modern techniques like XML you can have stored procedures that do batch updates. Then all access is controlled through the stored procedures so as long as they are secure/correct the data can have more integrity.

The SQL injection argument doesn't really apply so much anymore since we have parameterized queries on the programming language side. Also really even before parameterized queries a little replace("'", "''") worked most of the time as well (although there are still tricks to use to go past the end of the string to get what you want).

Overall I think SQL and Transact SQL are great languages for querying/updating data. But for coding any type of logic, doing string manipulation (or heck file manipulation....you'd be surprised what you can do with xp_cmdshell....) please no. I'm hoping to find a future place that does not use stored procedures mostly. From a code maintainability point of view they are a nightmare. Also what happens if you want to switch platforms (although really if you paid for Oracle/DB2/Sybase/Sql Server/etc. you might as well get everything you can out of them by using every proprietary extension you can that helps you out...).

Also surprisingly often the business logic is not the same. In the ideal world you would put all the logic in stored procedures and share that between applications. But quite often the logic differs based on the applications and your stored procedures end up becoming overly complex monoliths that people are afraid to change and do not understand all the implications of. Whereas with a good object oriented language you can code a data access layer which has some standard interface/hooks that each application can override to their own needs.

Answer 7

This was the official line when I worked for one of the Big Five a few years back. The rationale was that since SPs are tied to particular implementations (PL/SQL vs T/SQL vs ...), they unnecessarily limit technology choices.

Having lived through the migration of one large system from T/SQL to PL/SQL, I can understand the argument. I think it's bit of a canard though - how many places really move from one database to another on a whim?

Answer 8

How do you version stored procedures on the server?

If you redeploy stored procedures to the server from version control, you blow out the stored execution plan.

Stored procedures should not be modifiable directly on the server, otherwise how do you know what's really running _right now? If they are not, the deployment tool needs access to write stored procedures to the database. You'd have to deploy on every build (exec plan might need to be different)

While stored procedures are non-portable, neither is SQL in general (ever seen oracle date handling--uggghhh).

So, if you want portability, build an internal data access API. You can call this like function calls, and internally you can build in in whatever lingo you want, with parameterized queries, and it can be version-controlled.

Answer 9

This is so contrary to everything I've learned.

You might need to get out more. [grin] Seriously, stored procs have been on the decline for at least 10 years. Pretty much ever since n-tier replaced client-server. The decline has only been accelerated by the adoption of OO languages like Java, C#, Python, etc.

That's not to say stored procs don't still have their proponents and advocates - but this is a long-running discussion and debate. It's not new, and will likely still be going on for quite some time; IMO, the opponents of stored procs are clearly winning.

Stored procedures give you code reuse, and encapsulation (two pillars of software development)

Very true. But, so does a decently architected OO layer.

security (you can grant/revoke permissions on an individual stored proc)

While you can do that, few do it because of the serious limitations. Security at the DB level isn't granular enough to make context-aware decisions. Because of performance and management overhead, it's unusual to have per-user connections as well - so you still need some level of authorization in your app code. You can use role based logins, but you will need to create them for new roles, maintain which role you're running as, switch connections to do "system level" work like logging, etc. And, in the end, if your app is owned - so is your connection to the DB.

protect you from SQL injection attacks

No more so than doing parametrized queries. Which you need to be doing anyway.

and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

I think that started in MSSQL 7 or 2000. There's been a lot of debates, measurements, and misinformation on stored proc vs inline SQL performance - I lump it all under YAGNI. And, if you do need it, test.

We're developing a complex app using Agile software development methodology. Can anyone think of good reasons why they wouldn't want to use stored procs?

I can't think of many reasons you would want to. Java/C#/any 3rd GL language are all much more capable than T-SQL at encapsulation, reuse and felxibility, etc. Most of which is free given a half-decent ORM.

Also, given the advice to "distribute as needed, but not more" - I think the burden of proof these days is on SP advocates. A common reason for going stored proc heavy is that T-SQL is easier than OO, and the shop has better T-SQL devs than OO. Or, that the DBA stops at the database layer, and stored procs are the interface between dev and DBA. Or, you're shipping a semi-custom product and the stored procs can be user-customized. Absent some considerations like that, I think the default for any Agile SW project these days is going to be ORM.

Answer 10

Who do you work for?

The answer may depend on who you are employed by, the consulting firm or the company itself. What's best for a company is very often not best for a consulting firm or other software vendor. e.g. A smart company desires to have a permanent advantage over its competitors. By contrast a software vendor wants to be able to hawk the same solution to all the businesses in a particular industry, for the lowest cost. If they are successful in this, there will be no net competitive advantage for the client.

In this particular case, applications come and go but very often, the corporate database lives on forever. One of the primary things an RDBMS does is to keep junk data from entering the database. This can involve stored procedures. If the logic is good logic and highly unlikely to change from year to year, why should it not be in the database, keeping it internally consistent, irrespective of whatever application is written to use the database? Years later someone will have a question they want to ask of the database and it will be answerable if junk has been prevented from entering the DB.

So maybe this has something to do with the fact that your DBA works for a consulting firm. The more portable they can make their code, the more they can reuse code from client to client. The more logic they can tie up in their app, the more the company is wedded to the vendor. If they leave behind a big mess in the process, they will get paid to clean it up, or never see the mess again. Either way it's a win for them.

For (lots) more discussion on both sides of the fence, read the discussion at coding horror. FWIW I lean to the side of those advocating for SPs.

Answer 11

Considering all the above cases, I would like to add one more. The choice of SP may be depends on people choice as well.

I personally feel frustrated when people puts very complex logic in SP and I believe such SP is very complex to maintain and debug. Even many cases the developer himself faces problem when debugging in code behind (say language part) is much more easier than in SP.

SP should only be used for simple operations. Well that is my choice.

Answer 12

I want to cover both some pro- and con- issues with stored procs. We use them extensively with LedgerSMB, and our rule is, with a few very specific extension, "if it's a query, make it a stored proc."

Our reason for doing this was to facilitate cross-language query reuse. There isn't a better way to do this honestly.

In the end the question is always on the details. Well used, stored procedures make things a lot easier, and poorly used they make things a lot harder.

So on to the con side.

1. As traditionally used, stored procedures are brittle. Used alone they create the possibility of adding bugs to your code in places you didn't expect for no reason other than that the call syntax changed. Used alone this is a bit of a problem. There is way too much cohesion between layers and this causes problems.

2. Yes, it is possible to have intra-sproc sql injection if doing any dynamic sql. It is a bad thing to be over-confident in this area, and consequently one needs to have significant experience in security in this area.

3. Changes to interfaces are somewhat problematic with stored procedures for reason #1 above but this can become a very big nightmare if large numbers of client apps are involved.

The above are pretty hard to deny. They happen. Everyone, pro-SP and anti-SP has probably had horror stories regarding these. The problems aren't unsolvable but if you don't pay attention to them you can't solve them (in LedgerSMB we use a service locator to dynamically build SP calls at run-time, avoiding the above issues entirely. While we are PostgreSQL only, something similar could be done for other db's).

On to the positives. Assuming you can solve the above problems, you get:

1. The possibility of enhanced clarity in set operations. This is particularly true if your query is very large or very flexible. This also leads to enhanced testability.

2. If I have a service locator already working in this area, I find stored procedures speed up pace of development because they free up the application developer from db concerns and vice versa. This has some difficulties in doing right but it is not that hard to do.

3. Query re-use.

Oh and a few things you should almost never do in an SP:

1. non-transactional logic. You sent the email that the order was shipped but the transaction rolled back... or now you are waiting to continue for the email server to come online.... or worse you roll back your transaction just because you can't reach the email server....

2. lots of little queries loosely strung together, sprinkled with procedural logic....

Answer 13

It's very difficult to switch database brands and utilize the same stored procedures.

Your team either doesn't have a DBA and no one else wants to have anything to do with sql.

This is nothing more than a programmer v DBA pissing contest.

Answer 14

IMO it depends. Stored procedures have their place, but they are not a best practice, nor are they something to be avoided at all costs. A smart developer knows how to properly evaluate a given situation and determine if a stored procedure is the answer. Personally I am a fan of using an ORM of some kind (even a basic one like raw Linq to Sql) rather than stored procedures except maybe for predefined reports or similar but again it's really a case-by-case basis.

Answer 15

It's always a source of problems to have the business logic splitted up between different layers, using different programming languages. Tracing a bug or implementing a change is way harder when you have to switch between worlds.

That said, I know companies that do pretty well by putting all business logic into PL/SQL packages living in the database. Those are not very large applications, but not trivial either; say 20K-100K LOC. (PL/SQL is better suited for that kind of system than T-SQL, so if you only know T-SQL, you probably shake your head in disbelieve now...)

Answer 16

This is another point not mentioned yet:

Code generation tools and reverse engineering tools cannot really cope well with stored procedures. The tool generally cannot tell what the proc does. Does the proc return a resultset? Several resultsets? Does it fetch its resultsets from several tables and temporary-tables? Is the proc just an encapsulated update statement and does not return anything? Does it return a resultset, a return value and some "console output"?

So if you want to use a tool to create a data-transfer-object DTO and DAO layer automatically (such as liferay's "service builder" does) , you cannot easily do so.

Moreover, ORMs like Hibernate cannot really work properly when the data source is an SP. Data access is read-only at best.

Answer 17

Programming solo, I cannot resist writing stored procedures.

I'm using MySQL primarily. I have not used object-oriented databases before like PostGreSQL, but what I'm able to do with SP's in MySQL is abstract the table structure away a little bit. SP's allow me to design these primitive actions, whose inputs and outputs will not change, even if the database underneath it does change.

So, I have a procedure called logIn. When you log in, you always just pass username and password. The result passed back is the integer userId.

When logIn is a stored procedure, now I can add additional work to be done at login that happens at the same time as the initial log in. I find a series of SQL statements with embedded logic in a stored procedure easier to write than the (calling environment FETCH) -> (get result) -> (calling environment FETCH) series you have to do when you write your logic server side.

Answer 18

I want to also point out that stored procedures do use cpu time on the server. Not a lot, but some. Some of the work done in the work procedure could be done in the app. It's easier to scale the app layer than the data layer.

Answer 19

I agree with Mark that the community has really been moving away from stored procedures for quite some time now. While many of the points the original poster raised why we may want to use SPs were valid at one time, it has been quite a while and, as another poster said, the environment has changed. For instance, I remember one argument FOR using SPs 'back in the day' was the performance gains achieved because their execution plans were 'pre-compiled' while dynamic SQL from our code had to be 're-compiled' with each execution. This is no longer the case as the major databases have changed, improved, adapted, etc.

That said, we are using SPs on my current project. The reason is simply because we are building new applications on top of an existing database that still supports legacy applications. As a result, making schema changes is very difficult until we turn off the legacy apps. We made a conscious decision to design our new applications based on the behavior and rules required for the application and to use the SPs to temporarily interface with the database the way we'd like it to be and allow the SPs to adapt to the existing SQL. This goes to a previous poster's point that SPs make it easier to make changes at the database level without requiring changes to the application code. Using SPs as an implementation of the Adapter pattern certainly makes sense to me (esp. given my current project), and may be the only argument I can really see FOR their use today.

Fwiw, it is our intention to remove the SPs when the schema is updated. But, as with everything else in corporate development, we'll see if that ever happens! [grin]

Answer 20

I just wanted to make a concise overview of how I would recommend using stored procedures. I do not think they are a bad practice at all, and like others have said they should be used in the proper situations.

I can see problems where writing procedures for different applications can become confusing in functionality and separate the business logic of the application and result in the database becoming more disorganized and restrictive as well.

Therefore, I would use a stored procedure in relational data-oriented tasks specific to the database. In other words, if there is logic used for database operations that is consistent with the data for any application, a stored procedure could be used to keep the data stored consistently (makes sense). I think good examples of this are: consistent logging, consistent maintenance, working with sensitive information, etc.

Other tasks the manipulate the data to fit the needs of the application that follow the strong data model of the database, I think, should then be stored in another layer containing the business logic. In short, database specific data manipulation for consistency could use stored procedures, where the consistency extends past the database integrity schema model.

Answer 21

Besides distributing business logic needlessly, and tying you to a specific database vendor, I also firmly believe in using a technology for what it was intended. A database is just that, a relational data store. Use it to store data, nothing else.

Choose your tools wisely and you'll save yourself a world of hurt in the long run.