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

This article mainly introduces "how to optimize SQL statements". In daily operation, I believe many people have doubts about how to optimize SQL statements. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "how to optimize SQL sentences". Next, please follow the editor to study!

1. Do not check redundant columns

In order to save trouble, we often use * instead of all the columns in the query process. The advantage is that there is no need to write explicit columns. The disadvantage is that the query efficiency of tables with more columns is greatly reduced. For example:

SELECT * FROM [Sales]. [SalesOrderDetail]

If we execute it, we can see the following information:

Q: how did the above message interface come out?

A: click query on the menu bar-- query options.-- Advanced, just check both SET STATISTICS TIME and SET STATISTICS IO.

Let me interpret the relevant information above:

Scan count: index or table scan times

Logical reads: the number of pages read in the data cache

Physical reads: number of pages read from disk

Read-ahead: the number of pages cached from disk during a query

Lob logical read: the number of pages read from the data cache, image,text,ntext or large data

Lob physical reads: the number of pages read from disk, image,text,ntext or large data

Lob read-ahead: the number of pages of image,text,ntext or large data cached from disk during a query

The CPU time of the statement is divided into the compilation phase and the execution phase.

CPU time refers to the time the statement was executed

Occupancy time refers to the total usage time of reading data from disk and then processing.

Compilation phase:

SQL Server analysis and compile time:

Execution phase:

SQL Server execution time:

We will often see this information in the future, which is the most intuitive way to judge a query statement.

What happens if we check another column separately? For example:

SELECT UnitPrice FROM [Sales]. [SalesOrderDetail]

If we execute it, we can see the following information:

Through the time comparison above, we can see clearly: the efficiency of listing column names clearly and not showing irrelevant columns is greatly improved.

Here is a piece of optimization advice that you often see: do not directly use * to query, but only query the columns you need.

Of course, if you need to view the contents of the whole table, it will not be included in this optimization.

2. Do not check superfluous lines

Use the WHERE keyword when querying to filter out unwanted rows. This is also a way to improve query efficiency, in fact, this is the meaning of the existence of the WHERE keyword.

For example:

SELECT UnitPrice FROM [Sales]. [SalesOrderDetail] WHERE UnitPrice > 1000

If we execute it, we can see the following information:

The time taken is reduced exponentially, and the effect is obvious.

Use the DISTINCT keyword to reduce redundant lines.

For example:

SELECT DISTINCT UnitPrice FROM [Sales]. [SalesOrderDetail] WHERE UnitPrice > 1000

If we execute it, we can see the following information:

We found that in some cases, DISTINCT can also speed up the efficiency of data query.

At this point, the study of "how to optimize SQL statements" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.