sqlserver建立索引 注意的问题（The problem of index creation in sqlserver）.docVIP

sqlserver建立索引 注意的问题（The problem of index creation in sqlserver） The problem of index creation in SQLSERVER 2008-12-17 16:23:19| classification: sqlserver | Tags: | name and subscription. When people use SQL, they often fall into a misunderstanding, that is, too much attention is paid to the correctness of the results, and the possible differences between different implementations are ignored Performance differences are particularly evident in large or complex database environments such as online transaction processing, OLTP, or decision support system (DSS) Show. In the work practice, the author finds that bad SQL often comes from inappropriate index design, incomplete connection condition and non optimized where clause. In the After proper optimization, their running speed has been obviously improved! Now I will summarize these three aspects: In order to illustrate the problem more intuitively, the SQL running times in all instances are tested, and no more than 1 second is expressed as ( 1 sec). - Test Environment - - host: HP, LH, II main frequency: 330MHZ memory: 128 megabytes Operating system: Operserver5.0.4 database: Sybase11.0.3 I. irrational index design Example: table record has 620000 lines. Lets see how the following SQL runs under different indexes: - - 1. has a non clustered index on the date Select, count (*), from, record, where, date, and, date,nd, amount, 2000 (25 seconds) Select, date, sum (amount), from, record, group, by, date (55 seconds) Select, count (*), from, record, where, and, date, place, in (BJ,SH) (27 seconds) analysis: date has a large number of duplicate values. In a non clustered index, the data is physically randomly stored on the data page, and a range scan must be performed once the range is searched to find all the lines within that range. - 2. a clustered index on date Select, count (*), from, record, where, and, date, date and, amount, 2000 (14 seconds) Select, date, sum (am