10 ways to improve SQL query efficiency

First, the query conditions are accurate, for the parameter incoming situation

II. sequence of SQL logical execution

From–>join–>where–>group–>having–>distinct–>order–>top

Three, Horizontal

Fields required for query

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the aliases on each column so that the parsing time is reduced and the syntax errors caused by the column ambiguity are reduced

Four, do less duplication of work

Controlling the execution of multiple executions of the same statement, especially when some basic data is executed many times is seldom noticed by programmers

Reduce multiple data conversions

Eliminate unnecessary sub-queries and join tables, and subqueries are generally interpreted as outer joins in execution plans, with additional overhead associated with redundant connection tables

V. About ZERO table # vs. Table variable @

If the statement is complex and there are too many connections, consider stepping through the temporary table and table variables

If you need to use the same part of the data for a large table multiple times, consider staging this part of the data with temporary tables and table variables

If you need to synthesize data from multiple tables to form a result, consider staging tables and table variables to summarize data for multiple tables

On the selection of temporary tables and table variables, the temporary table is faster than the amount of data.

Select INTO will be faster than the CREATE TABLE + INSERT into method, but select into will lock the system table sysobjects, sysindexes, syscolumns for tempdb in a multiuser concurrency environment. Easy to block other processes

Six, sub-query

Subqueries can be introduced with in, not in, EXISTS, not EXISTS

correlated subqueries for not in, not exists can be replaced by a left join instead of a notation

If you guarantee that subqueries are not duplicated, in, exists related subqueries can be replaced with inner JOIN

In correlated subqueries with exists instead of

Vii. Index

Avoid calculating operations on indexed fields

SELECT ID from T WHERE num/2=100

should read:

SELECT ID from T WHERE num=100*2

Avoid using not,<>,!= on indexed fields

Avoid using is null and is not NULL on indexed columns

Avoid data type conversions on indexed columns

Avoid using functions on indexed fields

Avoid using null values in indexed columns

Do not make multi-field connections to indexed fields

WHERE fame+ '. ' +lname= ' Haiwei. YANG '

should read:

WHERE fname= ' Haiwei ' and lname= ' YANG '

Eight, multi-table connection

Multi-table connection, the connection conditions must be written, rather repeat, do not leave gaps

Join conditions use clustered indexes whenever possible

Ix. Other

In a statement that can use union all, use the Union ALL

Avoid using In,not in,or in the WHERE clause

Avoid the use of resource-intensive operations, SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine execution, resource-intensive sorting (sort) function. Distinct requires a sort operation, while others need to perform at least two sorting

Like '%5400% ' queries do not reference the index, and the type ' x5400% ' refers to the scope index.

10 ways to improve SQL query efficiency