Arguments for / against business logic in stored procedures

Question

What are the pros and cons of business logic in stored procedures?

Answer 1

Against Stored Procedures: Business Logic in the Programming Space

I put a lot of emphasis on the power of expression, and I don't find the SQL space so expressive. Use the best tools you have for the most appropriate tasks. Interaction with higher order logic and concepts is best done at the highest level. Consequently, storage and mass data management is best performed at the server level, possibly in stored procedures.

But it depends. If you have several applications that interact with the same storage engine, and you want to make sure that it supports integrity and workflow, you must offload all the logic to the database server. Or get ready to manage concurrent development across multiple applications.

Answer 2

I am totally against it. One of the biggest reasons is the first reason the ear is voiced - it lives in one place. You cannot easily integrate it into the source code. It is almost impossible for two developers to work on a stored program at the same time.

My other main complaint is that SQL is simply not very good at representing complex logic. You do not have a concept of scope, the code is usually copied because there are fewer possibilities for reusing the code (unlike the OO language).

You must provide developers with access to the database for its development. In many organizations, I worked on data that people are in a different world than developers, with different permissions, etc. Keeping developers from the database in these cases will be more difficult.

Answer 3

I am from the school of thought, which says as long as the business logic:

• lives in one place
• where is it correctly documented
• proper access is provided through services that may be loosely coupled
• via published abstract interface

I don’t care if the logic works in the stored procedure, in the middle level of J2EE, in the expert clip system or anywhere. No matter where you store our business logic, the “law of poverty” will guarantee that someone will say that it was a wrong idea, because component / repository X should be replaced with technology / method Y.

Answer 4

Some thoughts: note that this is a Java-oriented answer, but its main part of my last (last 10 years) experience

(1) Parallel development by a (large) development team. If the application is complex enough so that each developer cannot create his own private version of the database (with the participation of links / links to data, etc.), it is very difficult to have a whole TEAM of developers who all work on the same set of PL-SQL packages ( for example) stored in a common DEVL DB database? Then your stuck (my experience) with working in a database with the wrong procedures / code mismatch with tables, as people make changes ...

As a Java architect, I think it’s much easier to have each developer on a private JBoss instance on the desktop and easily work on their own set of features and integrate at their own pace without affecting everyone else ... which brings me ...

(2) Tools for continuous integration Despite the fact that there are several similar “concepts” in the world of databases, my experience has shown me that combos (I choose my best best of its kind here):

• mvn - build system
• junit - automatic module testing
• nexus - repo manager (manages artifact life cycle versions, snapshots and releases)
• Hudson server - ci build
• sonar - static analysis / code coverage reports / ALOT more

Starting a large project using all of the above (free tools) allows you to provide a simple / easy way to deliver XP to the masses and ensure quality control over all IT staff. Oracle / PL-SQL Does Not Have Matching Toolkits

(3) tools / libraries / etc ... Java has access to an amazing set of services that other platforms cannot touch - some are free and some are not. even basic ones like log4j (yes, they have it for PL / SQL, but pulease ... it's not the same thing) allows things like allowing developers to create customizable logs that can be changed on the fly (perfect suitable for dubugging) Automated API documentation (via javadoc). Automated unit test coverage reports. Incredible IDEs (Eclipse) with built-in debuggers / autodeploy for application servers. API for interacting with each type of service under the sun, open source for FREE and 100% support for each provider

(4) reuse of services. what someone commented is true. If you have heavy data-driven business rules, you can argue that they must live at the database level. What for? to prevent the presence of intermediate level (s), all of which should duplicate this logic.

But the same can be said for business rules that are not data-driven or complex enough that OO is a more natural choice . If you adhere to all business logic in the database, then they are available only through the database.

• What if you want the check to be performed at the client or mid-level level and a round trip to the database is maintained?
• What if you want to cache read-only data at an average level (for performance) and follow business rules against cached data?
• What if you have a mid-range service that does not require access to the database, or you have a client that can provide its own data?
• What should I do if part of the data-dependent business rules needs access to external services? Then you end up with fragmented business logic that looks like this:

I am

retCode = validateSomeDate(date); if (retCode == 1) then evaluateIfCustomerGetsEmail(...)//probably more stored proc invocations here... sendEmailMsg(....) else if (retCode == 2) then performOtherBizLogicStuf(...) //again, may need data, may not need data triggerExternalsystemToDoSomething(...) //may not be accessible via PL/SQL fi 

I am sure that we all saw the systems written as above, and we had to debug them at 2 in the morning. It is extremely difficult to get a consistent sense of the complex process when the business logic is fragmented between levels, and in some cases it cannot be supported.

Answer 5

"You cannot easily integrate it into a control source." - if you put the code that creates the stored proc in a script, a controlled version, this objection will disappear. If you stick with the flexible ideas of the Scott Ambler database, what exactly should you do.

Not all developers are good data designers. I can think of terrible schemes created by developers who believed that mastering SQL knowledge made them database experts. I think it’s very important that developers work with database administrators and data designers.

If only one application uses a database, I would say that business logic can appear at an average level. If many applications use the database, it might be better to place it in the database.

SOA offers a middle ground: services own their data. Only the service has access to data; getting data means going through a service. In this case, you can set the rules anywhere.

Applications come and go, but the data remains.

Answer 6

Another reason NOT to store business logic in sprocs is the limited ability to scale the database. This is a very common situation when your database is your bottleneck, so it is recommended to take as much database load as possible.

Answer 7

My few observations:

In favor of stored procedures:

• after some time of the project’s life, in most cases the bottleneck is the database, not the web server, and stored procedures are much faster.

• using sql profiler with requested ORM queries is very difficult; it is easy with stored procedures.

• you can deploy a fix for a stored procedure instantly, without a service window

• for performance, it’s easier to optimize a stored procedure than ORM code

• you can have many applications using the same db / stored procedures

• any complex data scenario is a vote on stored procedures.

In favor of the / ORM application:

• you can use the code repository (with saved processes it is still possible, but expensive)

• Java / C # is the best tool for expressing business logic

• java / C # is easier to debug (except for dynamically generated SQL ORM code)

• independence of the database mechanism (however, it is unlikely that the project will change the database to another)

• ORM provides a data model that is easy to use.

In my opinion: for large projects - go for stored procedures, for the rest - the application / ORM will work fine.

At the end of the day, the only thing that matters is the database.

Answer 8

Business logic must be encapsulated in one place. We can guarantee that logic always starts and starts sequentially. Using the classes in which all activities related to the entity in the database should be performed, we can guarantee the correct operation of the entire check. There is one place for this code, and any project developer can easily open this class and see the logic (since the documentation can and becomes outdated, the code is the only reliable form of documentation).

This is difficult to do with stored procedures. You can have more than one sproc associated with the same table. Combining several sprocs together so that the logic is in only one becomes cumbersome. This is a blow. How do you define "What are the business rules associated with entity X" in the database? Have fun finding thousands of sprocs trying to keep track of this.

The second number is that you connect your business logic with the persistence mechanism. You cannot store all your data in the same database, or some of them may be in XML, etc. This type of inconsistency is difficult for the developer.

Validation is difficult to perform if the logic is only in the database. Do you really call sproc to check each field in the data entry form? Validation rules and business logic are close relatives. This logic must be executed in the same place!

Answer 9

+: SQL server sometimes optimizes code

+: you are forced to pass parameters that limit the problems of SQL injection

-: Your code depends on one database (some dbs don't even have SP)

-: To change the code, you must connect to the database

-: Logic is not well organized

Personally, I am opposed, but I had to use it once on a really busy website. Using SP in MS SQL brought huge benefits, but as soon as I implemented caching, these advantages were not so great.

Answer 10

There is a saying ...

When you have a hammer, everything looks like a nail.

In my humble opinion, there is not a single answer that would correspond to all circumstances. It seems to me that many people simply assume that placing Business Logic in the database is always wrong.

A lot of work has been done to make transaction processing, especially bulk operations, very efficient when done on the database side. In addition, database code management has improved significantly since most opinions regarding databases have been generated.

I think it is wrong to treat the database server as the only persistence level. If your processing actions are most effective when they are executed on the database server, then do them there.

If not, do them elsewhere.

It all comes down to what works best for the application you are currently working on, the team you are working with, and the client who hired you.

This is just my 2 cents.

Answer 11

You can unit test business logic when it is at the level of business logic. If he fully shares the operations of perseverance, you can mock that you tested only BL. A stored procedure is much more difficult to maintain / debug / unit test than, for example, linq and C #.

Answer 12

DBMS! = Application Server

• Functional programming (DB stored procedure) versus OOP. For large programs, OOP is standard.
• IDE - eclipse, intellij, netbeans with all plugins for debugging, testing and analysis works only with real programming languages. Static code tools .
• Version control if you get it for PLSQL & co. fine. If you get a “sync presentation" right from you, the IDE is really lucky.
• Scale your system. For the database system - hell. You need expensive equipment for other "nodes". Replication. And probably a license for each node. Do not forget that you are still in "functional programming", and efforts to understand and maintain such systems are much greater.
• You are stuck in your database, try changing or adding a new one from another company.
• And so on...

Stored procedure for business logic today is bad practice. Use a 3-tier architecture instead.

Answer 13

There are different types of "business logic." Think about breaking it up based on how it relates to other levels or services. Here are some basic rules from an MVC perspective:

a) In a database (stored procedure), if it is mainly related to data, and can be performed using joins and relatively simple WHERE and SELECT statements.

b) In the controller, if it is mainly associated with routing or scheduling; that is, a larger control over the flow of the user interface in terms of screen or resource selection.

c) In a model or presentation model, if it involves complex or complex calculations and / or conventions.

d) In a view (for example, Razor), if it is primarily a display problem, for example, "friendly" reformatting and relatively easy to implement. (If this is difficult, consider placing it in a view model.)

Answer 14

@Nick "I am totally against this. One of the main reasons is the first reason that she is being cared for because she lives in one place. You cannot easily integrate it into the original control. While working on a stored procedure" .

Not that I argued about putting business logic on stored procedures (all the way around). But these reasons that you put forward do not make any sense. A stored procedure is just an sql / DDL artifact that can be stored in the original control, as well as a deployment artifact (i.e. something passed to dba for deployment, just like you transfer your war / ear artifacts to your personal IT capabilities / deployments) One or more developers can work with the same stored procedure outside the source code in the same way as with simple old source code - through branching, versioning, and merging.

Now, if the only copy of the stored procedure (and the packages that contain them) exists only in the database, then obviously you cannot control this with the source control (and all the problems associated with this). However, this is not a stored procedure problem, but an inappropriateness problem regarding how to use this code. This is tantamount to inappropriateness because you only have one copy of your source code living in the workplace.

I worked on systems with a huge amount of code, both in Java and in PLSQL / DDL, and they were all versions in pure space. They were all considered as source code that would be compiled and deployed with a rigorous process, with different teams working on them. There have never been problems like what you describe.

There are context-sensitive reasons for not placing business logic in stored procedures, but they are not valid.

Answer 15

Performance will be greatly improved by moving logic to stored procs, especially if explicit transactions are involved.

In my experience, application developers are not very good at writing optimized database code and are not inclined to think about concurrency or performance issues.
If the business logic is stored at the application level, you usually have to transfer large amounts of data (often in many rounds) over the network, duplicate them in memory on the database server and at least once on the application server, and download Take turns in the application while you keep an open transaction. Then, application developers complain that the database is slow and locked.

If you put logic in the database wherever possible, you usually just pass a few parameters over the network, transactions are not executed while you are waiting for network resources, and all this seems to be blurry. Databases must, of course, go in source control, just like any other source code .. there are many tools for this.

Answer 16

High budget projects: if you keep your latest data in memory and periodically save changes to disk, you will need to process the business logic except for the db server. use case: serve data from ram, complex caching mechanisms.

Low-budget projects: if you save up-to-date data to disk, and your presentation depends on reading disks, processing business logic in stored procedures can save your development time. usage: using data from disk

Answer 17

My rule of thumb (as soon as I realized what it was when I thought about the question) is that stored procedures must contain code that ensures the integrity of the data in the database, whether the stored procedure prohibits certain insertion, deletion or modification of data, or makes other changes necessary for data consistency. Business logic, especially logic that cannot be implemented in multiple set-based operations, must be implemented elsewhere. Databases are not applications. Databases should be the ultimate authority for relationships and restrictions, even if business rules are also implemented elsewhere, for example, to provide feedback in user interface code that reduces postbacks from a web server or eliminates unnecessary hits on a busy server. One can argue about whether "data consistency" includes the result of complex processing of complex business rules, but I think that usually becomes clear after understanding the context. Not all business rules are implemented as relationships or data restrictions. Not all operations in a stored procedure are faster than code executed in a separate process, even in a process running on a separate machine on the network. I recently conducted a demo demonstrating that many operations in SSIS, such as (INSERT INTO () SELECT FROM), are faster in SSIS running over a network on a separate computer than in a stored procedure (which also inserts the results into the database). through the network). This is an almost unbelievable result (where SSIS is faster than raw SQL statements), and demonstrates that finding the best optimization for any performance problems comes from reality (testing), and not from logic based on only a few concepts. (We still have to decide what to test based on practical experience.) (SSIS was faster due to the automatic implementation of multithreading and pipelines, using BULK INSERT even where it was not specified in the raw SQL statement, and sending packets inserts in one thread when creating additional BULK INSERTs in other threads, in which case it was about twice as fast as raw SQL statements.) When I used SQL Server to teach programming and courses, PowerBuilder users seemed to ate the expression "the Native" drivers provide the fastest access to data, "hardened in their own language", and although this can be justified by additional (unrecognized by them) explanations, the thought of it is misleading.

Answer 18

With all this ideology of microservices and microbusiness components, we are far ahead of the question of where to put our business logic correctly.

Although the ideology is well accepted, we still have the temptation and some cases where we ended up putting some decision making and business logic into a database. It is worth visiting the detailed answer about why this should not be done, but at the macro level, I would advise you to think about one good reason why it should remain in the stored procedure, and not at the application level.

The presence of such a counter-thinking process will always guide the adoption of the right decision. Ask these questions before deciding:

1. What if in the future we change our database from SQL to Mongo?
2. What if you want to provide an API that takes data (which the database provides) and applies this business logic from above?
3. Unit testing this business logic?
4. SDLC steps if we make changes to the terms of this business logic?
5. What if we need another user input to make decisions in this business logic?
6. What if high computing power is required (not data processing), and we want to start a separate process (or platform)?

In all cases, it would be natural to choose not to include any logic (other than simply extracting data) in the business logic.