SQL database in the application we often fall into a trap, that is too focused on the results may be correct while ignoring the emergence of different tips for the existence of performance differences between the estimates, this difference in performance in large or complex database environment (such as online transaction processing OLTP or decision support system DSS) was particularly evident in the. I invented the practice in the workers, often poor SQL database indexing from the inappropriate design of the connection is not required and can not be fully optimized wITe clause. They stopped right in the optimization, its speed has significantly increased! The following three areas I will summarize each stop:
SQL database in the application we often fall into a trap, that is too focused on the results may be correct while ignoring the emergence of different tips for the existence of performance differences between the estimates, this difference in performance in large or complex database environment (such as online transaction processing OLTP or decision support system DSS) was particularly evident in the. I invented the practice in the workers, often poor SQL database indexing from the inappropriate design of the connection is not required and can not be fully optimized wITe clause. They stopped right in the optimization, its speed has significantly increased! The following three areas I will summarize each stop:
More difficult to visually explain all the examples in the SQL database running time is tested, not more than one second of all that is (<1 second).
Test Environment -
Host: HP LH II
Frequency: 330MHZ
Memory: 128 MB
Operating System: Operserver5.0.4
Database: Sybase11.0.3
First, the index is not logical design
Example: the table record with 620,000 lines, Look in the index is not the same, the following operation of several SQL databases:
1. The date on the construction of a non-clustered indexes were
Planning:
date on a large number of repeat value, the non-clustered index, the data is physically stored in data random page, look at the range, the need to run a table scan to find all the lines within this range.
2. The date on a cluster index
Planning:
In the clustered index, the data in physical order in the data page of press, and repeated value are arranged together, Yin Er Chazhao O'clock in the area, not Cheng Ji Xian Zhao Dao that range from points in and to that extent 只 scan data page avoid large-scale scanning, increase the query speed.
3. Socate, date, amount, on a combination of the index
Planning:
This is not a very logical combination of the index, because of its leading column is socate, NO · 1 and the second SQL database does not reference socate, so do not use the index; third SQL database applications the socate, and references All columns are included in the combination of the index, an index covering the formation, so it is unusually fast pace.
4. The date, socate, amount on the combination of the index
Planning:
This is a logical combination of the index. It will date as the leading column, so no score for each SQL database using the index, and NO · 1 and the third formed the SQL database indexing coverage, and thus achieve the optimal performance.
5. Summary:
By default, the index established under the non-clustered index, but sometimes it does not do the best; index logical design of the establishment of various queries in the planning and Yuce on. In general:
①. There are a lot of repeat value, and often the scope of inquiry (in with,>, <,>=,< =) and order by, assemble by addition of the column, the index may be planning to establish cluster;
②. Often simultaneous access to multiple columns, and each column contain a duplicate value may be planning to establish combination index;
③. Combined Index to form as far as possible the key to query the index coverage, the leading column is the most frequently identified column.
A total of five.
No comments:
Post a Comment