Tips for optimizing database queries

Tips for optimizing database queries

The difference between an effective optimization and an unoptimized or incorrect optimization may make your program execution speed dozens or even hundreds of times different.

(This article is not suitable for viewing by database experts. If you have any errors, do not criticize and correct them .)

The following word indicates the query string, tablename indicates the data table name, and column indicates the field name.

========================================================== ==============================

Tip 1:

Problem type: When the ACCESS database field contains a Japanese Katakana or other unknown characters, the query will prompt a memory overflow.

Solution: Modify the query statement.

SQL = "select * From tablename where column like '%" & word & "% '"
Change
SQL = "select * From tablename"
Rs. Filter = "column like '%" & word & "% '"

========================================================== ==============================

Tip 2:

Problem type: how to use a simple method to implement multi-Keyword queries similar to Baidu (Separate multiple keywords with spaces or other symbols ).

Solution:

'// Use spaces to separate query strings
Ck = Split (word ,"")
'// Obtain the split quantity
Sck = ubound (CK)

SQL = "select * tablename where"

Query in a field
For I = 0 to sck
SQL = SQL & tempjoinword &"("&_
"Column like '" & CK (I) & "% ')"
Tempjoinword = "and"
Next

Query both fields
For I = 0 to sck
SQL = SQL & tempjoinword &"("&_
"Column like '" & CK (I) & "%' or "&_
"Column1 like '" & CK (I) & "% ')"
Tempjoinword = "and"
Next

========================================================== ==============================

Tip 3: several tips to greatly improve query efficiency

1. Try not to use or. Using or will cause full table scanning, which will greatly reduce the query efficiency.

2. After practical verification, charindex () does not improve the query efficiency better than the preceding % like, And charindex () will make the index ineffective (referring to sqlserver database)

3. Column like '% "& word &" %' will disable the index
Column like '"& word &" %' will make the index take effect (remove the % symbol above)
(Sqlserver database)

4. Differences between '% "& word &" %' and '"& word &" %' during query:
For example, the content of your field is a vulnerable woman.
'% "& Word &" %': All strings are matched, and the result is displayed no matter "injured" or "one.
'"& Word &" %': only matches the preceding string. For example, if "injured" is checked, no result is returned. Only "one" is displayed.

5. field extraction should follow the principle of "how much is required and how much is requested" to avoid "select *". Try to use "select Field 1, Field 2, Field 3 ........ ". Practice has proved that the data extraction speed will be improved for every few fields extracted. The speed of improvement depends on the size of the discarded field.

6. Order by is the most efficient sorting by clustered index columns. Only one clustered index can be created for one sqlserver data table. The default value is ID. You can also change it to another field.

7. Create an appropriate index for your table. Creating an index can increase your query speed by dozens or hundreds of times. (Sqlserver database)

========================================================== ==============================

The following is a query efficiency analysis between index creation and no index creation:

SQL Server Index and query efficiency analysis.

Table news

Field
ID: Automatic ID
Title: article title
Author: Author
Content: Content
Star: Priority
Addtime: Time

Record: 1 million
Test Machine: P4 2.8/1 GB memory/IDE Hard Drive

========================================================== ====================

Solution 1:
Primary Key ID. The default value is clustered index. No other non-clustered indexes are created.
Select * from news where title like '% "& word &" %' or author like '% "& word &" % 'order by ID DESC

Fuzzy search by field title and author, sorted by ID
Query time: 50 seconds

========================================================== ====================

Solution 2:
Primary Key ID. The default value is clustered index.
Create a non-clustered index on title, author, and star
Select * from news where title like '"& word &" %' or author like '"& word &" % 'order by ID DESC

Fuzzy search by field title and author, sorted by ID
Query time: 2-2.5 seconds

========================================================== ====================

Solution 3:
Primary Key ID. The default value is clustered index.
Create a non-clustered index on title, author, and star
Select * from news where title like '"& word &" %' or author like '"& word &" % 'order by star DESC

Fuzzy search from the title and author fields, sorted by star
Query time: 2 seconds

========================================================== ====================

Solution 4:
Primary Key ID. The default value is clustered index.
Create a non-clustered index on title, author, and star
Select * from news where title like '"& word &" %' or author like '"& word &" %'

Fuzzy search from the title and author fields without sorting
Query time: 1.8-2 seconds

========================================================== ====================

Solution 5:
Primary Key ID. The default value is clustered index.
Create a non-clustered index on title, author, and star
Select * from news where title like '"& word &" %'
Or
Select * from news where author like '"& word &" %'

Retrieve from field title or author, not sorted
Query time: 1 second

========================================================== ======

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 658861