SQL statement optimization introduction and SQL statement Optimization

SQL statement optimization introduction and SQL statement Optimization

1. To optimize the query, try to avoid full table scanning. First, consider creating an index on the columns involved in where and order.

2. Try to avoid null value determination on the field in the where clause. When creating a table, NULL is the default value, but not null should be used in most cases, or a special value, such as 0, -1 is used as the default value.

3. Try to avoid using it in the where clause! = Or <> operator. MySQL only uses indexes for the following operators:<, <=, =,>,> =, BETWEEN, INAnd sometimes LIKE.

4. Try to avoid using or in the where clause to connect to the condition. Otherwise, the engine will discard the index and perform full table scan. You can use UNION to merge queries: select id from t where num = 10 union all select id from t where num = 20

5, in and not in should also be used with caution, otherwise it will cause a full table scan. For continuous values, use between instead of using in: Select id from t where num between 1 and 3

6. The following query will also cause a full table scan: select id from t where name like '% abc %' or select id from t where name like '% abc' to improve efficiency, you can consider full-text retrieval. The index is used only when select id from t where name like 'abc %'

7. If a parameter is used in the where clause, a full table scan is also performed.

8. Avoid expression operations on fields in the where clause, and avoid function operations on fields in the where clause.

9. in many cases, replacing in with exists is a good choice: select num from a where num in (select num from B ). replace with the following statement: select num from a where exists (select 1 from B where num =. num)

10. indexes can certainly improve the efficiency of the select statement, but also reduce the efficiency of insert and update, because the insert or update statements may recreate the index, therefore, you need to carefully consider how to create an index, depending on the actual situation. It is recommended that the number of indexes in a table be no more than 6. If there are too many indexes, consider whether the indexes on some columns that are not frequently used are necessary.

11. Do not update the clustered index data column as much as possible, because the order of the clustered index data column is the physical storage order of the table records, once the column value changes, the sequence of the entire table record will be adjusted, which will consume considerable resources. If the application system needs to frequently update the clustered index data column, consider whether to create the index as a clustered index.

12. use numeric fields whenever possible. If fields containing only numerical information are not designed as numeric fields, this will reduce query and connection performance and increase storage overhead.

13. Try to replace char/nchar with varchar/nvarchar, because the storage space of the variable-length field is small, which can save storage space. Secondly, for queries, searching in a relatively small field is obviously more efficient.

14. It is best not to use "*" to return all: select * from t. Instead of "*" with a specific field list, do not return any fields that are not used.

15. Avoid returning large data volumes to the client whenever possible. If the data volume is too large, consider whether the appropriate requirements are reasonable.

16. Use the table Alias (Alias ):

When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column. in this way, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced. 17, Use "temporary table" to save intermediate results

An important way to simplify SQL statements is to use temporary tables to store intermediate results. However, temporary tables have far more benefits than this. Temporary results are saved to temporary tables, the subsequent query is in tempdb, which can avoid scanning the master table multiple times in the program. It also greatly reduces the blocking of the "Update lock" in program execution and reduces blocking, improves concurrency performance.

18, Nolock should be added to some SQL query statementsReading and writing are mutually blocked. To improve the concurrency performance, you can add nolock to some queries so that you can allow writing during reading, however, it is possible to read uncommitted dirty data. There are three principles for using nolock. Nolock cannot be added if the query result is used for "inserting, deleting, and modifying! The queried table is frequently split by pages. Use nolock with caution! The temporary table can be used to save the "Data shadow", which is similar to the oracle undo tablespace function. The temporary table can be used to improve the concurrency performance. Do not use nolock.

19. Common simplified rules: do not JOIN more than five tables. Use temporary tables or table variables to store intermediate results. Use less subqueries. Do not use too much view nesting. Generally, do not use more than two view nesting methods.

SQL statement Optimization

Because SQL optimization is complex and restricted by the Environment, you must follow the following principles to write SQL during development:

1. ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other WHERE conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

For example:

(Inefficient)

SELECT... From emp e where sal> 50000 and job = 'manager' AND 25 <(select count (*) from emp where mgr = E. EMPNO );

(Efficient)

SELECT... From emp e where 25 <(select count (*) from emp where mgr = E. EMPNO) and sal> 50000 and job = 'manager ';

2. Avoid '*' in the SELECT clause '*'

When all columns are listed in the SELECT clause, it is convenient to reference '*' using dynamic SQL columns. however, this is a very inefficient method. in fact, ORACLE converts '*' into all column names in sequence during parsing. This task is done by querying the data dictionary, which means it takes more time.

3. Use the table Alias (Alias)

When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column. in this way, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced.

Note: Column ambiguity means that different SQL tables have the same Column name. When this Column appears in an SQL statement, the SQL parser cannot determine the attribute of this Column.

SQL statement Optimization

Select *
From PIXPatient
Where 1 = 2
Or exists (select 1
From DomainPatient ex
Where a. PIXPatientTID = ex. DomainPatientTID
And PatientBirthday in ('2017-01-01 ', '2017-01-09 ')
)
Or exists (select 1
From PersonName ex
Where a. PIXPatientTID = ex. DomainPatientTID
And (FamilyName = 'blood' or GivenName = 'bound ')
)
;