Shulou(Shulou.com)06/01 Report--

This article will explain in detail how the SQL stored procedure is executed quickly, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Used to repair some old systems, query a report, after a long wait, still do not come out the results, and the client is often single-threaded, during this period of time users can only wait, can not do anything else, even if it makes people angry, but still can only endure.

Customers complain about the slow query, and the engineers often find that the stored procedure of the query is too poorly written, in fact, the amount of data of the query is not large, and the algorithm is not so complex, but the result is simply harmed by the rotten stored procedure. Often after optimizing bad stored procedures, it used to take 30 minutes to execute, but now it may only take a few seconds.

In fact, there are rules to follow when writing good stored procedures. The algorithm is nothing more than time-space conversion. In order to write and execute fast stored procedures, it is necessary to exchange space (memory space) for time (CPU processing time) as much as possible. For stored procedures, caching is often a temporary table. Create necessary fields in the temporary table without any extra fields. Then define strict filtering criteria to insert data from the source table into the temporary table. The intermediate calculation processing is carried out on the temporary table, if you need to change the processing result back to the source table, and then update the data from the temporary table back to the source table. This makes full use of memory space and minimizes the processing time of CPU. I thought this was the way to write stored procedures.

So much for sharing about how SQL stored procedures are executed quickly. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.