Database common query Statement (optimization)

Common query wording like

Like itself is relatively low efficiency, should try to avoid the use of the query conditions such as;

Reason:

• For a condition like '%...% ' (full blur), it is impossible to use the index, and the whole table scan is of low natural efficiency;
• Because of the relation of matching algorithm, the higher the field length of fuzzy query, the lower the efficiency of fuzzy query.

Workaround:

• Try to avoid fuzzy query, if because the business need to use fuzzy query, then at least guarantee not to use the full fuzzy query, for the right fuzzy query, that is, like ' ...% ', will use the index;
• Left blur like '% ... ' cannot directly use the index, but can use the form of Reverse + function index to change into like ' ...% ';
• Total ambiguity is not optimized, be sure to consider using a search engine. Reduce database fuzzy queries as much as possible to reduce the load on the database server.

Null

Reason:

• The query field is NULL when a single index fails, causing a full table scan.

Workaround:

• Using NULL in SQL syntax can be a lot of trouble, and the best indexed columns are NOT NULL;
• For is null, you can establish a composite index, NVL (field, 0), after the table and index analyse, is null query can re-enable index lookup, but the efficiency is not worth affirming;
• is not NULL when the index is never used. Tables with large data volumes do not use the IS null query.

<>,! =

Reason:

• In SQL, the non-equal operator restricts the index, causing a full table scan, even if there is an index on the comparison field.

Workaround:

• By changing the non-equal operator to or, you can use the index to avoid a full table scan. For example, to change column<> ' aaa ' to column< ' aaa ' OR column> ' aaa ', you can use the index.

OR

Reason:

• The two conditions that are compared in the WHERE clause, one with an index, one without index, and the use of or will cause a full table scan. For example: where A==1 or B==2,a have an index and no index on B, the full table scan will be restarted when the b=:2 is compared, the data for two conditions can be obtained, and then the union all is used.

Combined index

• Sorting should be sorted in the order of the columns in the combined index, even if only one column in the index is to be sorted, otherwise the sorting performance will be poor. "Include sort fields in index"

Update

• If you change only 1, 2 fields, do not update all fields, otherwise frequent calls can cause significant performance costs, along with a large number of logs.

Multiple large data volumes

• The first page and then join, otherwise the logical reading will be very high, poor performance.

Count function

• Count without any conditions will cause a full table scan, and without any business sense, it must be eliminated.

In and not in

• For consecutive values, use the Between:

Select ID from t where ID in (All-in-a- = = Select id from t where ID between 1 and 3

• Replace In;not exists with exists instead of in.

A/b

Reason:

• Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.
• Engine discards full table scan with index

Workaround:

Select ID from t where ID/2 = 10 = = Select id from t where id = 2 * 10

Substring/datediff

Reason:

• Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.
• The engine discards the full table scan using the index.

Workaround:

Select ID from t where substring (id,1,2) = ' ABCD ' = = Select id from t where id like ' abcd% '

Select ID from t where DATEDIFF (day,createdate, ' 2017-09-08′) = 0 = = Select id from t where createdate >= ' 2016-11-30 ' and CreateDate < ' 2016-12-1 '

A query that doesn't make sense

Reason:

• No result set is returned, but system resources are consumed. For example:
  • Select Col1,col2 into #t from T where 1=0
• Select ID from T with (index name) where id= @id

Force the use of indexes

• Select ID from T with (index name) where id= @id

Suggestions

1) As far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, and secondly for the query, in a relatively small field search efficiency is obviously higher.

2) do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.

3) Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index)

4) Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources. Temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, such as when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table.

5) When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, and if the amount of data is small, create table and insert to mitigate the resources of the system tables.

6) If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.

7) Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.

8) As far as possible to avoid large transaction operations, improve the system concurrency capability.

9) As far as possible to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.

10) Split the large DELETE or INSERT statement and commit the SQL statement in bulk.

Database Common query Statement (optimization)