One SQL Server optimization experience

Recently, I was working on a report in an ERP system. The big column in this report is statistical, such as delivery rate and purchase percentage. This means that it is not possible to simply connect to several tables, the data of several basic tables can be used to obtain the report data. Instead, several tables are connected and then computed. Or, unfortunately, after a round of calculation, the obtained results are then calculated using the data of another table. In my report, a round of calculation is required to generate about 40% of the fields, which need to be connected, computed, and then computed. About 50% of the fields can be directly obtained, there are only two or three columns.

At first, I directly wrote a query statement and tried to use a query to get started. However, because the logic is too complex, the statements are filled with a large number of case when statements, and there are n-layer subqueries, which are hard to understand. In addition, some Columns cannot be obtained at all.

To clarify the query logic, I used a temporary table as the intermediate table method. I analyzed the report, calculated some columns that can be computed together, and saved them to the temporary table. Then, these temporary tables are merged again and the data of each column is calculated. In this way, the query logic is clear. At this time, there are about 6 temporary tables.

It takes about 10-20 seconds for the initial query. In fact, for the statistical data such as reports, 10 to 20 seconds can be tolerated. After all, the reports are not often used, so they can be tolerated during export. But soon another problem emerged: the server memory consumption was serious. It is estimated that there are too many temporary tables during REPORT query, and the timeout problem gradually occurs. If there is a small amount of data, there is nothing wrong with using a temporary table for storage. However, if there is a large amount of data, the memory usage will soar. If too many pages of SQL Server are exchanged, the loss will be worth the candle.

For more than two weeks, I still try to optimize the SQL statements by increasing and optimizing indexes. However, I am helpless, some business requirements make some calculations unable to use indexes (mainly because there are too many secondary calculations, and I have even thought about adding indexes to temporary tables ).

Finally, if real-time queries do not solve the problem, I chose another method: Use the pre-calculation method to make statistics on reports during idle time, put it in a table. If the leader needs to view the report, the system queries the table instead of the basic table computing data in real time. As I said, reports are mostly statistical data and do not require high real-time performance. In this case, you can "secretly" Generate Reports when the server is idle,

For query by users. I create a job to run report statistics at a.m. And A.M. every day. The job is stored in a table named "pre-Statistical table, in addition, the structure and report structure of this table are just as simple as possible, and the report can be obtained by "select * from table" as far as possible. After paging, the final query speed is not very high.

Originally, I didn't like this method very much: it was not so beautiful, and it added a lot of redundant data. In addition, if we use this idea to improve performance at the beginning, it will often reduce the database design paradigm, which is no different from the "context ". However, during this period, I have googled many blogs and many people agree that database design that strictly follows the paradigm is not necessarily a good design. It often reduces the paradigm, appropriately increases redundancy, and improves query performance. It is also a good Optimization Method in addition to the Optimization policies provided by SQL Server.