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

Today, I will talk to you about how to optimize multi-table queries in SQLServer. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

What are the optimization schemes of SQLServer multi-table query

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 (cachebuffering) 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 drivingtable) 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 TAB116384 entry record

Table TAB21 entry record

Choose TAB2 as the underlying table (best method)

Selectcount (*) fromtab1,tab2 execution time 0.96 seconds

Choose TAB2 as the underlying table (bad method)

Selectcount (*) fromtab2,tab1 execution time 26.09 seconds

If there are more than 3 tables join queries, then you need to select an intersectiontable 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*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN

Will be more efficient than the following SQL

SELECT*FROMEMPE,LOCATIONL,CATEGORYCWHEREE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND2000

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... FROMEMPEWHERESAL > 50000ANDJOB='MANAGER'AND25 (SELECTMIN (X.ROWID) FROMEMPXWHEREX.EMP_NO=E.EMP_NO)

7. Replace DELETE with TRUNCATE

When deleting records in a table, the rollback section (rollbacksegments) 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

Space in c.redologbuffer

D.ORACLE is 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:

SELECTTAB_NAMEFROMTABLESWHERETAB_NAME= (SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604) AND DB_VER= (SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604

Efficient:

SELECTTAB_NAMEFROMTABLESWHERE (TAB_NAME,DB_VER) = (SELECTTAB_NAME,DB_VER) FROMTAB_COLUMNSWHEREVERSION=604)

Several Column examples of Update:

Inefficient:

UPDATEEMPSETEMP_CAT= (SELECTMAX (CATEGORY) FROMEMP_CATEGORIES), SAL_RANGE= (SELECTMAX (SAL_RANGE) FROMEMP_CATEGORIES) WHEREEMP_DEPT=0020

Efficient:

UPDATEEMPSET (EMP_CAT,SAL_RANGE) = (SELECTMAX (CATEGORY), MAX (SAL_RANGE) FROMEMP_CATEGORIES) WHEREEMP_DEPT=0020

10. Replace IN with EXISTS and NOTIN with NOTEXISTS

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 NOTEXISTS) will usually improve the efficiency of the query.

Inefficient:

SELECT*FROMEMP (basic table) WHEREEMPNO > 0ANDDEPTNOIN (SELECTDEPTNOFROMDEPTWHERELOC='MELB')

Efficient:

SELECT*FROMEMP (basic table) WHEREEMPNO > 0ANDEXISTS (SELECT'X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC='MELB')

(relatively speaking, replacing NOTIN with NOTEXISTS will be more efficient.)

In a subquery, the NOTIN clause performs an internal sort and merge. In either case, NOTIN is the least efficient (because it performs a full table traversal on the tables in the subquery). To avoid using NOTIN, we can rewrite it as an external connection (OuterJoins) or NOTEXISTS.

For example:

SELECT... FROMEMPWHEREDEPT_NONOTIN (SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A')

In order to improve efficiency. Rewrite as follows:

(method 1: efficient)

SELECT... .FROMEMPA, DEPTBWHEREA.DEPT_NO=B.DEPT (+) ANDB.DEPT_NOISNULLANDB.DEPT_CAT (+) ='A'

(method 2: most efficient)

SELECT... .FROMEMPEWhERENOTEXISTS (SELECT'X'FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_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:

SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,ROUND ((BUFFER_GETS-DISK_READS) / BUFFER_GETS,2) Hit_radio,ROUND (DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXTFROMV$SQLAREAWHEREEXECUTIONS > 0ANDBUFFER_GETS > 0AND (BUFFER_GETS-DISK_READS) / BUFFER_GETS

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.