Using indexes for SQL optimization (a small example)

I haven't written it for more than a year, and sometimes I have the urge to write a few words. Every time I want to write it, sometimes it is a record. Without other ideas, it is also an extra benefit to be useful to others.

I haven't written it for more than a year, and sometimes I have the urge to write a few words. Every time I want to write it, sometimes it is a record. Without other ideas, it is also an extra benefit to be useful to others.

According to the operation and experience in this article, we will have a basic and simplest understanding of SQL optimization. Other in-depth learning needs more information and practices:
1. Create a table:
The Code is as follows:
Create table site_user
(
Id int IDENTITY (1, 1) primary key,
[Name] varchar (20 ),
Code varchar (20 ),
Date datetime
)

2. Insert 80 thousand data records
The Code is as follows:
Declare @ m int
Set @ m = 1
While @ m <80000
Begin
Insert into [demo]. [dbo]. [site_user]
(
[Name]
, [Code], date)
VALUES
('Name' + CAST (@ m as varchar (20 ))
, 'Code' + CAST (@ m as varchar (20), GETUTCDATE ())
Select @ m = @ m + 1
END
-- Tips: We recommend that you use tools similar to sqlassist to speed up SQL statement writing.

3. Set to enable some parameter settings
The Code is as follows:
Set statistics io on -- view disk IO
Set statistics time on -- View SQL statement analysis compilation and execution time
SELECT * FROM site_user -- view results

4. View table indexes:
Sp_helpindex site_user

5. Execute SQL statements
The Code is as follows:
SELECT * FROM site_user su WHERE su. name = 'name1' table 'site _ user '.
Scan count 1, logical reads 446, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, lob pre-reads 0

Ctrl + L shortcut key to view the execution plan:

6. Optimization Step 1: clustering index scanning overhead accounts for 100%. You can consider optimizing the index search to create a non-clustered index on the query condition name.
The Code is as follows:
Create index name_index on site_user (name)
Sp_helpindex site_user -- add the newly created index

Then run the preceding query statement:
The Code is as follows:
SELECT * FROM site_user su WHERE su. name = 'name1'
Table 'site _ user '. 1 scan count, 4 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

The number of logical reads to the disk decreases significantly, and then you can view the execution plan:

The new index has already played a role, but it still scans the clustered index of the primary key. If it can complete the query on an index, the performance will be higher, because this query

Further optimization is considered:

7. The Code is as follows:
Create index name_index4 on site_user (name, code, [date])
Table 'site _ user '. 1 scan count, 3 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.


-- The number of logical reads on the disk has dropped.

View the execution plan:

In this way, the index search is much faster.

8. The Code is as follows:
Create index name_index5 on site_user (name) include (id, code, [date]) Table 'site _ user '.
1 scan count, 3 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

-- The number of logical reads to the disk does not change significantly. Then, view the execution plan:

The Code is as follows:
DBCC SHOW_STATISTICS ('site _ user', 'name _ index4 ')
DBCC SHOW_STATISTICS ('site _ user', 'name _ index5 ')

We can see that the same data volume, average key length: covers the index index5 and occupies less space. Therefore, we should prioritize overwriting indexes for optimization.
In view of this so easy article, you can mention more

Author: gaobanana
Source: http://www.cnblogs.com/gaobanana