Thoughts on Database Query Optimization

For example, I have some news information, including these fields: News ID, news Name, news ShortIntro, news Detail, news PublishTime. Now I want to store it in the database, and then retrieve it from the database and put it in the GridView for pagination.

Now I am simulating this implementation step by step in a common way of thinking that I have seen.

Step 1: Create a news data table.

In this step, many people will directly create a News table, which includes the fields mentioned above.

Step 2: query data.

Write a method to extract the data that meets the query conditions in the News table and place it in DataSetDataTable as the data source.

Step 3: bind to the GridView.

Set the pagination attribute of the GridView and bind the data source obtained in the preceding query to the GridView to display the data by page in the GridView.

The above is our common practice.

My approach will be like this:

Step 1: Create a master-slave table for news data.

During system development, we will find that, in fact, many list items are not used in a complete piece of data information. We analyze the News information. We can initially take ID, Name, ShortIntro, and PublishTime as the main information. We will combine the information to create a new table News (ID, Name, ShortIntro, publishTime), where another field Detail is placed in another slave table, create a new table NewsDetail (ID, Detail, NewsID ). What are the advantages of doing so? First, we reduced the "weight" of the table ". We can simplify the most important and commonly used information into a primary table. In this way, we only need to obtain the data we need from this table, instead of traversing all fields like the first method, this reduces the time required for database queries and improves performance. The principle of creating a master-slave table is to use the most important and commonly used separation as the master table, and use descriptive and massive content as the slave table

Step 2: compile appropriate SQL statements.

We should compile appropriate SQL statements for different functions. In the above method, all the data information is queried using one method, which meets the data requirements of all occasions. However, we don't need so much data, and the extra data takes a lot of time and space. We usually only need some of the content, such as the main information. This also confirms why we have to create a master-slave table. After creating a master-slave table, you can write the following methods to meet various requirements: GetNews (int? ID, string Name) // query the data that meets the conditions from the master table. GetNewsDetail (int? ID, string Name) // query the data that meets the conditions in the master and slave tables. The first method provides the main news information, and the second method provides comprehensive information. These two methods can basically meet all scenarios without bringing too much data redundancy. Some people prefer to write GetNewsByID (int? ID), GetNewsByName (string Name), this write is flexible and targeted, but this write is completely unnecessary.

Step 3: bind pages.

The above method is to extract all the data to the GridView at a time, and let the control go to the page by itself, so that it is convenient and convenient. But there are several problems:

1) large data volume. Because all the data that meets the condition is retrieved at one time, the data volume is large, and the data must be stored in the memory, which affects the system performance. In addition, there will be some cards when loading for the first time, giving people the feeling that the system loading is not smooth.

2) We don't need that much data. Why should I say that? By studying users' usage habits, we will find that users do not browse data page by page in most cases. users usually focus on the first few pages. Therefore, most of the retrieved data is not viewed by users.

So here, the paging query method is more suitable. Each time, only one page of data is queried from the database, so that the system load is low, the page load is smooth, and can fully meet user requirements. Some may ask, if you do not increase the number of database I/O operations, what I want to say is to obtain a large amount of redundant data at a time, and bear the persistent impact of redundancy. Compared with the theoretically increasing I/O times, users will not view the page by page, and will not generate so many paging queries, querying by PAGE has the undeniable advantage.