Asp.net program optimization minimizes database connection operations

The project is coded in my own design and integrated for testing. An error occurred while initializing the data. At the beginning, the client was slow to accept data. In the test environment, the database server is deployed abroad, and the website is deployed inside the company. I always think that my program has been optimized in database data processing, the indexes and primary keys have been properly used. To sum up, the speed problem at the beginning has not attracted my attention.

However, the key to the final problem lies in the database connection query. frequent queries lead to slow data initialization. At the beginning, I used the out-of-the-box query method: I checked the data from the database when I needed it. A large number of single-table queries returned a single field. If I have about 3000 data sets, I cyclically retrieve each of them and query the required fields from other data tables, in this way, more than 3000 database connections and shutdown operations are added for no reason. When the network speed is not ideal, the program speed will be intolerable.

Stick to one principle: Minimize database connection operations. It is similar to the minimum number of Http requests during frontend development optimization.

In the end, I adopted the solution to put the required data into a List object through multiple data tables, that is, to the memory, and query each data entry based on LINQ, this is much faster. This seems to be a destructive principle. The multi-table queries I followed earlier should not contain more than three inner join3 tables, but this time there is a situation where one SQL statement is more than three inner join3 tables. although this principle is broken, inner join is acceptable even when the speed is extremely slow and the functions cannot be implemented.