SQL Server Multi-table query optimization scheme collection _mssql

The optimization scheme of SQL Server Multi-table query is the main content of this article, we give the optimization scheme and specific optimization examples, then let us take a look at this part of the content.

1. Execution path

This feature of Oracle greatly improves the performance of SQL and saves memory usage: we find that the statistics of single table data are completely two concepts compared to the speed of multiple table statistics. One-table statistics may take only 0.02 seconds, but 2 tables combined statistics

It may take 10 seconds. This is because Oracle only provides caching for simple tables (cache buffering), and this feature does not apply to multiple table join queries. The database administrator must set the appropriate parameters for the area in the Init.ora, and the larger the memory area, the more statements can be retained and the greater the likelihood of being shared.

2. Select the most efficient table name order (fewer records in the back)

The Oracle parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In cases where multiple tables are included in the FROM clause, you must select a table with the fewest number of records to use 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 order the records, and then scan the second table ( The last second table in the FROM clause, and finally merges all the records retrieved from the second table with the appropriate records in the first table.

For example:

Table TAB1 16,384 Records

Table TAB2 1 Records

Select TAB2 as the base table (best Practice)

Select COUNT (*) from TAB1,TAB2 execution time 0.96 seconds

Select TAB2 as the base table (Bad method)

Select COUNT (*) from TAB2,TAB1 execution time 26.09 seconds

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.

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 
e.cat_no = C.cat _no 

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 connection order in the 3.WHERE clause (conditionally placed in the back)

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.

For example:

(Inefficient, execution time 156.3 seconds)

SELECT ... 
From emp E 
WHERE  SAL > 50000 
and   JOB = ' MANAGER '   < (SELECT COUNT (*) from EMP 
whe RE mgr=e.empno); 
(Efficient, execution time 10.6 seconds) 
SELECT ... 
From EMP E 
where < (SELECT COUNT (*) from EMP 
       where mgr=e.empno) and   SAL > 50000 
and
   job = ' MANAGER '; 

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

When you want to list all columns in the SELECT clause, it is a convenient way to use the dynamic SQL column reference ' * '. Unfortunately, this is a very inefficient approach. In fact, 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.

5. Reduce the number of accesses to the database

When executing every SQL statement, Oracle does a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on. Thus, reducing the number of accesses to the database can actually reduce Oracle's workload.

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 = 34 2 
   and  b.emp_no = 291; 

6. Delete duplicate records

The most efficient way to delete a duplicate record (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 you delete records in a table, in general, the rollback segment (rollback segments) is used to hold 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.

8. Use of commit as much as possible

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

Resources released by commit:

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

B. Locks obtained by program statements

C. Space in the Redo log buffer

D. Oracle manages the internal costs of these 3 resources (the integrity of the transaction must be noted in the use of a commit, and the actual efficiency and transactional integrity are often the fish and the paws)

9. Reduce the query to the table

In SQL statements that contain subqueries, you should pay special attention to reducing the query to the table.

For example:

Low efficiency:

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 the 
      TABLES 
      WHERE  (tab_name,db_ver) 
= (select Tab_name,db_ver) from 
          tab_columns 
          

Update multiple column Examples:

Low efficiency:

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. Substituting exists with 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.

Low efficiency:

SELECT * FROM 
EMP (base 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 significantly increase efficiency)

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.

For example:

SELECT ... 
From the EMP 
where Dept_no not in (SELECT dept_no from 
             DEPT 
             where dept_cat= ' A '); 

To improve efficiency. Rewrite as:

(Method one: 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 two: 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 a table association. Direct two-table relationship couplet speed is the fastest!

11. Identify the ' inefficient execution ' of the SQL statement

Use the following SQL tools to find 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; 

(although there are many graphical tools for SQL optimization at the moment, writing your own SQL tools to solve problems is always the best approach)

The above is the SQL Server multiple table query optimization of the relevant knowledge, I hope this introduction can be harvested for you!