Business Logic implementation methods: the stored procedure is good or bad?

Before domestic and foreign are on the storage process of good and bad for a heated debate, the following is a link to some research and discussion, interested can go to understand: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspxhttp://www.oreillynet.com/databases/blog/2007/03/_so_are_database_stored_proced.htmlhttp://andrewonedegree.wordpress.com/2010/03/08/are-stored-procedures-good-or-bad-and-when-to-use-them/ (recommended) http://www.cnblogs.com/cxd4321/archive/2008/03/21/1115881.html

 

This article will not discuss this part. More importantly, it will emphasize when stored procedures are used and when they should be encapsulated in the business class. General principles: 1. business logic requires complex judgment and processing. Use business implementation. 2. Small Data volumes (less than 200 data rows) processing and judgment using business Implementation 3. Involving batch data processing using stored procedures (such as batch merging of Department staff and batch addition of job change information sub-tables for each person) 4. Logic involving the statistical analysis part is implemented through the stored procedure. 5. If you need to provide the data layer interface part through the stored procedure, it is not recommended to open the data table directly, at least it should be opened in the form of a view (this is rare, usually used between internal systems, it is recommended to use less) 6. Business implementation that requires horizontal scaling (for example, the user authentication table only supports vertical scaling, but only the increase of records; the possible increase in the number of enterprises is either horizontal expansion or horizontal expansion of the number of modules, involving the addition of data tables)

of course, in most cases, You need to analyze the specific situation, the following is an analysis of my current project: 1. Only Oracle databases are supported. That is, the database of Oracle10g and later versions. Because it is a SAAS service, the customer does not need to care about the database. Therefore, the product does not meet the needs of multiple database environments. 2. Low module coupling. For example, user authentication, registration, and application of specific business logic modules are physically separated. The database storage and services provided are distributed on different database servers or instances. 3. high scalability requirements. Multiple scaling methods are required (for example, the high concurrency problem can be solved by adding servers horizontally in deployment, business Requirements can adapt to the increasing functional modules to meet the needs of enterprise management ). 4. High reliability requirements. uninterrupted service is required.

conclusion: the execution of the business logic layer deployed on the Web server has great advantages in horizontal scalability. However, there are still some problems in some aspects, and it is also necessary to test the level of developers and the quality of Code , adaptability to demand changes and timeliness of response. Oracle itself provides some database load solutions. Although I am not an oracledba, Oracle's database achievements are remarkable. As far as I know, when I find there is a bottleneck in the database, in addition to optimizing the execution efficiency of some SQL statements, the first thing to do is the read/write splitting of the database, this greatly reduces the pressure on Io resources. There should be some solutions in other aspects, at least Oracle databases in large applications such as telecommunications, power supply, and taxation did not have a major problem (maybe DBAs have solved it ). In my experience, most of the problems are caused by the Program . On the one hand, it is difficult to quickly adapt to demand changes, on the other hand, it is difficult to locate the problem. The reason for the latter is that the unit test is not completed, which makes it difficult to locate the problem and leads to too much trouble in test tracking.