Recently, I have been working on a statistical page optimization job, and the project is nearing completion. So I will share some of the optimization experiences with you. First, let's introduce the environment of this project. As this is an old project and developers are basically absent, ASP + Oracle and oh my gold are used! The main function of the page is to collect statistics on the various statuses of the mail (a total of 13 statuses). Before optimization, the page access speed is slow, it takes 5 minutes or longer to count the number of mails in a month. Therefore, it is necessary to optimize the performance. We will optimize the functions step by step.
- We will create an index for the fields in the query statement in the Oracle database. This step should naturally come to mind, because creating an index can greatly improve the system performance. Reminder: Do not blindly create an index. Sometimes the loss is worth the candle.
- Oracle hints is used in SQL statements because the Lecco SQL expert pro for Oracle () tool is used (), this tool filters different combinations of SQL statements (the time consumed by the system is listed in SQL run time) and applies Oracle hints directly to it, you can select SQL statements that consume the least time. The tool is very easy to use and saves a lot of time for our optimization work. (Tool: http://files.cnblogs.com/wangqi/LeccoSQLExpertForOracle_1.rar,http://files.cnblogs.com/wangqi/LeccoSQLExpertForOracle_1.rar)
- After we have optimized the SQL statements, we will start to process the page. because a large number of nested loops are used in the page, our principle is to reduce the number of cycles, the more nested loops, the lower the efficiency. Use SQL functions as much as possible in SQL statements and do not throw them to the page for execution. Because the Page code is messy, we need to sort out the page code. Although it may not be helpful, the code after sorting looks refreshing.
- Through the above steps, we have completed most of the optimization work, but the results are not as slow as we think. This makes us very depressed, until we use the composite index, the situation has greatly improved, we previously just created a single index for each field, but this time we will combine these indexes in a certain order (), here, I want to emphasize that the index order must start from the back to the front, that is, to push forward from the last condition in the SQL statement. (If there is only one condition, Oracle selects the composite index we created based on its own optimization policy)
By using composite indexes, the speed of accessing the page is greatly improved. Currently, both quarterly and full-year mail statistics only take one second. Haha ~~~
