There are many Database Data Optimization stories and Database Data Optimization stories

There are many Database Data Optimization stories and Database Data Optimization stories

Base call evaluation system interface, we can see that the IIS process remains high when running the program.

So I tried several methods to optimize the database.

First add an index.

An index is a structure that sorts the values of one or more columns in a database table. You can use an index to quickly access specific information in a database table. Indexes are just like the relationship between directories and the entire book. With the database index, we can first find the Directory and then quickly find the field we want to query. If you want to search for a specific student by his or her surname, the index will help you get information faster than searching all rows in the table.

Advantages:

Creating indexes can greatly improve the system performance. First, you can create a unique index to ensure the uniqueness of each row of data in the database table. Second, it can greatly speed up data retrieval, which is also the main reason for creating an index. Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity. Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.

 

Of course, it also has a disadvantage: too many indexes will affect the update and insertion speeds, because it needs to update each index file as well.

With some basic theories above, we begin to reform. The data size of students in the class will be extremely large, with more than 0.5 million data records. There are several basic principles for adding indexes:

1. Add it to fields frequently searched, because the index has been sorted, so that the query can use the index sorting to speed up the sorting query time;

2. Add the preceding values to be sorted by a field.

3. It is not added to fields with very few values. For example, gender only has two values: male and female.

Fortunately, we added the index before the data is imported. After 0.5 million data is imported and the view is indexed again, the operation times out ..

Second, create a view

These two days, the database operations have a deeper understanding of table connection. I have also written a blog post about creating a view before. You are welcome to take a look and try again. 3.0 Optimize Query-introduce View

It has no actual physical records and is especially suitable for multi-table join browsing. Like in our table, we need to query all the courses, types, and names of instructors for a student. We need to query the class ID in the class table based on the students, and then query the corresponding class ID in the class and administrative class tables based on the class ID, with the class ID, you can query the class teacher ID based on it, and then query the name of the teacher in the teacher table based on the class teacher ID. Query the course ID based on the class ID, and then query the course ID and name in the curriculum. Finally, find the course type ID based on the course ID and go to the course type table to query the course type name.

Such a query result is displayed. It is lightweight. After the view is used. All information is in the same table. Directly query the desired field.

However, here we consider that if we put all the courses of all students in one view, the data volume for one semester is basically more than 30000*10. So we thought of a way to fold it, that is, a view table stores public classes in the class where the student is located. In this way, more than 50 student data records are reduced to one class data. Although the class information needs to be queried Based on the student ID, the query time is also reduced.

The above two optimizations are our true operations and practices in this system. There are still many optimizations to the database.

For example, the application stored procedure. Speaking of this, we made an experiment and wrote an inefficient query statement when we had 0.5 million more data.

Select * from dbo. BasicTeacherEntities where TeacherID not in (select teacherID from dbo. BasicOnClassEntities where OnClassID not in
(Select OnClassID from BasicOnClassStudentEntities where StudentID = '193a83fc-558e-490c-b51f-04365a74a3be '))

As a result, it uses 11 s for the first query, But it is strange that I only use less than 1 s for the second query. I don't need to wait for another StudentID.

Is it the same principle as stored procedures? To be considered.

There are two other points under study. Database shard and data dictionary front-end cache. The blog will be updated in a timely manner.