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

In view of how to optimize the query in Server database, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

What are the query optimization methods of Server database

1. Try not to use or, the use of or will cause a full table scan, which will greatly reduce the query efficiency.

two。 It has been proved in practice that charindex () does not improve the query efficiency better than the previous addition of% like, and charindex () will make the index useless (refers to sqlserver database).

3. Alicelike% "& abigale&"% 'will invalidate the index

Like' "& abigale&"% 'will cause the cable to work (remove the preceding% sign)

(refers to sqlserver database)

4. The difference between'& abigale& 'and' & abigale& 'when querying:

For example, your field reads: Scarborough Fair

'%' & abigale&'%': all strings will be matched, regardless of whether you look up "bazaar" or "ska", the result will be displayed.

'& abigale&'%': only match the preceding string. For example, there is no result in checking "bazaar". Only by checking "Ska" will the result be displayed.

5. Field extraction should follow the principle of "how much you need, how much you need", avoid "select*", and try to use "select field 1, field 2, field 3." Practice has proved that: each less to extract a field, the data extraction speed will be correspondingly improved. The speed of improvement depends on the size of the field you discard.

6.orderby is most efficient in sorting by clustered index column. A sqlserver data table can only build one clustered index, which is generally defaulted to ID, or it can be changed to other fields.

7. Building an appropriate index for your table can increase your query speed by tens of thousands of times. (refers to sqlserver database)

The following is a query efficiency analysis of indexing and non-indexing:

Sqlserver index and query efficiency analysis.

Table News

Field

Id: automatic numbering

Title: article title

Author: author

Content: content

Star: priority

Addtime: time

Record: 1 million

Test machine: P42.8 Compact 1G memory / IDE hard disk

What are the query optimization methods of Server database

Option 1:

The primary key Id, which defaults to clustered index, does not create other nonclustered indexes

The following is the referenced content:

Fuzzy retrieval from fields Title and Author, sorted by Id

Query time: 50 seconds

Option 2:

Primary key Id, which defaults to clustered index

Establishing nonclustered indexes on Title, Author and Star

The following is the referenced content:

Fuzzy retrieval from fields Title and Author, sorted by Id

Fuzzy retrieval from fields Title and Author, sorted by Star

Query time: 2 seconds

Option 4:

Primary key Id, which defaults to clustered index

Establishing nonclustered indexes on Title, Author and Star

The following is the referenced content:

Select*fromNewswhereTitlelike' "& abigale&"% 'orAuthorlike' "& abigale&"%'

Fuzzy retrieval from fields Title and Author, unsorted query time: 1.8-2 seconds

Option 5:

Primary key Id, which defaults to clustered index

Establishing nonclustered indexes on Title, Author and Star

The following is the referenced content:

Select*fromNewswhereTitlelike' "& abigale&"%'

Or

Select*fromNewswhereAuthorlike' "& abigale&"%'

Retrieve from field Title or Author, unsorted query time: 1 second

This is the answer to the question about how to optimize the query in the Server database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.