Differences between database (MySQL) stored procedures and transactions

Differences between database (MySQL) stored procedures and transactions

This article briefly introduces the differences between stored procedures and transactions in databases.

Stored Procedure:

A group of SQL statement sets (or custom database operation command sets) for specific functions can be called simply based on input parameters (or none,
The function is more complex than a single SQL statement. It is stored on the database server and needs to be compiled once and then used again.
It mainly controls the stored procedure.

Advantages:

1. Fast execution speed. Especially for complicated logic, this reduces the consumption of network traffic. In addition, it is important that the stored procedure only compiles during creation, in the future, you do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled once every time you execute it. Therefore, using the stored procedure can speed up database execution ..

2. improve work efficiency. The write procedure is simple and uses the stored procedure call class. It takes only 1-2 lines of code to call any stored procedure.

3. Standardized program design, easy upgrade and maintenance.

4. Improve system security. You can set that only a user has the right to use a specified stored procedure.

Projects with a small amount of data or irrelevant to money can operate normally without using stored procedures. The mysql stored procedure remains to be tested. For a formal project, we recommend that you use SQL server or oracle stored procedures. When dealing with data, the process will be much faster than the program.

Disadvantages:

Some functions of the program are moved to the database, which destroys the three-layer structure design of CVM.

Transaction:

A series of data change operations constitute a whole. These operations include stored procedures, change statements, and other operations. Once an operation in the transaction fails or the user stops, the user can revoke all the operations in the transaction body and return the status before the start of the transaction. The operations in a transaction are either completed or not done. This ensures data integrity.

Transactions can have stored procedures or transactions.

When is the storage process suitable?

1. It is appropriate to use a stored procedure when a business processes multiple tables at the same time.

2. complex data processing uses stored procedures, such as some report processing.

3. Multi-condition multi-table joint query and paging processing.

When is the transaction more suitable?

Each time you use a transaction, it will take up a certain amount of overhead. In addition, the transaction may lock some table rows. Therefore, unnecessary transactions may cause performance loss. Here is a rule that uses transactions only when the operation is required. For example, if you only query some records from the database or execute a single query, explicit transactions are not required most of the time, because the declarations have been encapsulated in implicit transactions. However, as mentioned above, it is very important to update multiple declarations because transactions can actually increase the operation speed. Similarly, if you need to make a choice between saving several milliseconds and endangering data integrity, the correct answer is to keep the data clean and do not worry about the time consumption of those milliseconds.

Note the following before using transactions: keep transactions as short as possible. Avoid using the SELECT statement in the transaction to return data unless the statement depends on the returned data. If a SELECT statement is used, only the required rows are selected. Therefore, do not lock too many resources and maintain as high performance as possible. In the case of architecture order, all SELECT statements are removed from the business. This is because the transaction locks all operated data rows during processing, which affects the execution of other concurrent SQL statements.

Articles you may be interested in

• About database (MySQL) Transactions
• Mysql creates a stored procedure and calls it in php
• Differences between MySQL Database Engine MyISAM and InnoDB
• Use the PHP function memory_get_usage to obtain the current PHP memory consumption for program performance optimization.
• Use stored procedures in php and mysql
• Mysql stored procedure details
• How to know that the SQL statements executed in the database are slow
• How to sort the database query results in the alphabetic order of the first Chinese Characters