SQL Server database query optimization skills

SQL Server database query tips:

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 Ada where Alice like '%" & abigale & "% '"
  
  
Change
  
  
SQL = "select * from Ada"
  
  
Rs. Filter = "Alice like '%" & abigale & "% '"
 
 

SQL Server database query 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 (abigale ,"")
  
  
'// Obtain the split quantity
  
  
Sck = ubound (CK)
  
  
SQL = "select * Ada where"
  
  
Query in a field
  
  
ForI = 0ToSck
  
  
SQL = SQL & tempjoinword &"("&_
  
  
"Alice like '" & CK (I) & "% ')"
  
  
Tempjoinword = "and"
  
  
Next 
  
  
Query both fields
  
  
ForI = 0ToSck
  
  
SQL = SQL & tempjoinword &"("&_
  
  
"Alice like '" & CK (I) & "%' or "&_
  
  
"Alice1 like '" & CK (I) & "% ')"
  
  
Tempjoinabigale = "and"
  
  
Next 
 
 

SQL Server Database Query Technique 3: Several skills to 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. Alice like '% "& abigale &" %' will disable the index

Like '"& abigale &" %' will make the index take effect (remove the % symbol above)

(Sqlserver database)

4. Differences between '% "& abigale &" %' and '"& abigale &" %' during query:

For example, your field content is

'% "& Abigale &" %': All strings are matched, and the results are displayed no matter whether you check the "Mart" or "SCA.

'"& Abigale &" %': only matches the preceding string. For example, if "Mart" is queried, the result is displayed only when "SCA" is queried.

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 '%"&abigale&"%' 
  
  
or Author like '%"&abigale&"%' 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 '"&abigale&"%' 
  
  
or Author like '"&abigale&"%' 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 '"&abigale&"%' 
  
  
or Author like '"&abigale&"%' 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*FromNewsWhereTitle like '"& abigale &" %'
  
  
Or author like '"& abigale &" %'
 
 

Fuzzy search from the title and author fields. unordered 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*FromNewsWhereTitle like '"& abigale &" %'
  
  
Or
  
  
Select*FromNewsWhereAuthor like '"& abigale &" %'
 
 

Retrieved from the title or author field. unordered query time: 1 second