[SQL Server] 34 SQL Optimizations

[Optimization] 34 SQL Optimizations

We must not only write SQL statements, but also write SQL statements with excellent performance. The following section describes how to learn, extract, and summarize
Share the information with you!
(1) select the most efficient table name sequence (only valid in the rule-based Optimizer ):
The Oracle parser processes the table names in the from clause in the order from right to left. The from clause is written in the last table.
(Basic table driving table) will be processed first. When the from clause contains multiple tables, You must select
The table with the least number of records serves as the base table. If there are more than three table join queries, You need to select the cross table (InterSec
As the base table, the cross tabulation refers to the table referenced by other tables.
(2) join order in the WHERE clause .:
Oracle uses the bottom-up sequence to parse the WHERE clause. Based on this principle, the connections between tables must be written in other whe
Before the re condition, the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
(3) Avoid using '*' in the select clause '*':
During the parsing process, Oracle converts '*' into all column names in sequence. This task is to query the data dictionary.
Completed, which means it will take more time
(4) Reduce the number of visits to the database:
Oracle has performed a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks.
And so on;
(5) re-set the arraysize parameter in SQL * Plus, SQL * forms, and Pro * C to add
The amount of data retrieved by the database. The recommended value is 200.
(6) use the decode function to reduce processing time:
You can use the decode function to avoid repeated scan of the same record or join the same table.
(7) simple integration with no associated database access:
If you have several simple database query statements, you can integrate them into a single query (even if there is no
Link)
(8) delete duplicate records:
The most efficient way to delete duplicate records (because rowid is used) is as follows:
Delete from EMP e where E. rowid> (select Min (X. rowid)
From emp x where X. emp_no = E. emp_no );
(9) replace Delete with truncate:
When deleting records in a table, a rollback segment is usually used to store records that can be recovered.
If you do not have a commit transaction, Oracle will recover the data to the State before the deletion (to be exact, the recovery
The status before executing the DELETE command) When truncate is used, the rollback segment no longer stores any recoverable information.
After a command is run, the data cannot be recovered. Therefore, a small number of resources are called and the execution time is short.
Ncate is applicable only when the entire table is deleted, and truncate is DDL rather than DML)
(10) Try to use commit as much as possible:
As long as possible, use commit as much as possible in the program, so that the program performance is improved, the demand will also be due to commit
The released resources are reduced:
Resources released by commit:
A. Information used to restore data on the rollback segment.
B. Locks obtained by Program Statements
C. Space in redo log Buffer
D. Oracle manages the internal costs of the above three types of resources
(11) replace having clause with the WHERE clause:
Avoid using the having clause. Having filters the result set only after all records are retrieved.
Such as sorting and total. If you can use the WHERE clause to limit the number of records, this can be reduced.
Sale. (In non-Oracle) on, where, having are three clauses that can be added with conditions, on is the first execution, whe
Re followed by having, because on filters out records that do not meet the conditions before statistics are made, it can reduce
The data to be processed in the intermediate operation should be the fastest, and the where should be faster than having, because
It performs sum only after filtering data, and on is used only when two tables are joined. Therefore, when a table is joined, the where
Compared with having. In the case of query statistics for a single table, if the filter condition does not involve the field to be calculated,
The results are the same, but the Where can use the Rushmore technology, and having can't, in terms of speed.
The latter must be slow. If the calculated field is involved, it indicates that the value of this field is uncertain before calculation.
For the workflow written in the previous article, the where function is completed before computing, and having is only after computing.
So in this case, the two results will be different. In multi-table join queries, on is earlier than where
Function. The system first combines multiple tables into a temporary table based on the join conditions between tables, and then the where
Filter, calculate, and then use having to filter. It can be seen that the filtering condition must be positive.
First, you must understand when the condition should take effect, and then decide to put it there.
(12) Reduce table queries:
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table. Example:
Select tab_name from tables where (tab_name, db_ver) = (select
Tab_name, db_ver from tab_columns where version = 604)
(13) Improve SQL efficiency through internal functions .:
Complex SQL statements tend to sacrifice execution efficiency, so you can master the above methods to solve problems by using functions.
It is very meaningful.
(14) use the table alias (alias ):
When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column,
This reduces the parsing time and syntax errors caused by column ambiguity.
(15) Replace in with exists and not exists instead of not in:
In many basic table-based queries, to meet one condition, you often need to join another table. In this case
Using exists (or not exists) usually improves the query efficiency. In subqueries, the not in clause will execute
An internal sorting and merging. In either case, not in is the most inefficient (because it
The table executes a full table traversal). To avoid using not in, we can rewrite it into an outer join (Outer Join
S) or not exists.
Example:
(Efficient) Select * from EMP (basic table) Where empno> 0 and exists (select 'x'
From dept where Dept. deptno = EMP. deptno and loc = 'melb ')
(Inefficient) Select * from EMP (basic table) Where empno> 0 and deptno in (select Dep
TNO from Dept where loc = 'melb ')
(16) Identifying 'inefficient execution' SQL statements:
Although a variety of graphical tools for SQL optimization are emerging, write your own SQL tools to solve the problem
It is always the best method:
Select executions, disk_reads, buffer_gets,
Round (BUFFER_GETS-DISK_READS)/buffer_gets, 2) hit_radio,
Round (disk_reads/executions, 2) reads_per_run,
SQL _text
From v $ sqlarea
Where executions> 0
And buffer_gets> 0
And (BUFFER_GETS-DISK_READS)/buffer_gets <0.8
Order by 4 DESC;
17) using indexes to improve efficiency:
Index is a conceptual part of a table to improve the efficiency of data retrieval. Oracle uses a complex self-balancing B-tr
EE structure. Generally, data queried through indexes is faster than full table scan. When Oracle finds out and executes the query and update statements
The Oracle optimizer uses indexes when connecting multiple tables.
Another advantage of using an index is that it provides uniqueness verification for the primary key .. Those long or long
Raw data type. You can index almost all columns. Normally, using indexes in large tables is particularly effective. Of course,
You will also find that using indexes to scan small tables also improves the efficiency of queries.
High, but we must also note the cost. The index requires space for storage and regular maintenance.
The index itself is also modified when the table is increased or decreased or the index column is modified, which means the insert, del
Ete and update will pay 4 or 5 more disk I/O. Because the index requires additional storage space and processing,
Unnecessary indexes will slow the query response time .. Regular index reconstruction is necessary .:
Alter index <indexname> rebuild <tablespacename>
18) replace distinct with exists:
When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using D in the select clause.
Istinct. Generally, you can consider replacing it with exist. exists makes the query faster, because the core module of RDBMS will
If the subquery condition is met, the result is immediately returned. Example:
(Inefficient ):
Select distinct dept_no, dept_name from Dept D, EMP E
Where D. dept_no = E. dept_no
(Efficient ):
Select dept_no, dept_name from Dept d Where exists (select 'x'
From EMP e where E. dept_no = D. dept_no );
(19) SQL statements are written in uppercase, because Oracle always parses SQL statements first and converts lowercase letters to uppercase letters.
Run again
(20) try to use the connector "+" to connect strings in Java code!
(21) Avoid using not in index columns,
We should avoid using not in the index column, not will have the same impact as using the function in the index column. When Orac
Le "Encounters" not, and he will stop using the index and then perform a full table scan.
(22) Avoid using computation on index columns.
In the WHERE clause, if the index column is part of the function, the optimizer will use full table scan without using the index.
Example:
Inefficiency:
Select... From dept where Sal * 12> 25000;
Efficient:
Select... From dept where SAL> 25000/12;
(23) Replace with> =>
Efficient:
Select * from EMP where deptno> = 4
Inefficiency:
Select * from EMP where deptno> 3
The difference between the two lies in that the former DBMS will directly jump to the first record where DEPT is 4, while the latter will first locate Dept
No = 3 Records and scans forward to the first record whose DEPT is greater than 3.
(24) replace or with Union (applicable to index columns)
In general, replacing or in the WHERE clause with union will produce better results.
Full table scan. Note that the preceding rules are only valid for multiple index columns. If a column is not indexed
The rate may decrease because you didn't select or. In the following example, indexes are built on both the loc_id and region.
Efficient:
Select loc_id, loc_desc, Region
From location
Where loc_id = 10
Union
Select loc_id, loc_desc, Region
From location
Where region = "Melbourne"
Inefficiency:
Select loc_id, loc_desc, Region
From location
Where loc_id = 10 or region = "Melbourne"
If you insist on using or, you need to write the index columns with the least records at the beginning.
(25) use in to replace or
This is a simple and easy-to-remember rule, but the actual execution results must be tested. in Oracle8i, the two steps are as follows:
The path seems to be the same.
Inefficiency:
Select .... From location where loc_id = 10 or loc_id = 20 or loc_id = 30
Efficient
Select... From location where loc_in in (10, 20, 30 );
(26) Avoid using is null and is not null in the index column.
To avoid using any column that can be empty in the index, Oracle will not be able to use this index. For a single column index, if the column
This record does not exist in the index. For composite indexes, if each column is empty, the index does not exist.
This record. If at least one column is not empty, the record exists in the index. Example: If the unique index is created in
Column A and column B of the table, and the and B values of a record in the table are (123, null), Oracle will not accept the next
Records with the same A and B values (123, null) are inserted. However, if all the index columns are empty
The entire key value is null but null is not equal to null. Therefore, you can insert 1000 records with the same key value. Of course, they
All are empty! Because the null value does not exist in the index column, the null value of the index column in The WHERE clause will make the Oracle
Disable this index.
Inefficiency: (index failure)
Select... From department where dept_code is not null;
Efficient: (index valid)
Select... From department where dept_code> = 0;
(27) always use the first column of the index:
If the index is created on multiple columns, only when its first column (Leading column) is referenced by the WHERE clause,
The optimizer selects this index. This is also a simple and important rule. When only the second column of the index is referenced,
The optimizer uses full table scan and ignores indexes.
28) replace union with Union-all (if possible ):
When the SQL statement needs to union two query result sets, these two result sets will be combined in the form of union-all
And then sort the final result. If Union all is used to replace union, this sorting is not necessary.
The efficiency will be improved as a result. It should be noted that Union all will repeatedly output two result sets with the same records
Therefore, you still need to analyze the feasibility of using Union all from the business needs. Union will sort the result set,
This operation will use the sort_area_size memory. The optimization of this memory is also very important. below
SQL can be used to query the consumption of sorting
Inefficiency:
Select acct_num, balance_amt
From debit_transactions
Where tran_date = '31-DEC-95'
Union
Select acct_num, balance_amt
From debit_transactions
Where tran_date = '31-DEC-95'
Efficient:
Select acct_num, balance_amt
From debit_transactions
Where tran_date = '31-DEC-95'
Union all
Select acct_num, balance_amt
From debit_transactions
Where tran_date = '31-DEC-95'
(29) replace order by with where:
The order by clause only uses indexes under two strict conditions.
All columns in order by must be included in the same index and maintained in the order of the index.
All columns in order by must be defined as non-empty.
The index used by the WHERE clause and the index used in the order by clause cannot be tied together.
For example:
The dept table contains the following columns:
Dept_code PK not null
Dept_desc not null
Dept_type null
Inefficiency: (indexes are not used)
Select dept_code from Dept order by dept_type
Efficiency: (using indexes)
Select dept_code from Dept where dept_type> 0
(30) Avoid changing the index column type .:
Oracle automatically converts columns to different types of data.
Assume that empno is a numeric index column.
Select... From EMP where empno = '20140901'
In fact, after Oracle type conversion, the statement is converted:
Select... From EMP where empno = to_number ('123 ')
Fortunately, the type conversion does not occur on the index column, and the purpose of the index is not changed.
Assume that emp_type is a character-type index column.
Select... From EMP where emp_type = 123
This statement is converted:
Select... From EMP whereto_number (emp_type) = 123
This index will not be used because of internal type conversion! To avoid Oracle's implicit SQL statement
It is best to explicitly display the type conversion. Note that Oracle will give priority to character and value comparison.
Convert numeric to character
(31) WHERE clause to be careful:
The where clause in some select statements does not use indexes. Here are some examples.
In the example below, (1 )'! = 'No index will be used. Remember, the index only tells you what exists in the table, instead
Can tell you what does not exist in the table. (2) '|' is a character concatenation function. indexes are disabled just like other functions.
(3) '+' is a mathematical function. Like other mathematical functions, indexes are disabled. (4) The same index Columns cannot interact with each other.
This will enable full table scan.
(32) A. If the retrieved data volume exceeds 30% of the number of records in the table, using indexes will not significantly improve the efficiency.
B. In certain situations, using indexes may be slower than full table scanning, but this is a difference of the same order of magnitude.
In this case, using an index is several times or even several thousand times more than a full table scan!
(33) Avoid resource-consuming operations:
SQL statements with distinct, union, minus, intersect, and order by will start the SQL engine.
Execute the resource-consuming sorting (SORT) function. Distinct requires a sorting operation, and the other requires at least two
Secondary sorting. Generally, SQL statements with union, minus, and Intersect can be rewritten in other ways. If
The sort_area_size of your database is well configured. You can also consider using Union, minus, and intersect.
After all, they are highly readable.
(34) Optimize group:
To improve the efficiency of group by statements, you can filter out unnecessary records before group by. The following two
The query returns the same results, but the second result is much faster.
Inefficiency:
Select job, AVG (SAL)
From EMP
Group by job
Having job = 'President'
Or job = 'manager'
Efficient:
Select job, AVG (SAL)
From EMP
Where job = 'President'
Or job = 'manager'
Group by job