Millions database optimization scheme for SQL database optimization summary _mssql

There are a lot of tutorials about SQL optimization on the web, but it's messy. Recently have time to tidy up a bit, write to share with you, where there are errors and deficiencies, but also please correct the supplementary.

(1) Select the most efficient table name order (valid only in rule-based seo/' target= ' _blank ' > Optimizer): The ORACLE parser processes the table names in the FROM clause in Right-to-left order, written in the last table in the FROM clause (base table driving Table) will be processed first, and in the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. If you have more than 3 table join queries, you need to select a crosstab (intersection table) download address as the underlying table, which is the table referenced by the other tables.

(2) The order of joins in the WHERE clause. : Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

(3) Avoid the use of ' * ' in the SELECT clause: Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time is spent

(4) Reduce the number of visits to get= ' _blank ' > Databases: Oracle performs a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, etc.

(5) in

Sql*plus, Sql*forms and Pro*c.

To increase the amount of retrieved data per get= ' _blank ' > database access, the recommended value is 200.

(6) Use the Decode function to reduce processing time: Using the Decode function avoids repeated scans of the same record or repeated connections to the same table.

(7) Integration of simple, unrelated get= ' _blank ' > Database access: If you have a few simple get= ' _blank ' > database query statements, you can integrate them into a single query (even if there is no relationship between them)

(8) Delete duplicate records: The most efficient way to delete duplicate records (because of the use of rowID) Example:

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 records in a table are deleted, in general, the rollback segment (rollback segments) is used to store information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state before it was deleted (accurately, before the deletion was performed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, The data cannot be recovered. Therefore, very few resources are invoked and the execution time is short. (Translator: Truncate only applies when deleting full table, truncate is DDL not DML)

(10) Use of commit as much as possible: as much use of a commit in the program as it can be, the performance of the program is improved and the demand is reduced by the resources released by the commit: a. The information that is used to recover data on a rollback segment. B. The lock obtained by the program statement c. Redo Log Buffer space D. Oracle to manage the internal costs of the 3 resources

(11) Replace the HAVING clause with the WHERE clause: avoid having a HAVING clause, which will filter the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead. On, where, and having these three clauses that can be conditionally, on is the first execution, where is the second, having the last, because on is the first to filter the records that do not meet the criteria before statistics, it can reduce the intermediate operations to deal with the data, It is supposed to be the fastest, where it should be faster than having, because it filters the data before the sum is used on the two table joins, so there is a table where there is a comparison with having. In the case of this single table query statistic, if the condition to be filtered does not involve the calculation of fields, then their result is the same, where the Rushmore technique is used, and the having is not, the slower the latter is slow if it involves a calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow in the previous article, where the action time is done before the calculation, and the having is only after the calculation, so in this case the results will be different. On a multiple table join query, on has an earlier effect than where. First, the system is based on the join conditions between the tables, a number of tables into a temporary table, and then filtered by the where, and then calculated, after the calculation by having to filter. Therefore, to filter the conditions to play a correct role, first of all to understand how this condition should work, and then decide to put it there

(12) Reduce the query to the table: in the SQL statement containing the subquery, pay special attention to reducing the query to the table. Example:

 Select Tab_name from the 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 often sacrifices execution efficiency. It is very meaningful to master the application of the above function to solve the problem in practical work.

(14) Use the alias of the table (alias): When connecting multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This allows you to reduce parsing time and reduce syntax errors caused by column ambiguity.

(15) Substituting EXISTS instead of in, using not EXISTS instead of in: in many queries based on the underlying table, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as an outer join (Outer joins) or not EXISTS. Example:

(efficient) SELECT * from EMP (base table) where EMPNO > 0 and EXISTS (select ' X ' from DEPT where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ') (inefficient) SELECT * from EMP (underlying table) WHERE EMPNO > 0 and DEPTNO in (SELECT DEPTNO from DEPT where LOC = ' Melb ')

(16) Identify the ' inefficient execution ' of the SQL statement: Although a variety of existing graphical tools for sqlseo/' target= ' _blank ' > optimization are emerging, it is always a good idea to write your own SQL tools to solve problems:

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-d Isk_reads)/buffer_gets < 0.8 order by 4 DESC;

(17) Improve efficiency with indexes: An index is a conceptual part of a table to improve the efficiency of retrieving data, and Oracle uses a complex, b-tree structure. In general, querying data through an index is faster than full table scans. When Oracle finds the best path to execute queries and UPDATE statements, the oracleseo/' target= ' _blank ' > optimizer uses the index. It also increases efficiency when you use indexes to join multiple tables. Another advantage of using the index is that it provides uniqueness validation for the primary key (primary key) ... Those long or long raw data types, you can index almost all columns. In general, using indexes in large tables is particularly effective. Of course, you'll also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve query efficiency, we must also pay attention to its cost. Indexes require space for storage and regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that each record's insert, DELETE, and update will pay 4, 5 more disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow down query response times. It is necessary to periodically refactor the index.:

 ALTER INDEX REBUILD

(18) Replace distinct with EXISTS: When submitting a query that contains a one-to-many table of information (such as departmental and employee tables), avoid using distinct in the SELECT clause. You can generally consider replacing with exist, EXISTS make the query more rapid, Because the RDBMS core module returns the result immediately after the condition of the subquery is satisfied. 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);

() server/' target= ' _blank ' >sql statements are capitalized, because Oracle always resolves server/' target= ' _blank ' >sql ' statements, converts lowercase letters to uppercase executions

(20) in Java code as little as possible with the connector "+" connection string!

(21) Avoid using not on indexed columns usually, we want to avoid using not on indexed columns, and not the same effect as using functions on indexed columns. When Oracle "encounters" not, he stops using the index instead of performing a full table scan.

(22) Avoid using calculations on indexed columns. In the WHERE clause, if the indexed column is part of the function. The seo/' target= ' _blank ' > optimizer will use full table scans without indexing.

Example: Inefficient:

Efficient:

SELECT ... From DEPT WHERE SAL > 25000/12;

(23) Replace > Efficiently with >=: SELECT * from emp where DEPTNO >=4 inefficient: SELECT * from emp where DEPTNO >3 The difference is that the DBMS will skip directly to the first A dept equals 4 record and the latter will first navigate to the Deptno=3 record and scan forward to the first dept greater than 3.

(24) to replace or (for indexed columns) with union it is usually preferable to replace or in the WHERE clause with union. Using or for an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, query efficiency may be reduced because you have not selected an OR. In the following example, indexes are built on both loc_id and region. Efficient: Select loc_id, Loc_desc, REGION from LOCATION WHERE loc_id = UNION SELECT loc_id, Loc_desc, REGION from Locatio N WHERE REGION = "MELBOURNE" low efficiency: SELECT loc_id, Loc_desc, REGION from LOCATION WHERE loc_id = ten OR REGION = "MELBOURNE" If you insist on using or, you need to return the least recorded index column to the front.

(25) using in to replace or this is a simple and easy to remember rule, but the actual execution effect has to be checked, under Oracle8i, the execution path of the two seems to be the same.

Low efficiency:

Efficient

SELECT ... From LOCATION WHERE loc_in in (10,20,30);

(26) Avoid using is null and is null on indexed columns to avoid using any nullable column in the index, which Oracle will not be able to use. For Single-column indexes, this record will not exist in the index if the column contains a null value.　For composite indexes, if each column is empty, the record also does not exist in the index. If at least one column is not empty, the record exists in the index. For example: If the uniqueness index is based on the columns A and B of the table, and the a,b value of a record exists in the table (123,null), Oracle will not accept the next record (insert) with the same a,b value (123,null). However, if all the indexed columns are empty, Oracle will assume that the entire key value is empty and empty is not equal to NULL. So you can insert 1000 records with the same key value, of course they're all empty! Because a null value does not exist in an indexed column, a null comparison of an indexed column in a WHERE clause causes Oracle to deactivate the index. Inefficient: (Index invalidated)

SELECT ... From DEPARTMENT WHERE dept_code are 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 based on more than one column, the seo/' target= ' _blank ' > optimizer will choose to use the index only if its first column (leading column) is referenced by the WHERE clause. This is also a simple and important rule that when only the second column of the index is referenced, the seo/' target= ' _blank ' > optimizer uses a full table scan and ignores the index

(28) Replace union with Union-all (if possible): When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before outputting the final result. If you use Union All instead of union, this sort is not necessary. Efficiency will be improved accordingly. It should be noted that UNION all outputs the same record in the two result sets repeatedly. So you still need to analyze the feasibility of using union all from the business requirements analysis. The UNION will sort the result set, which will use the memory of the Sort_area_size block. For this memory the seo/' target= ' _blank ' > optimization is also very important. The following SQL can be used to query for sorted consumption

Low efficiency:

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) Use the where instead of the order By:order by clause to index only under two strict conditions. All columns in the order by must be contained in the same index and remain in the index. All columns in the order by must be defined as non-null. The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.

For example:

The table dept contains the following:

Dept_code PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL

Inefficient: (index not used)

 SELECT Dept_code from DEPT order by Dept_type

Efficient: (using index)

 SELECT Dept_code from DEPT WHERE dept_type > 0

(30) Avoid changing the type of indexed columns.: Oracle automatically makes simple type conversions to columns when comparing data of different data types. Suppose Empno is an indexed column of a numeric type.

In fact, after Oracle type conversion, the statement translates to:

Fortunately, the type conversion did not occur on the index column, and the purpose of the index was not changed. Now, suppose Emp_type is an indexed column of character type.

This statement is converted by Oracle to:

This index will not be used because of a type conversion occurring internally! To avoid an implicit type conversion of Oracle to your SQL, it is a good idea to explicitly display the type conversion. Note that when characters and numeric comparisons are compared, Oracle converts numeric types to character types preferentially

(31) The WHERE clause needs to be careful: some SELECT statements WHERE clauses do not use indexes. Here are some examples. In the following example, (1) '!= ' will not use the index. Remember, an index can only tell you what exists in the table, not what doesn't exist in the table. (2) ' | | ' is a character join function. As with other functions, the index is deactivated. (3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated. (4) The same indexed columns cannot be compared to each other, which will enable full table scans.

A. The number of records in a table that retrieves more than 30% of the data. Using indexes will have no significant efficiency gains. B. In certain situations, using an index may be slower than full table scans, but this is the same order of magnitude difference. In general, using an index is a few times or even thousands of times times more than a full table scan!

(33) Avoid the use of resource-consuming operations: with

SQL Distinct,union,minus,intersect,order by

Statement starts the SQL engine's execution of resource-consuming sort (sort) functionality. Distinct requires a sort operation, while the other requires at least two times to perform the sort. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If your get= ' _blank ' > Database sort_area_size Well, use union, minus, intersect can also be considered, after all, they are very readable

seo/' target= ' _blank ' > Optimization Group by: Improves the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is obviously much faster.

Low efficiency:

Efficient:

 SELECT JOB, AVG (SAL) from EMP WHERE job = ' PRESIDENT ' OR job = ' MANAGER ' GROUP by JOB