SQL Server query performance optimization-index and SARG (iv) SQL Server query performance optimization-index and SARG (iii) SQL Server query performance optimization-index and SARG (III)

ConnectSQL Server query performance optimization-index and SARG (III)

Note:The index creation mentioned below is an SQL Server query performance optimization-the index in the index list described in the index and the first index in SARG (1.

For example, Index 1 (Clustered index) and index 5 (non-clustered index)

 

4: Use the OR operator with caution.

 

For example, the SQL Server query performance is optimized. In the example of index and SARG (3), WBK_PDE_LIST_ORG_HISTROY is created.Index 2, that is, inThe [QTY_1] field is used to create an index to quickly find records that match the record from a large number of records (for example,"2. Do not perform negative queries."Number 2 In the table, reads 43 times logically, and costs 0.121935 of execution), filters records with COP_G_NO = '000000' in a small amount of data, so you can use the index function. However, if the OR operation is used, all fields must have indexes available. The query statement is changed to the following:

SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1 = 312 or COP_G_NO = '20140901'

When the COP_G_NO field does not apply an index, the entire data table is directly scanned.

 

	Serial number		Logical read	Execution cost
Query statement		SELECT [WBOOK_NO], [COP_G_NO], [G_NO], [CODE_T], [QTY_1], [UNIT_1] , [TRADE_TOTAL], [GROSS_WT] FROM [WBK_PDE_LIST_ORG_HISTROY] Where qty_1 = 312 or COP_G_NO = '000000'
Index 2	1	Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 1, logical reads 1306, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.	1306
				1.03687
Index 2, 3	2	Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 2, logical reads 101 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, and lob pre-reads 0 times.	101
				0.245731
Index 4, 5	3	Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 2, logical read 6 times, physical read 0 times, pre-read 0 times, lob logical read 0 times, lob physical read 0 times, lob pre-read 0 times.	6
				0.0125617

 

Summary:

Serial number	Index created	Logical read	Query the number of records	Execution cost
1	Index 2	1306	90	1.03687
2	Index 2, 3	101	92	0.245731
3	Index 4, 5	6	92	0.0125617

 

 

From the table in the summary, we can see that index 4 and index 5 use the include keyword, when creating an index, add the fields required in the query statement to the leaf level of the non-clustered index. Therefore, you only need to access the leaf level of the index during the query, the data on the data page does not need to be accessed through the RID operation, so the query speed is improved.

That is to say, the query optimizer can find all column values in the index, and does not access tables or aggregate index data, thus reducing disk I/O operations. However, the disadvantage is that the index requires additional disk storage space. It is superior or inferior, and is adjusted according to the actual situation.

 

 

Exceptions:

When the OR operator is used, if one of the multiple conditions does not have an appropriate index, it is useless for other conditions to have an index. Only the entire data table can be scanned OR clustered index scanned, to check whether all the data has a matching record.

Even if multiple conditions have indexes and the number of query results required is too large, the SQL SERVER query optimization program will automatically use full table scan or clustered index scan, to check whether all the data has a matching record.

For example:

Create a non-clustered index 4, 5, and no index 1.

Run the following code:

SELECT [WBOOK_NO] ,[COP_G_NO],[G_NO],[CODE_T],[QTY_1],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]  FROM [WBK_PDE_LIST_ORG_HISTROY] where qty_1=2 or COP_G_NO='60207106'

Table 'wbk _ PDE_LIST_ORG_HISTROY '. Scan count 1, logical reads 1306, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.

 

 

Summary:

Do not consider that indexes are ineffective as long as there is a negative query in the WHERE clause of the query condition. Using a non-SARG clause in the WHERE clause does not necessarily result in full table scan or clustered index scan. SQL SERVER can use indexes in some non-SARG conditions, and although the query contains some non-SARG, it can still use indexes in the SARG section of the query.

Do not consider that using sarg in the where clause of the query condition in the query statement will certainly use the corresponding index instead of full table scan or clustered index scan. The SQL SERVER query optimization program determines whether to use the corresponding index or full table scan or clustered index Scan Based on the number of records in the query results obtained by the SARG usage. Of course, the first and most direct change to performance tuning is to change non-SARG to SARG. Because the worst case for changing a non-SARG to a SARG is full table scan or clustered index scan, the number of records in the query results is relatively small, and the corresponding index is used efficiently to quickly find the results.