How to optimize SQL statement (full) _mssql

High-performance SQL statements will play a very important role in the operation of the software, the following small series of recently compiled SQL statements to optimize the data to share.

First: Select the most efficient table name order (valid only in rule-based seo/' target= ' _blank ' > Optimizer): The ORACLE parser processes the table name in the FROM clause in Right-to-left order, written in the last table in the FROM clause ( The underlying table driving tables 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 the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.

Second: 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.

Third: 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 will be spent

Fourth: 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.

Fifth: reset the arraysize parameters in Sql*plus, Sql*forms, and pro*c to increase the amount of retrieved data per get= ' _blank ' > Database access, with a recommended value of 200

Sixth: Use the decode function to reduce processing time: Use the Decode function to avoid repeated scans of the same record or repeated connections to the same table.

Seventh: 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 they are not related)

Eighth: 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 WH ERE x.emp_no = e.emp_no);

nineth: replace Delete with truncate: When records are deleted in a table, 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)

Tenth: use commit as much as possible: use a commit in your program as much as you can, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit: A. 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

11th: Replace the HAVING clause with the WHERE clause: avoid having a HAVING clause that 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

12th: Reduce queries against tables: In SQL statements containing subqueries, special care is taken to reduce queries against tables. Example: Select Tab_name from Tables WHERE (tab_name,db_ver) = (select Tab_n Ame,db_ver from tab_columns WHERE VERSION = 604)

13th: Increase 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.

14th: 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 on each column. This allows you to reduce parsing time and reduce syntax errors caused by column ambiguity.

15th: substituting EXISTS instead of in, using not EXISTS instead of: 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, use EXISTS (or not EXISTS) Generally, you will increase the efficiency of your 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 ')

16th: identify the ' inefficient execution ' of the SQL statement: Although there are a variety of graphical tools for sqlseo/' target= ' _blank > optimization, 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-disk_re ADS)/buffer_gets < 0.8 order by 4 DESC;

17th: 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. Periodic refactoring of indexes is necessary.: ALTER index REBUILD

18th: replace distinct with exists: avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. It is generally possible to consider replacing with exist, EXISTS make the query faster, 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);

19th: server/' target= ' _blank ' >sql statements are capitalized, because Oracle always parses server/' target= ' _blank ' >sql ' statements first. Convert lowercase letters to uppercase and then execute

20th: in the Java code as little as possible with the connector "+" connection string!

21st: Avoid using not on indexed columns normally, 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.

22nd: 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: Low efficiency: SELECT ... From DEPT WHERE SAL * > 25000; Efficient: SELECT ... From DEPT WHERE SAL > 25000/12;

23rd: replace > Efficiently with >=: SELECT * from emp where DEPTNO >=4 inefficient: SELECT * from emp where DEPTNO >3 The difference is that the former The DBMS will jump directly to the first dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first dept greater than 3 records. ======================================== Integrated code generator Springmvc_mybatis or Hibernate+ehcache level two cache _shiro_druid_bootstrap_ HTML5 Java Enterprise Framework tab _maven Non-maven version http://my.oschina.net/u/2347562/blog/400728 =================================== =====

24th: Replace with union or (applicable to indexed columns) it is generally 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.

25th: Replace with in 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: SELECT .... From LOCATION WHERE loc_id = ten or loc_id = or loc_id = 30 efficient SELECT ... From LOCATION WHERE loc_in in (10,20,30);

26th: Avoid using is null and is not 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;

27th: Always use the first column of the index: if the index is based on multiple columns, only when its first column (leading column) is referenced by the WHERE clause, seo/' target= ' _blank ' > The optimizer does not choose to use the index. 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

28th:   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. Then sort the output 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 the sorted consumption inefficiencies: Select Acct_num, Balance_amt from debit_transactions WHERE tran_date = ' 31-dec-95 ' UNION SELECT ACC T_num, Balance_amt from debit_transactions WHERE tran_date = ' 31-dec-95 ' efficient: SELECT acct_num, Balance_amt from Debit_trans The ACTIONS WHERE tran_date = ' 31-dec-95 ' UNION all SELECT acct_num, Balance_amt from debit_transactions WHERE tran_date = ' 31 -dec-95 '

29th: 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 D Ept_type Efficient: (using index) SELECT dept_code from DEPT WHERE dept_type > 0

30th: 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. SELECT ... From EMP WHERE EMPNO = ' 123 ' Actually, after Oracle type conversion, the statement is converted to: SELECT ... From EMP WHERE EMPNO = to_number (' 123 ') Fortunately, the type conversion did not occur on the index column, and the use of the index was not changed. Now, suppose Emp_type is an indexed column of character type. SELECT ... From EMP WHERE Emp_type = 123 This statement is converted by Oracle to: SELECT ... From EMP Whereto_number (emp_type) =123 This index will not be used because of an internally occurring type conversion! 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

31st: The WHERE clause needs to be careful: Some SELECT statements do not use indexes for WHERE clauses. 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.

32nd: 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!

33rd: Avoid using resource-consuming operations: SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine to perform resource-intensive sort (sort) functions. 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

34th: seo/' target= ' _blank ' > optimize GROUP by: increase the efficiency of the group BY statement by using the records that you do not need in the group by Filtered out before. The following two queries return the same result but the second one is obviously much faster. Inefficient: SELECT JOB, AVG (SAL) from EMP GROUP by job have job = ' PRESIDENT ' OR job = ' MANAGER ' Efficient: SELECT JOB, AVG (SAL) from EMP WHERE job = ' PRESIDENT ' OR job = ' MANAGER ' GROUP by JOB

The above summary of 34 on how to optimize the SQL statement to do a summary, I hope you like.