Shulou(Shulou.com)05/31 Report--

SqlServer execution plan and Sql query optimization example analysis, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

When it comes to optimization, it is necessary to involve the index, just as if you want to talk about the lock, you have to talk about the transaction, so you need to know about the index. just the index, you can talk about it for a long time, so I won't talk about the index (typing a lot of words is very tiring. And I know very little), you can refer to the relevant articles, this online information is more.

Today, let's explore the execution plan of MSSQL to let you know how to view the optimization mechanism of MSSQL to optimize SQL queries.

-- DROP TABLE titled UserInfoMurray-

-- build a test table

CREATETABLET_UserInfo

(

Userid varchar (20), UserName varchar (20)

RegTimedatetime,Tel varchar (20)

)

-- insert test data

DECLARE@IINT

DECLARE@ENDIDINT

SELECT@I=1

SELECT@ENDID = 100PUE-change the data to be inserted here and delete all data before reinserting

WHILE@I= 'ABCDE8' AND [T_UserInfo]. [Userid] <' ABCDE9'), WHERE: (like ([T_UserInfo]. [Userid], 'ABCDE8%', NULL)) ORDERED FORWARD) Ctrl+L take a look at the graph line execution plan at this time:

Ctrl+L takes a look at the graph line execution plan at this time:

With an index, let's write another SQL:

SETSHOWPLAN_ALLON

SELECT*FROMT_UserInfoWHERELEFT (USERID,4) = 'ABCDE8%'

View StmtText:

SELECT*FROMT_UserInfoWHERELEFT (USERID,4) = 'ABCDE8%'

|-- Clustered Index Scan (OBJECT: ([student]. [dbo]. [T _ UserInfo]. [INDEX_Userid]), WHERE: (substring ([T_UserInfo]. [Userid], 1,4) = 'ABCDE8%')

Ctrl+L takes a look at the graph line execution plan at this time:

Let's take a look at the operation of IO in three cases.

They are as follows:

The first case is the table 'Tunable UserInfo'. Scan count 1, logic read 7 times, physical read 0 times, pre-read 0 times.

The second case: table 'Tunable UserInfo'. Scan count 1, logic read 3 times, physical read 0 times, pre-read 0 times.

The third case: table 'Tunable UserInfo'. Scan count 1, logic read 8 times, physical read 0 times, pre-read 0 times.

This shows that:

The first time was a table scan, which scanned 7 pages, that is, a full table scan

The second time is an index scan, scanning 1 page of index and 2 pages of data.

The third time is index scan + table scan, scanning 1 page of index and 7 pages of data

[the graphical interface also consumes CPU and IO, and you can see which one is the best!]

By comparison, hey, it is easy to see: the second and third writing in the case of an index, like effectively uses the index, while left can not, such a simple example of optimization comes out, .

If you understand all of the above, then you may have a preliminary new idea for SQL optimization. Is that really the case with piles of SQL optimization articles on the Internet? Try it yourself, and you don't have to remember those things blindly. Try it yourself and see how MSSQL is implemented.

In my example, using a clustered index scan, the fields are letters plus numbers, you can try pure numbers, letters, Chinese characters, and so on, to see how MMSQL will change the SQL statement to take advantage of the index. Then try the nonclustered index again. What does it have to do with using an index or not? How is the subquery MSSQL executed? Does IN use an index? does LIKE use an index? Does the function use an index? OR 、 AND 、 UNION? What about sub-queries? Here I will not try to show you one by one, as long as you know how to see the MSSQL implementation plan (graphics and text), a lot of things will be very clear.

There may be many ways to write SQL to achieve the same query function. If we judge which kind of optimization, if we only measure it in terms of time, it will be affected by many external factors, and we understand how to execute MSSQL. The real way to optimize SQL is to read through IO logic, to view the query plan of the diagram, and to execute SQL statements after optimization.

Another reminder: the amount of data sometimes affects MSSQL's execution plan for the same query writing statement, which is particularly obvious on the nonclustered index, and in the case of multiple CPU and single CPU, the query statement execution plan of the same writing method will be different.

After reading the above, have you mastered the method of SqlServer execution plan and sample analysis of Sql query optimization? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.