1. Avoid using incompatible data types. For example, Float and Int, CHAR and VARCHAR, BINARY and VARBINARY are incompatible. The incompatibility of data types may enable optimizers to perform some optimized operations that can be performed. E.g:
Select Name from Employee Where Salary> 60000
In this statement, if the Salary field is Money type, the optimizer is difficult to optimize because 60000 is an integer. We should transform the integer when programming, not to wait until running.
2. Avoid using other mathematical operators for search parameters, if necessary
Select Name from Employee Where Substring (ID, 1, 1) = 'B'
SELECT NAME FROM EMPLYEE WHERE SALY * 12> 30000
Write a write:
SELECT NAME FROM EMPLOYEE WHERE ID LIKE 'B%'
Select Name from Emplye WHERE SALY> 3000
3, avoid use! = Or <>, etc., because this will make the system unable to use the index, but only directly search the table. E.g:
SELECT ID from Employewhere ID! = 'B%'
The optimizer will not be able to determine the number of rows that will hit by claim, so you need to search for all rows of the table.
4. In the application, ensure that the number of access to the database is guaranteed to reduce the number of access to the database; through search parameters, try to reduce the number of rows of the table, minimize the result set, thereby reducing the network burden; Try to be processed separately, improve each response speed; when using SQL in the data window, try to put the used index in the selected first column; the structure of the algorithm is as simple as possible; do not use too much wildcard such as SELECT * statement when query Try not to use the database cursor in your application, the cursor is a very useful tool, but it is more expensive than the use of conventional, viewed SQL statements, and extracts data in a specific order.
Above we mentioned that some basic improvement of query speeds, but in more cases, programmers often need to repeated test compare different statements to get the best solution. In addition, more important is that the database administrator needs to adjust the parameters of the database management system at one end of the server to obtain faster response performance.
