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 http://www.martinfowler.com/articles/dblogic.html 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:

Putting business logic into PL/SQL is difficult and suffers from lack of OO features - we really never did it right.
The performance advantage of PL/SQL have gone away compared to Java/JDBC. (complex SQL & Views are still useful)
Testability of PL/SQL suffers compared to Java (slower, harder, etc...)

--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:

Centralized access: if many applications access a database (front end system, reporting system, auditing system, the often forgotten query tool, etc.) then they all access the database the same way and many of the changes to the schema propagate to all systems seamlessly.
Secured data access: Only people with permissions to access the data can access it.
- Same thing can be achieved with tables (most RDBMS allow to restrict CRUD to a Table), StoredProcedures only give you a false sense of security (unless you always receive a security token as one of the parameters for you stored procedures... And I have never seen that in the RealWorld, not even from applications built by people that believe that StoredProcedures increase security)
Controlled data access: If your database allows any old SQL, then the rules for how you designed your data to be stored can easily be violated. Stored procs hide the schema from the application and allow only the functionality provided.
Enforces data rules: Data rules are different than business logic. Data rules are specific to the database design and how data is stored and retrieved. Business logic is the process by which the data is assembled before it is stored. Sometimes data that follows all the business logic can violate the data rules, which is why data rules are often incorporated into the business logic as well as existing in a stored procedure.
- Yes, but error reporting is generally bad with StoredProcedures, so you end up with cryptic messages like "Error 3446" instead of "You can not transfer money from account {X} because it is empty", that why most ObjectWeenies? prefer to do that kind of validation in their persistent objects.
Provides the easiest way of expressing SQL: Many modern programming languages like Java and C# have a difficult time expressing complicated SQL strings because they lack a facility to switch context from typical string input like "Hello World!" to strings containing complicated interpreted languages like SQL. Stored procs provide the most natural and easy way of expressing multiple complicated SQL statements together.
- Unless you need to do pagination or interactive sorting by single or multiple columns... then you need to reinvent ORDER BY and do crazy stuff to allow pagination (stored procedures in general don't make it easy to add pagination to queries without violating OnceAndOnlyOnce), stored procedures are never as good as DynamicSql for anything interactive.
Almost always provides faster data storage/retrieval than inline SQL
- Only if you are doing something that is not interactive (batch processing)

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) {...}
...
  }

--top

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:

application-side loops = table scan + useless network traffic, and I'm yet to find a practical case when they can't be *easily* avoided -- OK, for single select queries you don't need a sp, but even then a view is welcome;
deployments get easy, especially in environments when you have to rebuild your 100MB bloatware ERP and upload it to 200 clients for a critical bug-fix;
you get a common business API for many apps (like web services) which sometimes you even develop and test independently from those apps;
database independence is a joke, unless you want the lowest common denominator, which in the case of dbs is very low; so I don't mind mixing business logic in both sp and app code-- not for the sake of separation that is;

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.

Just because IDE's currently don't handle SQL well does not mean they can't. If you can show that it's theoretically impossible for IDE's to assist with SQL, please do. (Newer versions of ToadTool allegedly do, but I haven't tested them.)

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.