SQL Server Query

In SQL Server, the optimizer dynamically determines a low-cost connection policy between two tables based on the table or index structure and table data. SQL Server has three connection policies: Nested Loop Join, Merge Join, and Hash Join.

Nested Loop Join: The optimizer selects a table with a small amount of data as the outer table, and the table with a large amount of data as the inner table must have an appropriate available index.

Merge Join: The two joined tables are sorted on the connected columns, and both tables have available indexes.

Hash Join: The two connected tables have no indexes on the connected columns, or the inner table selected by the optimizer has no available indexes, or one table is far smaller than the other.

Spool: a table that temporarily saves intermediate result sets for complex queries. It exists in tempdb and only exists during the query lifecycle.

Seek: used only for clustered or non-clustered indexes. The Seek operation uses the index to locate the rows that satisfy the where clause, instead of checking all rows in the index. If the where clause is highly selective, seek is more efficient, because the where clause has excluded a large part of the table data.

Scan: it can be used on tables or indexes. Unlike seek, scan evaluates all rows in a table or index against the where clause. If the evaluation result is true, this row is returned. How many rows of data are detected in a table or index. While seek uses indexes to select only the rows that meet the conditions.

Below are some query optimization suggestions:

1. Avoid implicit conversion

Implicit conversion occurs when the parameters provided for search parameters are compatible with the corresponding columns but not identical data types. Use the following script to test the performance (from SQL server2005 Performance Tuning ):

-- CreditCardApprovalCode field type [varchar] (15)
DECLARE @ CreditCardApprovalCode_UNICODE NVARCHAR (30)
SET @ CreditCardApprovalCode_UNICODE = n' 539435Vi62867'

SELECT CreditCardApprovalCode FROM Sales. SalesOrderHeader
WHERE CreditCardApprovalCode = @ CreditCardApprovalCode_UNICODE -- when data is converted, varchar must be converted to nvarchar.
GO
DECLARE @ CreditCardApprovalCode VARCHAR (30)
SET @ CreditCardApprovalCode = '539435vi62867'

SELECT CreditCardApprovalCode FROM Sales. SalesOrderHeader
WHERE CreditCardApprovalCode = @ CreditCardApprovalCode -- no data conversion

Cost of conversion statements:

Table 'salesorderheader '. Scan count 1, logical reads 704 times

CPU time = 15 ms, occupied time = 15 ms.

Execution Plan:

Cost of no conversion statement:

Table 'salesorderheader '. Scan count 1, logical reads 704 times,

CPU time = 15 ms, occupied time = 13 Ms.

Execution Plan:

2. Avoid functions in the WHERE clause Column

Using a function on the column of the Where clause can prevent the optimizer from using the index on the column (in general, you should avoid using a function on the column of the Where clause, but it is best to make a decision after testing based on the actual data ). Run the following script:

select d.Name
from HumanResources.Department as d
where SUBSTRING(d.name,1,1)='F'

select d.Name
from HumanResources.Department as d
where d.name like 'F%'

Execution Plan:

This script is statistic during my test.
I/O is the same as times output. for better performance, you must test the specific data. However, you should avoid using functions in the WHERE clause columns.

For date comparison, similar queries are often used in program development.

Create a test index first

CREATE NONCLUSTERED INDEX [ix_test] ON [Sales].[SalesOrderHeader] 
(
[OrderDate] ASC
)
select soh.SalesOrderID,soh.OrderDate
from Sales.SalesOrderHeader as soh
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID=sod.SalesOrderID
where DATEPART(yy,soh.OrderDate)=2002
and DATEPART(mm,soh.OrderDate)=4

Execution cost:

Table 'worktable '. Scan count 0, logical read 0, physical read 0

Table 'salesorderdetail '. Scan count 1, logical reads 228, physical reads 0

Table 'salesorderheader '. 1 scan count, 61 logical reads, 2 Physical reads

CPU time = 16 ms, occupied time = 198 Ms.

Execution Plan:

The preceding query prevents the optimizer from using indexes. You can change the preceding query to the following:

select soh.SalesOrderID,soh.OrderDate
from Sales.SalesOrderHeader as soh
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID=sod.SalesOrderID
where soh.OrderDate>='2002-04-01'
and soh.OrderDate<'2002-05-01'

Execution cost:

Table 'salesorderdetail '. Scan count 244, logical reads 814, physical reads 0

Table 'salesorderheader '. Scan count 1, logical read 3, physical read 0

CPU time = 0 ms, occupied time = 101 Ms.

Execution Plan:

3. Use exists instead of Count (*) to verify that the data exists.

Count (*) must scan all rows in the table. Exists only scans records that match the first condition to stop scanning.

4. Use set nocount

After each query in a batch or stored procedure is executed, the server reports the number of affected rows, as shown in figure

<Number> row (s) affected

This information is returned to increase the network overhead for the application. You can use the following methods to avoid this overhead:

Set nocount on

<SQL queries>

Set nocount off

5. Reduce cable overhead

By default, select, insert, update, and delete all use database locks and are row-level locks. For queries of a large number of rows, the request line lock on all individual rows adds a great deal of overhead to the lock management process. It can reduce the lock granularity to reduce the lock overhead.

In WEB applications, it is best to query in the following form

Select * from <tablename>
With (nolock)