Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use WITH AS to improve performance. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

How to use WITH AS to improve performance

1. Cause of case

The company's store application issues a report every day, which is used to count the opening inventory, incoming and outgoing quantities of all goods on the same day.

And the quantity of inventory at the end of the day. After running for half a year, the report runs more and more slowly, and now it takes nearly 20 seconds to run the report to display the data for the day. So the developer came to me and wanted me to see if I could make the report run faster.

The statement is a SQL statement, which is mainly composed of three parts, the first part is to calculate the opening quantity of each commodity, the second part is to calculate the quantity of each commodity that occurred on the same day (including those in and out of the warehouse), and the third part is to calculate the final quantity of each commodity, that is, the balance of the day. Each part is connected using UNION ALL.

When I saw the report, my first feeling was that every part of the SQL had to scan the table, which was obviously too expensive. It should be possible to rewrite using WITH AS.

2. The meaning of WITH AS

The WITH AS phrase, also known as the subquery section (subquery factoring), allows you to do a lot of things, defining a SQL fragment that will be used by the entire SQL statement. Sometimes, it is to make the SQL statement more readable, or it may be in different parts of the UNION ALL as part of providing data.

It is especially useful for UNION ALL. Because each part of the UNION ALL may be the same, but if each part is executed once, the cost is too high, so you can use the WITH AS phrase, just execute it once. If the table name defined by the WITH AS phrase is called more than twice, the optimizer automatically puts the data obtained by the WITH AS phrase into an TEMP table, but not if it is called only once. The prompt materialize forces the data in the WITH AS phrase to be placed in a global temporary table. Many queries can be improved in this way.

This is the end of this article on "how to use WITH AS to improve performance". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please 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.