ASP. NET program performance optimization experience (1): Database

Database performance optimization
I. Field Creation

1. reduce cross-table queries

After the requirements are determined, databases are often created. Therefore, database creation and database operations are inseparable from the addition, deletion, modification, and query operations. Queries should be frequent operations, A basic principle for improving query operations is to minimize cross-table queries, that is, JOIN, UNION, and subqueries. This situation is often the most common, generally, one of the fields in Table A is the foreign key of Table B. During query, all the data in Table A needs to be queried at the same time, at the same time, the field matching the foreign key of Table A will be queried, which will greatly increase the query cost. Here we take the document system as an example. The general table structure is as follows:

News (NewsId, NewsTitle, Content, CateId, PostUserId, Hits, AddTime) -- Article
NewsCate (CateId, CateTitle) -- document category
Such a table structure requires two queries or connection queries to obtain the data of the two tables. If you COUNT the number of articles under a category, you also need to select count (*) to scan the entire table of the News table, which will seriously affect the performance.

If the table structure is not properly designed, optimization of SQL statements is not helpful. The improvement is to minimize the number of cross-table queries in a table. You can create the table structure as follows:

News (NewsId, NewsTitle, Content, CateId, CateName, PostUserId, PostUserName, Hits, AddTime)
NewsCate (CateId, CateTitle, NewsNum)
Here we have made two improvements. One is to merge CateName into the News table at the same time to avoid performance loss caused by cross-table join queries (PostUserName is similar to this ); the second is to store the total number of records in the News table to the NewsNum field in the NewsCate table, which can avoid the select count (*) performance Loss (the effect is obvious in the case of large data volumes ). The performance impact of the resulting redundant fields can be significantly compared with the previous performance impact. This kind of optimization can be called space-for-time.

2. sorting problems

The other point is for the AddTime field, which is usually sorted by it. This DateTime type field is calculated during sorting, and its sorting is much slower than that of the Int type, therefore, you can also consider adding a new DateNum (int) field to store the date. For example, if AddTime is, The number inserted to DateNum can be 20110527, in this way, order by DateNum DESC can be used to reduce the sorting time. Of course, if your AddTime is getdate () BY default and there is only one sort BY time, you can order by NewsID DESC.

For sorting, the database queries the data that meets all the WHERE conditions before sorting. Therefore, do not use complex sorting if necessary, you can consider whether to add OrderNum to reduce the related sorting according to the actual situation. For complex sorting, we will discuss the index problem in the second point.

3. Do I need a foreign key?

If your database is good at learning, you must remember the database paradigm. Meeting the three-level paradigm is the standard database design. In actual situations, you must never write your own books. There has been a lot of debate on whether foreign keys are needed. My understanding is that foreign keys are a constraint, which helps avoid program insertion of abnormal data, abnormal data may increase where the program needs to handle exceptions, and the Code increases, reducing program stability. However, in actual development, foreign keys will lead to complicated debugging programs and reduce the efficiency of SQL Execution in a certain program, because the engine will verify the validity of data before data insertion, it will also reduce the database performance in a certain program, in addition, when the foreign key data is deleted, an unexpected exception may occur when querying the master table data. In my personal suggestion on the website, foreign keys are not needed, but to avoid DBNull, the option "whether to be empty" should be selected as not allowed if necessary.

2. index creation

Before the project starts, determine which queries may be performed frequently in the actual project. Generally, the programmer and DBA are the same person, therefore, we assume that you already know where these queries will be frequently queried. Taking an article system as an example, there may be frequent queries of time, category, and keyword. If necessary, you need to create an index. Generally, you need to create an index for each index. For example, the actual table structure is as follows:

News (NewsId, NewsTitle, Content, CateId, CateName, PostUserId, PostUserName, Hits, AddTime, CommentNum)
Assume that queries are performed frequently by classification and time sorting, you need to create the CateId and AddTime indexes respectively. If this query is a complex query, if the SQL statement is as follows:

SELECT * FROM News WHERE CateId = 1 order by AddTime DESC, CommentNum DESC
You can create a composite index for multiple fields. Here, you can create a composite index for CateId and AddTime, and include CommentNum if necessary.

If a keyword is frequently used for search, we recommend that you create a full-text index on the query field. Full-text index is a query rule based on the built-in search algorithm of SQL Server, and the performance is much better than LIKE. For example, query title:

Select top 10 * FROM News where contains (News, 'mongoingp ')
* Note that full-text indexing is available only in SQL 2000 and later versions.

Iii. Query Optimization

Query is used to optimize the vast majority of SQL statements. Different SQL statements may make query time quite different. The core of query optimization is to reduce scan and try to achieve seek; scan indicates full table scan, and seek indicates that a row is located. Use COUNT, NOT ,! =, IN, LIKE, and so on will cause full table scanning. If there is enough data IN this table, the performance impact will be very large. The better way is to avoid this full table scan, and use the most accurate condition restriction to narrow the scope of the database scan and reduce the SQL Execution time.

The preceding table is used as an example. The most commonly used DAL function of ASP. NET programs is to retrieve data based on a specific number and store the data in the object.

SELECT * FROM News WHERE NewsId = @ NewsId
In this way, even after the required data is queried, it will still perform a full table scan for the remaining data, which wastes a lot of resources and program time. You can use TOP 1 to restrict conditions:

Select top 1 * FROM News WHERE NewsId = @ NewsId
Suppose there are 1 million data records, and NewsId = 1 in practice, we save the time to query 999999 data records.

In general, the News table will be large, but there will be very few NewsCate. For the two tables with very disparity in comparison, If you perform JOIN queries, place the NewsCate with small data after JOIN, this improves query performance.

Query Optimization involves a wide range of related materials. You can search for problems such as IN and EXISTS, and test the query performance IN the actual database, then decide which one to choose.

Iv. Use of SQL Profiler

SQL Profiler is the most easily overlooked tool, which is a very powerful tool for database performance optimization. It is bound with Management Studio during SQL Server installation, select create trail, and then select the corresponding event columns in the trail properties. Generally, select CPU, Reads, Writes, Duration, StartTime, and EndTime, they correspond to the CPU usage of physical SQL statements, the number of reads and writes to the hard disk, and the start and end times. It intuitively shows the impact on SQL Performance.

For example, I tested the performance of SELECT * and SELECT NewsId for 1 million pieces of data. We can intuitively see that SELECT * is much larger in terms of CPU consumption and hard disk reading. Therefore, we recommend "how much to eat, how much to take" in the actual project ".