Business Logic In Stored Procedures

The current draft of CommonChannelArchitecture states the following:

"The default stance in designing an application should be that business logic is held in the application code, NOT in database stored procedures. Only move business logic into StoredProcedures as performance needs required. "

In your opinion, is this a defensible assertion? Or does it simply reflect the fact that our workshop participants are all primarily application coders as opposed to DBA's? What kinds of criteria should help drive the decision about whether to put application logic in the database or in the application code? -- BillBarnett

In my opinion, StoredProcedures should only be used for some operation that many different applications and/or languages need. It is OnceAndOnlyOnce 101. If many different apps have or will have the same need, then consider factoring it to a stored procedure. You can't readily do this with app code because different languages would need re-coding for themselves.

As far as doing it for performance, it may complicate or slow down maintenance. So be sure you know the trade-offs. I have seen places go hog-wild and turn every query in an application into a stored procedure, when they could have done it to say 10 percent of the most common calls. It seems to me that a smart database system should be able to recognize that a given query is the same as a prior recent one (except parameter values) and use the pre-compiled one from before.

See for Martin Fowler's take on this topic. -- bb

On the one hand, I agree that business logic should NOT be in stored procedures, except where really really necessary. Why? --CosminApreutesei

On the other hand, filtering of data, and querying of data-sets absolutely should be done at the database, using views, or perhaps stored procedures (or whatever drives your particular persistence-layer).All logic in stored procedures is rubbish. All logic in memory after thrashing through a large candidate dataset is also garbage.

Beyond performance there are also data-sharing issues which tend to be more critical. Slow data is one thing, but non-ACID data is a much bigger problem.
Stored procedures are useful and OO advocates tend to shun them a bit too much. Just wrap them in a data access object, and you're good to go. The real problem is that SQL in general requires a knowledge base and approach to programming that is different than in most OO programs. Not a wrong approach, or a worse approach, just a different approach, one that is suited to dealing with large data sets.

If you put all of your SQL in your application system, you are now making your developers the responsible parties for performing SQL. This can be troublesome if you're aiming for database independence. Perhaps a stored procedure interface would be a more practical approach to database independence. You're rarely going to get away from using proprietary SQL if you have a system of significant size anyway.

I (and our other architect) have recently set a direction in our organization to move away from PL/SQL in Oracle. I have been a staunch supporter of using PL/SQL for about 10 years now. My main reasons were about performance and a fuzzy reasoning about separating business logic into stored procedures. When I look back over the years, the painful truth is that we never actually separated business logic into stored procedures in a way that was clean enough for any reasonable reuse. In addition, the last few years has seen two changes that eliminate the performance reason. Newer versions of Oracle are actually becoming quite a bit slower (especially in PL/SQL, even compiled PL/SQL and the even much slower Java Stored Procedures) for our work. Java using JDBC has become much faster. Telling ourselves the truth that we really never made a clean separation of business logic into stored procedures is hard (for me). Admitting that the performance advantage has gone away is less difficult since I do a lot of tuning and my last couple of tuning efforts had solutions involving getting rid of PL/SQL and moving to straight JDBC out of Java for several orders of magnitude in performance gains. Truthfully, straight Java just slams PL/SQL these days. Our mileage may be different from yours. We do mostly decision support systems and huge complex batch data loads. One would think this would be the place for PL/SQL. It used to be. In the mid-90's I could almost beat C programs with PL/SQL. I still strongly believe in using SQL (and even quite complex SQL occasionally) and views for performance and good design issues (as mentioned in Fowler's paper). My final reason for moving away from the PL/SQL that I love so dearly is that I've become infected by Extreme Programming (more specifically Test-Driven Development). I've used utPLSQL for two years now and the difference between the environments when doing TDD on PL/SQL versus Java is very dramatic. I really like utPLSQL and I highly recommend it to anybody sticking with PL/SQL and desiring to do TDD with it. However, the testability of PL/SQL is just harder than Java. This is probably due to a combination of the development environments and the OO differences. So, my reasons for moving away from PL/SQL (note: I've been talking about Oracle's PL/SQL specifically) are: --MikeCorum

I am not in favor of placing complicated "business logic" in Stored Procedures, however Stored Procedures are an important aspect of any database and I believe should almost always be used as a means of controlling how data is placed into a database. T-SQL and PL/SQL are much more powerful languages than they usually get credit for, but are typically overlooked as a database gatekeeper for all data entry and retrieval either because of developers having a greater skill set in the application's main language, from a misunderstanding that a database is an application in and of itself, or from a fear of stored procs which they can't actually explain but "know" is wrong because that's what they've always heard. Ok, so why you are not in favor of placing complicated "business logic" in Stored Procedures then? --CosminApreutesei

Here's why stored procedures ought to be used more often than they are: The problem I see with people avoiding stored procedures is that they don't view the database as separate from the applications that store the data. Granted, sometimes there will only be one application that touches a database and that application could contain all the business logic and data rules and would probably be fine. But, for an enterprise level application, that's just not always the case. The database really should be treated as a separate application supporting its own rules and structure and not relying on another application to enforce data storage logic.

-- mattmc3

Rigid Parameters

The biggest problem with stored procedures is that they often result in one change having to be made in 2 more places. For example, if you add a new column, you cannot just change the spot that needs the new column, but you must also must change the stored procedure, and ponder what to do with other calls that don't need the new parameter. Perhaps if SP's could accept a dynamic map or named parameters instead of just using "flat" positional interfaces, managing changes could be simpler in some cases.

-- top

This is quite possible with PL/SQL. You can name specific parameters, and you can provide default values for parameters.

If you consider your database to be responsible for data, not function, and understand that application code is poor at dealing with transactions etc, there is a good architectural breakpoint between SP's and SQL in code.

A good rule of thumb is that if you need loops or conditionals then you have gone beyond the competence of the database. Similarly, if you find you need to write a bunch of sequential SQL statements in a code routine, then you probably need an SP. --RichardHenderson.

Why should the amount of query code in an app be a determinant for makeing an SP? I don't feel it is productive to have app-specific business logic split between the database and the app. That requires more back-and-forth editing and parameter management. And conditionals are often part of QueryAndLoop how often? why not so often in the sql "where" clause? --CosminApreutesei. A query loop often looks something like this:

  rs = query("select * from foo where...")
  while (row = getNext(rs)) {
	if (row.blah == zarg) {...}
	if (row.fob==znix or row.ferg==7) {...}


Well, try splitting the query into an sp, and processing the result-set in your app code. You might like it. Refactoring the database becomes loads easier and can be done by a dba. Programmers only need to deal with a simple functional interface. Everyone is happy. It isn't about 'the amount', its about separating concerns. YMMV of course. ---RIH.

For CategoryEnterpriseComputingConcerns

Most enterprise computing use database servers from IbmCorporation or MicrosoftCorporation. And most shops still have a heavy dependence on CobolLanguage.

The future seems to suggest that in one instance, there is move towards having DotNet language capabilities within the database environment, whereas the other vendor has already included extensive Java support within stored procedures.

Given the above scenario, are there further support for / or against the increased use of BusinessLogicInStoredProcedures? --dl

I feel that file systems will eventually be replaced by post-hierarchical databases. Thus, what is "in" the database may not mean what it means now. In that scenario we may end up talking about what is "registered with the database" as a SP rather than code being "in" the database since everything will be in the database. Related: FileTreesToManageCodeDiscussion. --top
I always put business logic in SPs, views and triggers, for many reasons: Anyway, with such popular misuses and under-uses of RDBMS'es such as O/R mapping and handling datasets imperatively, it's no wonder why sps have such low popularity. Any arguments for *not* using them (I *did* read the above)? -- CosminApreutesei.

SQL is a terrible language. The programming world has advanced quite a bit over the decades, to the point that your IDE (plus extensions) can give you auto-completion with programmer documentation (e.g., javadoc, .NET xml comments, the like of which I almost never see in SQL) AND make judgments about possible bugs. Object-oriented and functional programming are good paradigms invented for a reason, but all of that development has not happened in the SQL sphere (e.g., no polymorphism, no type inference). SQL is stuck in the 60s.

The arguments for putting logic in SQL as a first approach are invalid. We don't have different languages and apps hitting a database. We have one language (Java or C#, depending on your shop). We have one monster enterprise app. Security is defined at the app level, not the d/b level. Your common API is defined by a web service. Performance is pretty good if you minimize the number of database trips you have to make (obviously, you have to strike a balance between too much data returned by the query and too many queries for very specific data).

There are good reasons to move minimal logic into a sproc if all other (reasonable) optimizations fail, but I don't believe it's good architecture at initial design time.

-- JohnLusk, 12 Mar 2013

"We don't have different languages and apps hitting a database. We have one language (Java or C#, depending on your shop). We have one monster enterprise app."

That's true in some shops. In others, there are twenty years of legacy apps, plus current apps (where the definition of "current" varies from department to department) and experimental development across ten or more application languages, all hammering the database at once. Security is defined at the database level, because you dare not leave it to being defined (or not) elsewhere.
See Also: DatabaseBestPractices StoredProcedures StoredProceduresAreEvil BusinessRulesMetabase

View edit of March 12, 2013 or FindPage with title or text search