MySQL version 5.0 is starting to support stored procedures. Stored Procedure is a database object that complex programs are Stored in a database for external applications to call. A stored procedure is a set of SQL statements designed to perform a specific function. It is created by compilation and stored in a database. Users can invoke and execute the stored procedure by specifying its name and (optional) parameters.

Stored procedures can effectively improve the reuse rate of SQL statements, and can put a related set of SQL into the stored procedures, thus avoiding the application’s multiple queries to the MySQL server connection delay and occupy network resources. The following is an example of a stored procedure that passes in an ID to delete the student with the specified ID, along with the student information in the extended table. In this way, the associated data can be processed without requiring the application to do two SQL operations.

DROP PROCEDURE IF EXISTS delete_student_by_id;

delimiter $$

CREATE PROCEDURE delete_student_by_id(IN p_id INT)
BEGIN
	DELETE FROM t_students
  WHERE id = p_id;
      
  DELETE FROM t_students_info
  WHERE student_id = p_id;
END
$$
    
delimiter ;
Copy the code

In general, stored procedures have the following advantages:

  • It runs directly at the database layer, which reduces the occupation of network bandwidth and the delay of query task execution.
  • Improved code reusability and maintainability, business rules can be aggregated, consistency enhanced, and security improved.
  • Provides security advantages and elegant access controls. A typical example is the transfer stored procedure in a bank. The stored procedure completes the transfer in a transaction and records a complete operation log for subsequent audit. Access can be done through stored procedures without having to weigh the tables involved.
  • The server caches the execution of the stored procedure to reduce the burden of repeated execution.
  • Stored procedures are stored on the server side, so they are more maintainable for deployment, backup, and maintenance of service sheets.
  • You can separate the work of the application developer from that of the database developer, so you can let the database guy write the stored procedures and avoid the problems of some application developers who are not very good at WRITING SQL.

Of course, there are pros and cons, and there are some pitfalls to stored procedures:

  • MySQL does not provide good development and debugging tools, so debugging stored procedures is relatively difficult.
  • The SQL language itself is not as efficient as the application programming language and is relatively rudimentary. Therefore, it is difficult to deal with complex business.
  • Stored procedures can also add complexity to application deployment by deploying stored procedures as well as application code and database tables.
  • The execution plan cache for each connected stored procedure is independent. If there are many connections calling the same stored procedure, repeated caching can be a waste of resources.
  • Stored procedures move runmatches to the database server, which makes database server capacity expansion more difficult and expensive than application server capacity expansion.
  • The resources consumed by stored procedures are difficult to control, and a bug can bring the server down.
  • The stored procedure code is difficult to read, and it is difficult to parse the slow query log if the stored procedure is simply called in the form of CALL XYZ(‘A’). This requires finding the code of the stored procedure and examining the statements inside.
  • For statement-level binlog or replication, there are a number of pitfalls to using stored procedures that can make them unusable — unless they are rigorously checked to rule out potential problems.

​

Therefore, in general, you need to keep stored procedures small and concise to avoid the pitfalls described above. Of course, there are certain operations where stored procedures run faster, especially when loops are used to complete multiple small queries in stored procedures. If the query is small enough, parsing SQL statements and network traffic becomes a significant contributor to the workload. This is where the advantages of stored procedures come into focus. Take the following stored procedure code for example:

DROP PROCEDURE IF EXISTS insert_many_rows;

delemiter //

CREATE PROCEDURE insert_many_rows(IN loops INT)
BEGIN
	DECLARE v1 INT;
  SET v1=loops;
  WHILE v1 > 0 DO
  	INSERT INTO test_table values(NULL.0.'aaaaaaaaaaaabbbbbbbbbb'.'aaaaaaaaaaaabbbbbbbbbb');
    SET v1=v1- 1;
  END WHILE;
END
//

delemiter ;
	
Copy the code

By comparing the same functionality with an application, you can see a more than two-fold improvement in performance using stored procedures and a three-fold improvement when compared to using MySQL agents.

Conclusion: The stored procedure is not used much at present, but for some stable services, if the network requests between the database server and the database server are too many or occupy a large amount of network bandwidth, you can consider using the stored procedure to optimize the performance and improve the response speed. However, the stored procedure must be verified repeatedly to avoid unnecessary troubleshooting time due to unexpected errors.