Something about Indexes

1. The two tables have the same ID in connection query. One ID is int type and the other ID is nvarchar.

Select a. Name, sum (B. chengji) from a, B where a. ID = B. ID and A. name not in ('0', null)

Time consumed: 32 seconds

After the guidance of csdn

Improvement:

Select a. Name, sum (B. chengji) from a, B where a. ID = B. ID and A. Name <> '0' and A. Name is not null

Time consumed: 32 seconds

Found so slow

Later, Baidu and Google studied the table structure and found that the IDs and character types in the two tables were slow.

Id nvarchar --> ID int

Then, what?

Select a. Name, sum (B. chengji) from a, B where a. ID = B. ID and A. Name <> '0' and A. Name is not null

Time consumed: 200 milliseconds

////////////////

1. Function for converting characters: Cast (ID as INT) takes precedence over convert (INT, ID)

2. Try not to use not in

3. If there are many columns, use no texists

4. It is faster to add a clustered index to the column data compared with the two tables. (is the clustered index equal to the clustered Index ?)

5. Pay attention to details

---------------- Cute split line --------------

About composite indexes

I have a view like this.

Select. name,. sex,. address, sum (B. qw1), sum (B. qw2), sum (C. qw3), sum (D. qw4) from a, B, c, d Where. id = B. ID and. id = C. ID and. id = D. id group by. name,. sex,. address

Composite indexes should be used here

Because name is the most commonly used sex, address is the second

Therefore, to create a composite index, add a composite index and set an independent index respectively.

Create index ix_name_sex_address _ table on (name, sex, address)
Create index ix_name _ table on table (name)
Create index ix_sex _ table on table (sex)
Create index ix_address _ table on (address)

Reference: http://topic.csdn.net/t/20040330/13/2903545.html

Use exists or

Select * From spt_values where number in (1, 2, 4) Select SPT. * From spt_values as SPT join (select number = 1 Union all select number = 2 Union all select number = 4) B on SPT. number = B. number -- can be abbreviated as -- select SPT. * From spt_values as SPT join (-- select number = 1 -- Union all select 2 -- Union all select 4 --) B on SPT. number = B. number