SQL Server multi-Table query optimization solution highlights, sqlserver

SQL Server multi-Table query optimization solution highlights, sqlserver

The multi-Table query optimization solution of SQL Server is what we will introduce in this article. We provide the optimization solution and specific optimization examples, next let's take a look at this part.

1. Execution path

This function greatly improves SQL Execution performance and saves memory usage. We found that the speed of Single-table data statistics is completely two concepts than that of Multi-table statistics. the statistics for a single table may only take 0.02 seconds, but the combined statistics for two tables are

It may take dozens of seconds. this is because ORACLE only provides cache buffering for simple tables. This function is not applicable to multi-table join queries .. the database administrator must. set appropriate parameters for this region in ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher.

2. Select the most efficient table name sequence (with fewer records placed behind)

The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. Therefore, the table written in the FROM clause (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 as the base table. when ORACLE processes multiple tables, it uses sorting and merging to connect them. first, scan the first table (the last table in the FROM clause) and sort the records, and then scan the second table (the last second table in the FROM clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.

For example:

Table TAB1 16,384 records

Table TAB2 1 record

Select TAB2 as the base table (the best method)

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

Select TAB2 as the base table (poor method)

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

If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

For example, the EMP table describes the intersection of the LOCATION table and the 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 

It will be more efficient than the following SQL statements

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 

3. The join order in the WHERE clause (the condition is placed below)

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, 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); (efficient, execution time: 10.6 seconds) SELECT... From emp e where 25 <(select count (*) from emp where mgr = E. EMPNO) and sal> 50000 and job = 'manager ';

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

When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. unfortunately, 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.

5. Reduce the number of database accesses

When each SQL statement is executed, ORACLE performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. it can be seen that 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,EMP B    WHERE A.EMP_NO = 342    AND  B.EMP_NO = 291; 

6. Delete duplicate records

The most efficient method for deleting duplicate records (because ROWID is used)

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, a rollback segment is usually used to store information that can be recovered. if you do not have a COMMIT transaction, ORACLE will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When you use TRUNCATE, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short.

8. 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's performance is improved, and the demand will also be reduced by the resources released by COMMIT:

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 (when using COMMIT, you must pay attention to the integrity of the transaction. In reality, efficiency and transaction integrity are often not the same as the fish and the bear's paw)

9. Reduce table queries

In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table.

For example:

Inefficiency:

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) 

Example of updating multiple columns:

Inefficiency:

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

Inefficiency:

SELECT * from emp (basic table) where empno> 0 and deptno in (select deptno from dept where loc = 'melb ')

Efficient:

SELECT * from emp (basic table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb ')

(Replacing not in with not exists will significantly improve efficiency)

IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists.

For example:

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

To improve efficiency, rewrite it:

(Method 1: efficient)

SELECT …. FROM EMP A,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 associate tables. directly link two tables with the fastest speed!

11. Identify 'inefficient execution' SQL statements

Use the following SQL tools to find out inefficient SQL statements:

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; 

(Although various graphical tools for SQL optimization are emerging, writing your own SQL tools is always the best way to solve the problem)

The above is the knowledge of the SQL Server multi-Table query optimization solution. I hope this introduction will help you gain some benefits!