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

In this issue, the editor will bring you more information about how to optimize the database. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Optimization of query

Ensure that on the basis of realizing the function, minimize the number of visits to the database; through searching parameters, minimize the number of rows accessed to the table and minimize the result set, so as to reduce the network burden; separate operations that can be handled separately as far as possible to improve the response speed of each time; when using SQL in the data window, try to put the index in the first column of the choice; the structure of the algorithm is as simple as possible In the query, do not use too many wildcard characters such as SELECT*FROMT1 statement, to use a few columns, select a few columns such as: SELECTCOL1,COL2FROMT1; as far as possible to limit the number of result set rows such as: SELECTTOP300COL1,COL2,COL3FROMT1, because in some cases users do not need so much data.

In the case of no indexing, when the database looks for a certain piece of data, it must scan the whole table and traverse all the data to find out the records that meet the conditions. In the case of a small amount of data, there may be no obvious difference, but when the amount of data is large, the situation is extremely bad.

How SQL statements are executed in SQLSERVER, they are worried that the SQL statements they write will be misunderstood by SQLSERVER. For example:

Select * from table1 where name = 'zhangsan'and tID > 10000

And execution:

Select * from table1 wheret ID > 10000 and name = 'zhangsan'

Some people do not know whether the execution efficiency of the above two statements is the same, because if you simply look at the sequence of the statements, the two statements are indeed different, if tID is an aggregate index, then the latter sentence will only look up the records after 10000 items of the table; while the former sentence should first look for several name='zhangsan' in the whole table, and then propose the query results according to the constraint condition tID > 10000.

In fact, such worries are unnecessary. There is a "query analysis optimizer" in SQLSERVER that calculates the search conditions in the where clause and determines which index reduces the search space for table scans, that is, it optimizes automatically. Although the query optimizer can automatically optimize queries based on the where clause, sometimes the query optimizer does not query quickly as you intended.

During the query analysis phase, the query optimizer looks at each stage of the query and decides whether it is useful to limit the amount of data that needs to be scanned. If a phase can be used as a scan parameter (SARG), it is called optimizable, and the required data can be quickly obtained using the index.

The above is the editor for you to share a large amount of database how to optimize, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.