My experiences in ASP. NET program performance optimization (1): Database

Personal ASP. Net Program Performance Optimization experience series:

My experiences in ASP. NET program performance optimization (1): Database

My experiences in ASP. NET program performance optimization (1): Database (another article)

Personal experience in ASP. NET program performance optimization (2): ASP. NET code optimization

Personal experience in ASP. NET program performance optimization (3): frontend Performance Optimization

------------------------------------------------------------------------------

Preface

I believe that many programmers in the garden are working on small and medium-sized projects such as xx management system. Such projects are often a kind of workload code, and programmers will spend their youth on such projects, constantly changing requirements and working overtime, I began to doubt this industry and hate developers and want to learn new things. However, the level of colleagues around me is almost the same; I don't have time to work overtime when I want to buy books. This situation is believed to happen most of the time around us. I used to be in this situation and used the Asp.net language. However, it is difficult to define whether the project is a website or software, because of its complexity, there is no difference between the development cycle and traditional software development, but it is indeed deployed on IIS and can be accessed through a browser. Or programmers who specialize in website creation for enterprises. The kernel of a set of programs remains unchanged, but each website is changed to a shell, news system, message system, and download system ....... Why should I say this? As I want to say before, these are not really Internet companies. Such companies tend to pursue maximization, fastest and most direct interests and sign for money, the role of the whole process programmer is almost negligible, because the project can be continued after just a few graduates take a few months or two.

In Internet companies, especially medium and large websites, the performance is definitely a very important position. Imagine that there are millions of IP addresses and tens of thousands of concurrent websites on a daily basis, if the homepage saves 1 k of traffic, a considerable cost will be saved for the enterprise in one day. page loading will reduce the user loss by 1 second. I mentioned in the previous section that a small company is working on a project. Now, in a medium-sized Internet company, there are hundreds of thousands of websites with a daily PV, the impact of front-end, code, and database design on the website is so significant that I have never personally experienced it before. In the past year, I have summarized some experiences on ASP.net performance optimization. due to personal limitations, it is inevitable that there are some inaccuracies and imperfections, welcome to Brick O (∩ _ ∩) O

In this series of articles, I plan to spend three major directions to explain some performance aspects, including database performance optimization, Asp.net program optimization, and front-end optimization. Here I will put the database performance optimization first, represents its importance. I personally think that the most important impact on a website's performance is the three aspects. optimizing these three aspects one by one will greatly help improve the website's performance.

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) -- Article 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.

II,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，‘walkingp’)

* 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.

My experiences in ASP. NET program performance optimization (1): Database

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 ".

My experiences in ASP. NET program performance optimization (1): Database

The above section is about database performance optimization. We look forward to a better discussion. The next article will explain the optimization of ASP. NET code.

This article is also posted on my personal blog http://www.walkingp.com/

(Thank you for your guidance and discussion. I will update the version of this article at night to avoid misleading new users .)