Sinsing interpreting the merits and demerits of stored procedures in MySQL

Our commonly used SQL statements need to be compiled first and then executed, while stored procedures are a set of SQL statements for specific functions that are compiled directly into the database, and the user can invoke the stored procedure by specifying the name of the stored procedure.

The advantages of the stored procedure include the following points:

1th, performance improvement. This is relative to the non-applicable stored procedure, because the stored procedure is compiled at the time of creation, and then each call will not be compiled again, which is a bit more than two points compared to the case where each call in a traditional SQL statement needs to be compiled.

2nd, re-usability is strong. The stored procedure uses the name to be used, which is the legendary "write once, call casually". This not only improves reusability, but also reduces the chance of error and speeds up development, which can be said to be a very good thing.

3rd, reduce network traffic. This is generally not the case for small data volumes, so when the amount of data is large, we will find that the use of stored procedures is less than the use of SQL statements, so it will reduce the amount of data transferred.

4th, improve security. Because stored procedures can also use permissions control, and parameterized stored procedures can prevent SQL injection attacks, but also to a certain extent, to ensure security.

5th, flexibility is enhanced. Because a stored procedure can be written using a Process Control statement, it has the flexibility to execute different SQL statements based on the actual situation, rather than simply executing the commands. And the stored procedure can also modify its logic and other parts do not change, that is, the structure of our table has changed, we just need to modify the corresponding stored procedures, our Java or PHP programs do not need to change.

6th, when the business complex, the storage process will reduce the workload, for what, the reason is very simple, if we do not apply the stored procedures, then we will be taken out of the database data, and then after the calculation, and then into the database, the cost is quite large, This overhead includes our Java or PHP program to connect to the database to get a result set, and so on, if we use a stored procedure, then there is not so much, directly within the MySQL is done.

Well, it says the advantages of the stored procedure, of course, the stored procedure must have its drawbacks, and here are some of its drawbacks:

1th, the workload increased. This is not to say that we have to do what the program does to make MySQL bad, but MySQL itself does not have a very decent IDE to develop our stored procedures, we often need handwriting, this will be more troublesome, and the debugging of stored procedures is a problem, there is no very decent debugging tools.

2nd, the advantages are not obvious. On the run speed, for most of the statement caches, the time spent compiling SQL is not very large, but the execution of stored procedures also requires checking permissions and other overhead, so for very simple SQL, stored procedures do not have a great advantage.

3rd, the redundancy function. For Web applications, the users we connect to the database are often the same and do not require much security, so security checks look good, and in fact, the benefits are superfluous.

4th, small programs are completely useless. For small Web applications, the use of it is even less, but it can slow down the development process.

5th, for operational dimensions. When our program to replace the database, its portability relative to the non-applicable stored procedures are more complex, for maintenance, because it is on the DB side, and therefore better than the server side of the program to maintain some.

Sinsing interpreting the merits and demerits of stored procedures in MySQL