My program is slow to query the database. How to improve the query speed?

1. In order to optimize the query, we should avoid scanning the whole table as much as possible. First, we should consider building indexes on the columns involved in where and order by.

2. Try to avoid judging the null value of the field in the where clause, otherwise the engine will give up using the index and scan the whole table, for example:

Select the id from t, where num is empty.

You can set the default value of num to 0 to ensure that the num column in the table has no null value, and then query it like this:

Select id from t, where num=0.

3. Try to avoid using it in the where clause! = or

4. Try to avoid using the or join condition in the where clause, otherwise the engine will give up using the index and scan the whole table, for example:

Select the id from t, where num= 10 or num=20.

You can query like this:

select id from t where num= 10

Joint ownership

Select id from t, where num=20.

5.in and not in should also be used with caution, otherwise it will lead to full table scanning, such as:

select id from t where num in( 1，2，3)

For continuous values, you can use between instead of in:

Select the id from t, where num is between 1 and 3.

6. The following query will also result in a full table scan:

Select the id from t, where the name is "%abc%".

In order to improve efficiency, full-text retrieval can be considered.

7. If parameters are used in the where clause, it will also lead to a full table scan. Because SQL only parses local variables at runtime, the optimizer cannot postpone the selection of access plan until runtime; You must select it at compile time. However, if the access plan is established at compile time, the value of the variable is still unknown, so it cannot be used as an input item for index selection. The following statement scans the entire table:

select id from t where num=@num

You can force the query to use an index:

Select id from t with(index) where num=@num.

8. Try to avoid expression operations on fields in the where clause, which will cause the engine to give up using indexes and scan the whole table. For example:

Select id from t where num/2= 100.

It should read:

select id from t where num = 100 * 2

9. Function operations on fields in the where clause should be avoided as much as possible, which will cause the engine to abandon the use of indexes and scan the whole table. For example:

Select ID from where substring (name, 1, 3)=' abc '- name id starting with ABC.

Select id from where datediff (date of creation,' 2005-11-30') = 0-'2005-11-30'.

It should read:

Select the id from t, where the name is "abc%".

select id from t where create date & gt； ='2005- 1 1-30' and create date <' 2005-12-1'

10. Do not perform functions, arithmetic operations or other expression operations on the left side of "=" in the where clause, otherwise the system may not use the index correctly.

1 1. When the index field is used as a condition, if the index is a composite index, the first field in the index must be used as a condition to ensure that the index is used by the system, otherwise the index will not be used, and the field order should be as consistent as possible with the index order.

12. Don't write some meaningless queries. If you need to generate an empty table structure:

Select col 1, col2 into #t from t where 1=0.

This kind of code will not return any result set, but it will consume system resources. It should be changed to this:

Create table #t (...)

13. It is a good choice to use exists instead in many cases:

Select the number from A, where the number is in (Select the number from B).

Replace with the following statement:

select num from a where exists(select 1 from b where num = a . num)

14. Not all indexes are valid for the query. SQL optimizes the query according to the data in the table. When there is a lot of duplicate data in the index column, the SQL query may not use the index. For example, if there are almost half of the fields in a table, men and women, even if the index is based on gender, it will not play a role in query efficiency.

15. The more indexes, the better. Although the index can improve the efficiency of corresponding selection, it will also reduce the efficiency of insertion and update. Because the index may be rebuilt during insertion or update, how to build the index needs to be carefully considered according to the specific situation. The number of indexes in a table should not exceed 6. If there are too many indexes, consider whether it is necessary to establish indexes on some columns that are not commonly used.

16. Updating clustered index data columns should be avoided as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column values change, the order of the whole table records will be adjusted, which will consume considerable resources. If the application system needs to update the clustered index data columns frequently, it is necessary to consider whether the index should be built as a clustered index.

17. Try using a numeric field. If the field only contains numerical information, try not to design it as characters, which will reduce the performance of query and connection and increase the storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections, but only one comparison is enough for the number type.

18. Use varchar/nvarchar instead of char/nchar as much as possible, because firstly, the storage space of variable-length fields is small, which can save storage space, and secondly, for queries, the search efficiency is obviously higher in relatively small fields.

19. Don't use select * from t anywhere, replace "*" with a specific field list, and don't return any unnecessary fields.

20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, please note that the index is very limited (only the primary key index).

2 1. Avoid creating and deleting temporary tables frequently to reduce the consumption of system table resources.

22. Temporary tables are not unavailable. Using them correctly can make some routines more effective, for example, when it is necessary to repeatedly refer to data sets in large tables or public tables. However, for one-time events, it is best to use export tables.

23. When creating a temporary table, if you insert a large amount of data at a time, you can use select into instead of create table to avoid creating a large number of logs and improve the speed; If the amount of data is not large, in order to reduce the resources of system tables, tables should be created first and then inserted.

24. If temporary tables are used, all temporary tables must be explicitly deleted at the end of the stored procedure. truncate table table first and then drop table table to avoid locking the system table for a long time.

25. Try to avoid using cursors, because cursors are inefficient. If the data of cursor operation exceeds 654.38+0 million rows, then it is necessary to consider rewriting.

26. Before using the cursor-based method or temporary table method, we must first find a set-based method to solve the problem, and the set-based method is usually more effective.

27. Like temporary tables, cursors are not unavailable. Using FAST_FORWARD cursor for small data sets is usually superior to other line-by-line processing methods, especially when multiple tables must be referenced to obtain the required data. A routine that contains "Total" in the result set is usually faster than using a cursor. If development time permits, both cursor-based method and set-based method can be tried to see which method works better.

28. Set SET NOCOUNT ON at the beginning of all stored procedures and triggers, and set set SET NOCOUNT OFF at the end. There is no need to send the DONE_IN_PROC message to the client after executing each statement of the stored procedure and trigger.

29. Try to avoid big transaction operations and improve system concurrency.

30. Try to avoid returning a large amount of data to the client. If the amount of data is too large, it is necessary to consider whether the corresponding requirements are reasonable.

1. Avoid setting this field to Allow Null.

2, data table design to specification.

3. In-depth analysis of data operations on the database.

4. Try not to use temporary tables.

5. Use more transactions

6. Try not to use cursors.

7. Avoid deadlock

8. Pay attention to the use of read-write locks.

9. Don't open large data sets.

10. Do not use the server-side cursor.

1 1. Use a large database when writing programs.

12. Do not create an index for the gender column.

13, pay attention to the timeout problem.

14, do not use Select *

15. When inserting records in the schedule, do not execute Select MAX(ID) in the main table.

16. Try not to use the text data type.

17, using parameter query

18, don't import a lot of data with Insert.

19, learn to analyze and query.

20. Use referential integrity

2 1, replace Where with inner join and left join.

Improve the efficiency of SQL query (key points and skills);

Hint 1:

Question type: When an ACCESS database field contains Japanese katakana or other unknown characters, the query will prompt memory overflow.

Solution: Modify the query statement.

SQL = " select * from tablename where column like“%”& amp； Word & amp%' "

replace

sql="select * from tablename "

Rs.filter = "columnlike'%" & word & amp%' "

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

Tip 2:

Question type: How to realize multi-keyword query similar to Baidu in a simple way (multiple keywords are separated by spaces or other symbols).

Solution:

//Split the query string with spaces

Ck=split (word, "")

//Get the number divided by.

Sck = underload (ck)

sql="select * tablename where "

Query in field

For i = 0 to sck

SQL = SQL & amptempJoinWord & amp"(" & amp_

"column image" & ampck (1) & amp%') "

tempJoinWord = " and "

then

Query in two fields at the same time

For i = 0 to sck

SQL = SQL & amptempJoinWord & amp"(" & amp_

"column image" & ampck (1) & amp“% "or" &

" column 1 like " & amp； Ck (1) & amp%') "

tempJoinWord = " and "

then

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

Tip 3: Several tips to greatly improve the query efficiency.

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

2. Practice has proved that charindex () will improve the query efficiency by no more than% like, and charindex () will make the index useless (refer to sqlserver database).

3. Columns like "%"&the word "%"will make the index useless.

Words like ""& "%"will make the index valid (remove the preceding% symbol).

(refers to sqlserver database)

4.% "& words & amp“%" and "&; Difference of%' in the word & query:

For example, your field content is a fragile woman.

% "& word & amp“%": All strings will be wildcards, and the result will be displayed regardless of "injured" or "one".

& word & amp“%': Only the preceding string is a wildcard. For example, the search for "injury" has no results, and only the search for "one" will show the results.

5. The field extraction should follow the principle of "take as much as needed", avoid "select *", and try to use "select field 1, field 2, field 3 ...". Practice has proved that every time a field is extracted less, the speed of data extraction will increase accordingly. The speed of promotion depends on the size of the field you discard.

6.order by is the most efficient sorting by clustered index columns. Only one clustered index can be established for a sqlserver data table, which is usually ID by default, and can also be changed to other fields.

7. Establishing an appropriate index for your table can improve your query speed by dozens or even hundreds of times. (refers to sqlserver database)

The following is an analysis of query efficiency with and without indexes:

Analysis of Sqlserver index and query efficiency.

Table news

field

Id: automatic numbering

Title: the title of the article

Author: author

Content: content

Asterisk: priority

Add time: time

Record: 654.38+0 million.

Test machine: P4 2.8/ 1G memory /IDE hard disk.

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

Scheme 1:

Primary key Id, which is a clustered index by default. No other nonclustered indexes will be created.

select * from News where Title like“%”& amp； Author of the word & "%"or similar "%"& Word & "%"is sorted by Id, desc.

Fuzzy retrieval from title and author fields, sorted by Id.

Query time: 50 seconds

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

Option 2:

Primary key Id, which defaults to clustered index.

Create a nonclustered index of titles, authors and asterisks.

select * from News where Title like ' " & amp； The word &%' or the author likes' "&the word &%' is sorted by Id, desc.

Fuzzy retrieval from title and author fields, sorted by Id.

Query time: 2-2.5 seconds

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

Option 3:

Primary key Id, which defaults to clustered index.

Create a nonclustered index of titles, authors and asterisks.

select * from News where Title like ' " & amp； The word &%' or the author likes' "&the word &%" is sorted by star desc.

Fuzzy retrieval of title and author fields, sorted by asterisk.

Query time: 2 seconds

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

Option 4:

Primary key Id, which defaults to clustered index.

Create a nonclustered index of titles, authors and asterisks.

select * from News where Title like ' " & amp； The word &%' or the author likes' "&the word &%'

Fuzzy retrieval from title and author fields, without sorting.

Query time: 1.8-2 seconds

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

Option 5:

Primary key Id, which defaults to clustered index.

Create a nonclustered index of titles, authors and asterisks.

select * from News where Title like ' " & amp； Word & amp%'

or

Choose from the author's favorite news. Word & amp%'

Retrieve from the field title or author, without sorting.

Query time: 1 sec

How to improve the query efficiency of SQL language?

Q: How to improve the query efficiency of SQL language?

A: This has to start from the beginning:

Because SQL is a result-oriented query language rather than a process-oriented query language, large relational databases that usually support SQL use an optimizer based on query cost to provide the best execution strategy for real-time queries. For the optimizer, the input is the query statement and the output is the execution strategy.

An SQL query statement can have multiple execution strategies, and the optimizer will estimate the so-called lowest-cost method that takes the least time among all the execution methods. All optimizations are based on the where clause in the query statement used by the user, and the optimizer mainly uses the Serach parameter in the where clause for optimization.

The core idea of search parameters is that the database uses the index of the fields in the table to query the data, rather than directly querying the data in the records.

With =，

Emp_id = "1000 1" or salary > 3000 or a = 1 and c = 7.

The following are not search parameters:

Salary = emp_salary or dep_id! = 10 or salary * 12 > = 3000 or a= 1 or c=7.

We should try to provide some redundant search parameters to give the optimizer more choices. Please look at the following three methods:

The first method:

select employee.emp_name，department . dep _ name from department， employee where(employee . dep _ id = department . dep _ id)and(department . dep _ code = " 0 1 ")and(employee . dep _ code = " 0 1 ")；

The search analysis results are as follows:

Estimate 2 I/O operations.

Scan departments with primary key

For lines with dep_code equal to "0 1"

Estimated arrival here 1 time

Scan employees in sequence

It is estimated that it will arrive here five times.

The second method:

select employee.emp_name，department . dep _ name from department，employee where(employee . dep _ id = department . dep _ id)and(department . dep _ code = " 0 1 ")；

The search analysis results are as follows:

Estimate 2 I/O operations.

Scan departments with primary key

For lines with dep_code equal to "0 1"

Estimated arrival here 1 time

Scan employees in sequence

It is estimated that it will arrive here five times.

The first method is as effective as the second method, but the first method is the best because it provides more choices for the optimizer.

The third method:

select employee.emp_name，department . dep _ name from department，employee where(employee . dep _ id = department . dep _ id)and(employee . dep _ code = " 0 1 ")；

This method is the worst because it cannot use indexes, that is, it cannot be optimized. ...

When using SQL statements, please note the following points:

1. Avoid using incompatible data types. For example, Float and Integer, Char and Varchar, Binary and Long Binary are incompatible. Incompatibility of data types may prevent the optimizer from performing some optimization operations that could have been completed. For example:

Select emp_name form employees, in which salary > 3000;

In this statement, if salary is a Float type, it is difficult for the optimizer to optimize it, because 3000 is an integer, so we should use 3000.0 in programming instead of waiting for DBMS to convert it at runtime.

2. Try not to use expressions, because it can't be obtained at compile time, so SQL can only use its average density to estimate the number of records that will hit.

3. Avoid using other mathematical operators for search parameters. For example:

select EMP _ name from employee where salary * 12 & gt； 3000;

It should read:

Select emp_name from employee where salary & gt250;

4, avoid using! = or

Oral application

A160,000 data table-SMS uplink table TBL _ SMS _ Mo

Structure:

Create table TBL_SMS_MO

(

SMS_ID number,

MO_ID VARCHAR2(50)，

Move VARCHAR2( 1 1),

SPNUMBER VARCHAR2(20)，

Message VARCHAR2( 150),

TRADE_CODE VARCHAR2(20)，

LINK_ID VARCHAR2(50)，

Gateway identification number,

Gateway port number,

MO_TIME date default system date

);

Create index IDX month day on TBL month day.

PCTFREE 10

INITRANS 2

MAXTRANS 255

Storage; stock

(

Initial 1M

Next 1M

MINEXTENTS 1

MAXEXTENTS unlimited

Percentage increase of 0

);

Create IDX mobile phone index on TBL mobile phone

PCTFREE 10

INITRANS 2

MAXTRANS 255

Storage; stock

(

Initial 64K

Next 1M

MINEXTENTS 1

MAXEXTENTS unlimited

Percentage increase of 0

);

Question: Query the SMS sent by the mobile phone within a certain period of time from the table, as shown in the following SQL statement:

Select mobile phone, message, transaction code and time.

A text message from TBL

Where mobile ='130xxxxxxxxx'

And mo _ between the date ('2006-04-0 1',' YYYY-MM-DD HH24:MI:SS') and the date ('2006-04-07',' YYYY-MM-DD HH24:MI:SS').

In DESC chronological order

It takes about 10 minutes to return the results, which is unbearable for web queries.

Analysis:

In PL/SQL Developer, click "Explain Plan" button (or F5 key) to analyze SQL, and find that the default index is IDX _ Mo _ Date. This may be the problem, because compared with the total data of16 million, the data of Du Mobile is very small, and it is easier to lock the data with _MO_MOBILE.

The optimization is as follows:

SELECT/*+index(TBL _ SMS _ Mo IDX _ Mo _ Mobile) */Mobile phone, message, transaction code, Mo _ time.

A text message from TBL

Where mobile ='130xxxxxxxxx'

And mo _ between the date ('2006-04-0 1',' YYYY-MM-DD HH24:MI:SS') and the date ('2006-04-07',' YYYY-MM-DD HH24:MI:SS').

In DESC chronological order

Test:

Press F8 to run this SQL, wow ~......2.360s, that's the difference.

blogs . com/ShaYeBlog/archive/20 13/07/3 1/3227244 . html