SQL statement Optimization Tips

1. To optimize the query, try toAvoid full table scan, you should first consider establishing an index on the columns involved in where and order by.

2. Avoid null-valued fields in the WHERE clause, which will cause the engine to discard full-table scans using the index, such as:
Select ID from t where num is null

3. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.

4. Try to avoid using or in the WHERE clause to join the condition, if a field has an index and a field is not indexed, it will cause the engine to discard using the index for a full table scan, such as:
Select ID from t where num=10 or Name = ' root '
You can query this:
Select ID from t where num = 10
UNION ALL
Select ID from t where Name = ' root '

5.in and not in should also be used with caution, otherwise it will result in full table scans, such as:
Select ID from t where num in
For consecutive values, you can use between instead of in:
Select ID from t where num between 1 and 3

A lot of times it's a good choice to replace in with exists:
Select num from a where num in (select num from B)
Replace with the following statement:
Select num from a where exists (select num from B where num=a.num)

6. The following query will also cause a full table scan:
Select ID from t where name like '%abc% '
To be more efficient, consider full-text indexing.

7. You should try to avoid expression operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. Such as:
Select ID from t where NUM/3 = 100
should read:
Select ID from t where num = 100*3

8. You should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. Such as:
Select ID from t where substring (name,1,3) = ' abc '-–name ID starting with ABC
should read:
Select ID from t where name like ' abc% '

9. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used.
And, as far as possible, match the order of the fields to the index order.

10.Update statement, if you only change 1, 2 fields, do not Update all fields, otherwise frequent calls will cause significant performance consumption, while bringing a large number of logs.

11. For multiple large data volume (here Hundreds of is even larger) table join, to first paged and then join, otherwise the logical reading will be very high, poor performance.

12.select Count (*) from table, so that count without any conditions causes a full table scan, and without any business meaning, it must be eliminated.

13. The index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt.
So how to build an index requires careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.

14. Whenever possible, avoid updating clustered index data columns because the order of the clustered index data columns is the physical storage order of the table records, and once the column values change, the order of the entire table records is adjusted,
Will cost a considerable amount of resources. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index.

15. Use numeric fields as much as possible, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of queries and connections and increase storage overhead.
This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.

16. Use Varchar/nvarchar instead of Char/nchar as much as possible, because the first variable length field storage space is small, can save storage space, second, for the query, in a relatively small field in the search efficiency is obviously higher.

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

SQL statement Optimization Tips