Programmers will get through — why are you still using stored procedures?

Time:2019-8-12

Programmers will get through -- why are you still using stored procedures?

stored procedure

Stored Procedure is a set of SQL statements stored in a large database system for specific functions. It is permanently valid after a compilation. Users execute it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). Stored procedure is an important object in database.

advantage
  1. Reduce the amount of information that a program can transfer when calling DB (actually only Request is reduced)
  2. Stored procedures are pre-optimized and pre-compiled, saving time for each run of compilation, so it is generally considered that the performance of stored procedures is better than that of SQL statements.
  3. The caller can hide the complexity of the database and encapsulate the process of data assembly.
  4. Parametric stored procedures can prevent SQL injection attacks, and Grant, Deny, and Revoke permissions can be applied to stored procedures.
  5. If data and business code personnel are separated in business development, business personnel can call stored procedures directly without concern for data, and more oriented to the layered development design concept.
Inferiority
  1. Stored procedures, a “one-time optimization, multiple-use” strategy, saves compilation time per execution, but it also leads to a fatal disadvantage: incorrect execution plans may be used.
  2. Storage procedures are difficult to debug. Although some DBs provide debugging functions, general accounts simply do not have that kind of permission. Moreover, online databases can not give you debugging permission. Further, even if debugging can be done, the debugging effect is much worse than the debugging effect of the program.
  3. Portability is poor, and stored procedures will basically stop when it comes to switching data types.
  4. If the business data model changes, the stored procedures must be changed along with the business code. If it is a large project, such changes are unprecedented and fatal.

Stored procedures are not recommended

The advantages and disadvantages of the above stored procedures, you can easily find that the network, on the surface, the advantages of stored procedures are still many, which explains why many older generations of programmers like to write stored procedures. However, with the increasingly complex business in the software industry, stored procedures are now somewhat powerless in the face of complex business.

Storage procedure is not recommended for dishes in business. Please leave a message for refutation.

  1. It is true that using stored procedures to manipulate data in network data transmission is much less than using SQL statements directly, but this is usually not the bottleneck of operating data system performance. In the process of manipulating data, assuming 100 milliseconds, using stored procedures to save 0.5 milliseconds of data transmission time (even 5 milliseconds), I I think this time can be neglected.
  2. Stored procedures are optimized only once, which is sometimes a flaw. Sometimes with the increase of data volume or the change of data structure, the original execution plan of stored procedure selection may not be optimal, so manual intervention or recompilation is needed at this time, and when execution plan is not the optimal balance point, which can not be known beforehand, which leads to the existence of Some applications suddenly slow down and the programmer is in a state of stupidity.
  3. Stored procedures can hide the details of the database from the caller, but how much is the case when the business coder and database designer are two teams? If they are two teams, the business needs two teams to understand and communicate. I think the cost of communication must be high, and the differences are easier. Generation.

Vegetables think that databases should do what they are best at: store-related. I’ve seen more than once how business is written in stored procedures. The level of program code is really a thin layer of anemia, which is the transmission of data. I don’t agree with this writing, because I’ve taken over such a program. It’s not the business that bothers me, but watching thousands of lines of stored procedures familiar with the business. The key is that there’s no debugging authority (let alone online debugging).

The design of a business system often requires you to separate from the database level and concentrate on the design of business models, reflecting business logic at the program level, rather than giving business logic to managers at the data level. I checked a “Bug” the other day: the stored procedure is a list string whose input parameter is a primary key ID and whose length is actually nvarchar (max). Its main function is to query data according to the ID list. What I want to say is that even if you are the length of max, there is also the possibility of super-long, because what parameters are transmitted by the business side and what length is beyond your DB’s control, so this kind of business must be processed in the program instead of being thrown to DB by chance.

If you have a high-performance stored procedure mentality, then I think you are going astray. Vegetable dishes think that stored procedure is never the key to improve performance. On the contrary, the architecture of the system, the design of cache and the consistency of data are the key issues of the system.

Stored procedures are usually a solution, but usually they are not the only solution. Make sure they are the right choice before choosing stored procedures as a solution.

Show me the last wave of stored procedures.

Programmers will get through -- why are you still using stored procedures?

Programmers will get through -- why are you still using stored procedures?


Add attention, view more exquisite versions, harvest more exciting

Programmers will get through -- why are you still using stored procedures?

Recommended Today

Implementation of PHP Facades

Example <?php class RealRoute{ public function get(){ Echo’Get me’; } } class Facade{ public static $resolvedInstance; public static $app; public static function __callStatic($method,$args){ $instance = static::getFacadeRoot(); if(!$instance){ throw new RuntimeException(‘A facade root has not been set.’); } return $instance->$method(…$args); } // Get the Facade root object public static function getFacadeRoot() { return static::resolveFacadeInstance(static::getFacadeAccessor()); } protected […]