SQL SERVER Performance Optimization

1. Select the most efficient table name order (valid only in the rule-based optimizer )

The parser for SQL Server processes the table names in the FROM clause in a right-to-left order, so the table that is written in the FROM clause (the underlying table driving tables) will be processed first, and in the case of multiple tables in the FROM clause, the tables with the fewest number of record bars must be selected as the underlying table. When SQL Server 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, 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 from the first table.

Example: Table A 26,888 records, table B 50 Records, select B as the base table (best method) Select COUNT (*) from a A, 1 seconds, select a as the base table (poor method) select COUNT (*) from B,a execution time 28.08 seconds; If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which is the table referenced by the other table.　

For example:

The EMP table describes the intersection of the T1 table and the T2 table

　　

SELECT *  from  WHEREbetween .   and=A.deptid

will be more efficient than the following SQL

　　

SELECT *  from  WHERE a.deptid=and A.empid= and  Between

2. connection order in the WHERE clause

SQL Server parses the WHERE clause in a bottom-up order, and according to this principle, the connection between tables must be written before other where conditions, and those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

For example:

(Low efficiency, execution time 13.8 seconds)

SELECT *  from  WHERE>20000and=' manager '  and  - < (SELECTCOUNT(*fromWHERE MGR=a.empid);

(High efficiency, execution time 1.6 seconds)

SELECT *  from  WHERE< (SELECTCOUNT(* from  WHERE MGR=and>50000and= ' Manager ';

3. Avoid using ' * ' in the SELECT clause

When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column, which unfortunately is a very inefficient approach. In fact, SQL Server will convert ' * ' to all column names in the process of parsing, which is done by querying the data dictionary, which means more time is spent.

4. Use the Decode function to reduce processing time

Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.　　

For example:

 select  count  ( * ), sum   ( Cost)  from   EMP  where  deptid =  "  0020   " and  EmpName like   " john%  ; 

SELECT COUNT (*SUMfromWHERE deptid='0030 '  and  like ' john% ';

You can use the Decode function to get the same result efficiently (' X ' denotes any field):

 SELECT COUNT(DECODE (DeptID,'0020','X',NULL)) D0020_count,COUNT(DECODE (DeptID,'0030','X',NULL)) D0030_count,SUM(DECODE (DeptID,'0020', Cost,NULL)) D0020_cost,SUM(DECODE (DeptID,'0030', Cost,NULL)) D0030_cost fromEMPWHEREEmpName like 'john%';

Similarly, the Decode function can also be used in the group BY and ORDER BY clauses

5. Replace in with exists

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition, in which case the use of EXISTS (or not EXISTS) will usually improve the efficiency of the query.

Low efficiency:

SELECT *  from  WHERE EmpID>0 and in (SELECTfrom  WHERE = ' RD ') 

Efficient:

 select  *   From   EMP (base table)  where  EmpID >  0  and  exists  (select   " x  "  from   DEPT  where  DEPT. Deptid=   EMP. DeptID  and  LOC =  "  rd   ) 

6. Replace not in with not exists

In a subquery, the NOT IN clause performs an internal sort and merge, in either case, not in is the least effective because it performs a full table traversal of the table in the subquery, and in order to avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS.

For example:

  Select from WHERE not in ( select   from WHERE DType = ' A ');

Efficient processing:

To improve efficiency, rewrite it as

  SELECT from WHERE a.deptid=and is  NULL and b.dtype  ='A'

Most efficient:

  Select from WHERE not EXISTS (select ' X '  from  WHERE d.deptid= and DType='A  ');

7. Improve efficiency with indexes

An index is a conceptual part of a table that is used to improve the efficiency of retrieving data. In fact, SQL Server uses a complex self-balancing b-tree structure. In general, querying data through an index is faster than a full table scan. The SQL Server optimizer uses the index when SQL Server finds the best path to execute the query and the UPDATE statement. Similarly, using an index when joining multiple tables can also improve efficiency.　Another advantage of using an index is that it provides uniqueness validation of the primary key (primary key). In addition to those long or long raw data types, you can index almost any column. Although the use of indexes can improve the efficiency of query, but we must also pay attention to its cost, the index needs space to store, also need regular maintenance, whenever there are records in the table or the index column is modified, the index itself will be modified, which means that each record's insert, DELETE, Update will pay 4, 5 times more disk I/O for this. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time.

SQL Server has two access modes for indexes:

1). Index unique scanning (index unique scan)

In most cases, the optimizer accesses the index through the WHERE clause

For example:

Table Lodging has two indexes: a unique index built on the lodging column LODGING_PK and a non-unique index built on the MANAGER column Lodging$manager

SELECT *  from User WHERE = ' FLOWER ';

Internally, the above SQL will be executed in two steps:

First, the LODGING_PK index is accessed by indexing a unique scan, obtaining a corresponding rowid, and then performing the next retrieval by ROWID access to the table.

If the retrieved column is included in the index column, SQL Server does not perform the second step of processing (accessing the table via ROWID)

Because the retrieved data is kept in the index, accessing the index alone can fully satisfy the query results.

　　

2). Index scope query (index range SCAN)

Available in two situations:

1> a range of searches based on a unique index

2> retrieval based on non-uniqueness Index

　　

Example 1

SELECTfromUserWHERElike'M%';

The WHERE clause condition includes a series of values that SQL Server will query through the indexed range query LODGING_PK

Because the index range query will return a set of values, its efficiency will be the peso cited as the only scan lower

Example 2

SELECTfromUserWHERE='mrzhang ';

This SQL is executed in two steps, Lodging$manager the index range query (get all rowid that match the condition record), and get the value of the lodging column through the ROWID Access table.

Because Lodging$manager is a non-unique index, the database cannot perform an index-unique scan on it.

In the WHERE clause, the index will not be adopted if the first character of the Fu Yutung (WILDCARD) of the value corresponding to the index column begins.

SELECT from WHERE like '%hanman ';

In this case, SQL Server will use a full table scan.

8. Avoid using calculations on indexed columns

In the WHERE clause, if the index column is part of a function, the optimizer uses a full table scan without using the index.

For example:

Low efficiency

SELECT from WHERE * > 25000;

Efficient

 SELECT from WHERE > 25000/ ;

It is important to note that the index columns are not processed in the retrieval, such as: Trim,to_date, type conversion operations, destroying indexes, using full table scans, and affecting the efficiency of SQL execution.

SQL SERVER Performance Optimization