SQL Server Application System Performance Optimization Design

This article is reproduced from: wenku.baidu.comview4b1f67d97f1922791688e801.html when using SQLServer for system design, correct and rational design of databases, indexes, queries, selection of appropriate data types, and optimization of SQL statements can improve system performance. This article discusses how to optimize the application system performance.

This article Reprinted from: http://wenku.baidu.com/view/4b1f67d97f1922791688e801.html in the use of SQLServer system design, correct and rational design of databases, indexes, queries, selection of appropriate data types, and optimization of SQL statements can improve system performance. This article discusses how to optimize the application system performance.

This article Reprinted from: http://wenku.baidu.com/view/4b1f67d97f1922791688e801.html

In SQL ServerSystemDesignIs correct and reasonableDesignDatabase, index, query, select appropriate data type,OptimizationSQL statements can be improvedSystemPerformance. This article discussesApplicationSystemPerformanceOptimizationAnd give some useful suggestions.
DatabaseApplicationSystemDesignIt should contain two aspects: 1. StructureDesign, That isDesignDatabase framework or structure; second, behaviorDesign, That isDesignApplicationPrograms and transactions.
1 DatabaseDesign
To achieve optimal performance in a good SQL Server SolutionPerformanceThe most important thing is to have a good database.DesignSolution. In actual work, many SQL Server solutions are often caused by DatabasesDesignPoorPerformancePoor. Rational DatabaseDesignIs to improve overallPerformance.
InDesignDatabase considerations
①ApplicationProgramPerformanceRequirement.DesignYou must use SQL Server to improve database performance.Performance. ForPerformanceIt is important to weigh the database size and hardware configuration.
② Consider whether to use a narrow table or a long data table. Using multiple and correlated narrow tables instead of long data tables with many columns can make sorting and indexing faster. Because the table is narrow, each table can have fewer indexes, so it can improve the speed of Insert, Update, Delete and so on, because these operations willSystemPerformanceIt has a great impact. However, a large number of tables will increase the complexity of the number of referenced tables and their connection relationships when obtaining data. Too many tables and complex connections will reduce the server'sPerformanceTherefore, the two must be considered comprehensively.
③ Correctly and reasonably select the data type. In SQL Server, data is essential. The data types selected for each attribute are largely based on the requirements of the table, but without violating the requirements of the table, selecting the appropriate data type can improveSystemPerformance. The data types related to each table column should reflect the minimum storage space required by the data. Do notDesignIs optimized, which improves query and ConnectionPerformance. This is because the engine only needs to compare the number type once when processing queries and connections, while the character type will compare each character in the string one by one. If the smallint type is used, do not use the integer type. In this way, index fields can be read faster and more data rows can be placed on one data page, therefore, I/O operations are reduced. If the length of all values in a column varies greatly, use varchar/nvarchar instead of char/nchar as much as possible. Because the long-length field storage space is small, it can save storage space. In addition, for queries, searching in a relatively small field is obviously more efficient. If the length of all values in a column is the same or the length of values is not much different, it is more efficient to use a fixed length column.
2ApplicationProgramDesign
DatabaseApplicationSystemDesignOptimizationAn important aspect isApplicationProgramOptimization.ApplicationProgramDesignWhen you decide to use SQL ServerSystemOfPerformanceIt plays a key role. Below are some basic development skills and related technologies.
① Eliminate excessive network traffic. The Network round-trip between the client and SQL Server usually affects the database.ApplicationProgramPerformanceThe primary cause is even greater than the amount of data transferred between the server and the client. InDesignApplicationThe program should filter data, minimize unnecessary data from remote data sources, and minimize network traffic.
② Use the stored procedure. The use of stored procedures can greatly improve the operational efficiency, and can be independentApplicationYou can modify the stored procedure to allow users to access certain data through the stored procedure and perform restricted operations to ensure data security in the table. ③ Use SET Nocount session settings. If there are multiple statements in the stored procedure, the SQL Server sends the statement to the client when each statement is completed by default.ApplicationThe program sends a message detailing the number of lines affected by each statement. If you are sureApplicationSET Nocount sessionApplicationThe program disables these messages. Because this setting greatly reduces the network traffic, it can significantly improvePerformance.
④ Use a moderate result set. Retrieving unnecessary result sets and browsing on the client will increase the load on CPU and network I/OApplicationThe remote use capability of the program is reduced and the multi-user scalability is limited. It is bestApplicationProgramDesignEnter sufficient information to prompt the user to query and generate a result set of moderate size after submission.
⑤ Send statements in batches. To improve the execution efficiency of the program, multiple SQL statements can be separated using the Go statement in a program written in the T-SQL language, and the SQL statement between two Go statements can be used as a batch process. SlaveApplicationThe program is sent to the SQL server for execution at one time, accelerating program transmission and execution.
⑥SystemScalability. InDesignMust fully considerSystemScalability to enableDesignEasy to change. OneDesignExcellent DatabaseSystemIt should have certain scalability,ApplicationChanges in the environment and the emergence of new requirements generally do not overturn the originalDesignDoes not apply to existingApplicationPrograms and data have a big impact, but only in the originalDesignSome extensions can meet new requirements.
3. Index QueryDesign
DesignIndexing and query are good for SQL ServerPerformanceIs very important.
31 IndexDesignAndOptimization
ValidDesignIndex can be improvedPerformance. Common indexes are classified into clustered indexes and non-clustered indexes. Clustered indexes ensure that the physical order of records in the database table is the same as the index order, and the retrieval efficiency is higher than that of normal indexes. However, they have a greater impact on the increase, modification, and deletion of data, therefore, clustered indexes are suitable for fixed tables. Non-clustered indexes do not affect the actual storage sequence of records in the data table and require less storage space than clustered indexes. the retrieval efficiency is lower than that of clustered indexes, but it has little impact on data addition and modification and deletion, and is suitable for tables with frequent data changes. InDesignDuring the processDesignCriterion to queryOptimizationFeature-basedDesignReference, inDesignWhen using an index: ① use a narrow index. Narrow indexes have high efficiency. For narrow indexes, there will be more index rows and fewer index levels on each page (compared with multi-index and composite index ), therefore, more index pages can be stored in the cache, which also reduces I/O operations.
② Do not create indexes for columns that are frequently updated; otherwise, they will be seriously affected.Performance.
③ Do not index a table with a small number of records. Indexing small tables may not produceOptimizationEffect.
④ Only index fields that are frequently used for retrieval. The more indexes, the better. Because the index itself occupies a certain amount of storage space, and when data is added or updated, the database needs to perform additional operations to maintain the index, these operations willSystemPerformanceIt has a great impact. InDesignAnd when creating an index, make sure thatPerformanceThe increase is greater than the cost of storage space and processing resources.
⑤ Null cannot be used as an index. If a column has a null value, it will not increase even if the column is indexed.Performance.
32 QueryDesignAndOptimization
Data Query is the core operation of the database. QueryOptimizationTo avoid full table scanning. ForApplicationThe program focuses on the execution efficiency of SQL statements. SQLServer provides Select statements for database query. SpecificOptimizationMethod:
① When creating an index, you should consider the columns involved in Where, Order By, or Group.
② Only specify the required fields. Do not use statements such as Select * From unless all fields in the table are required. ③ The Where Clause limits the number of downloaded records. When executing a query, you should avoid unlimited read and processing of all rows in a table unless it is fully required. The more accurate the range and condition set by the Where clause, the less records are transferred to the computer, and the faster the query is completed.
④ Avoid Null value determination on the field in the Where clause whenever possible. When a field has a Null value, you can set the default value on this field ., Make sure that the column in the table does not have a Null value. For example, Select ID From Table Where Num Is Null
You can set the default value on the Num field ., Then query: Select ID From Table Where Num = 0
⑤ Avoid using NOT and! In the Where clause whenever possible ,! = Or <> operator. Because such clauses are exclusive rather than inclusive, the selectivity of clauses cannot be determined before scanning the entire table.
⑥ Avoid performing function operations on fields in the Where clause as much as possible. This will cause the engine to discard the use of indexes for full table scanning, unless a function-based index is created.
For example, Select ID From Table Where Substring (Name, 1, 3) = 'xx'
Should be changed to: Select ID From Table Where Name Like 'xx %'
7. For continuous values, use Between instead of In.
For example, Select ID From Table Where Num In (1, 2, 3)
Can be changed to: Select ID From Table Where Num Between 1 And 3
Sort Reasonably arrange the order of multiple selection conditions. Select the sort order of conditionsPerformanceTo improve the query response speed, you need to write more stringent conditions in front, and write weaker ones in the back.
4 Conclusion
InDesignSQL Server database developmentApplicationSystemThe rational and effective use of the above methods can certainly improvePerformance,SystemPerformanceOptimizationIs a complex process that affects the databaseSystemPerformanceThere are many factors, whileApplicationAnd find a commonOptimizationThe solution is unrealistic.SystemSelect a proper database based on actual conditions during developmentSystemDesignAndOptimizationPolicy to make full use of database managementSystemHigh AvailabilityPerformanceService enablingApplicationSystemMake full use of its efficient functions.