Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to optimize multi-table query in SQL Server. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

1. Execution path

This function of ORACLE greatly improves the execution performance of SQL and saves the use of memory: we find that the speed of single-table data is faster than that of multi-table statistics. Single table statistics may only take 0.02 seconds, but the combined statistics of two tables

It may take dozens of seconds. This is because ORACLE only provides caching (cache buffering) for simple tables, which is not suitable for multi-table join queries. The database administrator must set the appropriate parameters for this area in init.ora. The larger the memory area, the more statements can be retained and, of course, the more likely it is to be shared.

two。 Choose the most efficient order of table names (with fewer records at the back)

ORACLE's parser processes the table names in the FROM clause from right to left, so the table written at the end of the FROM clause (the underlying table driving table) is processed first. In the case of multiple tables in the FROM clause, you must select the table with the least number of records as the base table. When ORACLE processes multiple tables, it joins them by sorting and merging them. First, scan the first table (the last table in the FROM clause) and dispatch the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records in the first table.

For example:

Table TAB1 16384 records

Table TAB2 1 record

Choose TAB2 as the underlying table (best method)

Select count (*) from tab1,tab2 execution time 0.96 seconds

Choose TAB2 as the underlying table (bad method)

Select count (*) from tab2,tab1 execution time 26.09 seconds

If there are more than 3 table join queries, you need to select an intersection table as the underlying table, which refers to the table referenced by other tables.

For example, EMP table describes the intersection of LOCATION table and CATEGORY table.

SELECT * FROM LOCATION L, CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN

Will be more efficient than the following SQL

SELECT * FROM EMP E, LOCATION L, CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000

The order of connections in the 3.WHERE clause (the conditions are detailed at the end)

ORACLE parses the WHERE clause in a bottom-up order. According to this principle, the join between tables must be written before 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.

For example:

(inefficient, execution time 156.3 seconds)

SELECT... FROM EMP E WHERE SAL > 50000 AND JOB = 'MANAGER' AND 25

< (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效,执行时间10.6秒) SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL >

50000 AND JOB = 'MANAGER'

Avoid using'*'in the 4.SELECT clause

When you want to list all the COLUMN in the SELECT clause, using dynamic SQL column references is a convenient way. Unfortunately, this is a very inefficient method. In fact, ORACLE converts'*'to all column names in turn during parsing, which is done by querying the data dictionary, which means it will take more time.

5. Reduce the number of visits to the database

When each SQL statement is executed, ORACLE performs a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading data blocks, and so on. Thus, reducing the number of visits to the database can actually reduce the workload of ORACLE.

Method 1 (inefficient)

SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO = 291

Method 2 (efficient)

SELECT A.EMP_NAME, A.SALARY, A.GRADE, B.EMP_NAME, B.SALARY, B.GRADE FROM EMP A Magi EMP B WHERE A.EMP_NO = 342EMP = 291

6. Delete duplicate record

The most efficient way to delete duplicate records (because of the use of ROWID)

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN (X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)

7. Replace DELETE with TRUNCATE

When deleting records in a table, the rollback section (rollback segments) is usually used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE will restore the data to the state it was before the deletion (to be exact, the state before the delete command), and when using TRUNCATE, the rollback segment no longer stores any recoverable information. When the command runs, the data cannot be recovered. As a result, few resources are called and the execution time is very short.

8. Use COMMIT as much as possible

Whenever possible, use COMMIT in your program as much as possible, so that the performance of the program is improved and the requirements are reduced because of the resources released by COMMIT:

Resources released by COMMIT:

a. The information used to recover data on the rollback segment.

b. A lock acquired by a program statement

C. Space in redo log buffer

D. ORACLE to manage the internal expenses of the above three resources (transaction integrity must be taken into account when using COMMIT. In reality, efficiency and transaction integrity are often impossible to get at the same time)

9. Reduce queries on tables

In SQL statements with subqueries, special attention should be paid to reducing queries on tables.

For example:

Inefficient:

SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604

Efficient:

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)

Several Column examples of Update:

Inefficient:

UPDATE EMP SET EMP_CAT = (SELECT MAX (CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX (SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020

Efficient:

UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX (CATEGORY), MAX (SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020

10. Replace IN with EXISTS and NOT IN with NOT EXISTS

In many queries based on underlying tables, it is often necessary to join another table in order to meet one condition. In this case, using EXISTS (or NOT EXISTS) will usually improve the efficiency of the query.

Inefficient:

SELECT * FROM EMP (basic table) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')

Efficient:

SELECT * FROM EMP (base table) WHERE EMPNO > 0 AND EXISTS (SELECT'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')

(relatively speaking, replacing NOT IN with NOT EXISTS will be more efficient.)

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 on the tables in the subquery). To avoid using NOT IN, we can rewrite it as an external connection (Outer Joins) or NOT EXISTS.

For example:

SELECT... FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT='A')

In order to improve efficiency. Rewrite as follows:

(method 1: efficient)

SELECT... . FROM EMP Amag Dept B WHERE A.DEPT_NO = B.DEPT (+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT (+) ='A'

(method 2: most efficient)

SELECT... . FROM EMP E WHERE NOT EXISTS (SELECT'X' FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT ='A')

Of course, the most efficient way is to have table associations. The speed of direct couplet of two tables is the fastest!

11. Identify SQL statements for 'inefficient execution'

Use the following SQL tools to identify inefficient SQL:

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 The above is how to optimize the multi-table query in the SQL Server shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.