Notes for optimizing SQL statements

1. Try not to perform function processing on the column name. It processes the subsequent values.

For example, the efficiency of where col1 =-5 is higher than that of where-col1 = 5.

The column value is calculated based on the following conditions. The optimizer cannot use indexes under such conditions.

Instead, it is necessary to calculate all values before comparison.

2. Try to use the same value as the number of drama columns for operations

If col1 is Numeric

For example, where col1 = 2 and where col1 = '2 ′

The former is more efficient.

Because when comparing characters and numbers

The engine needs to convert both of them into dual-precision and then compare them.

3. Reduce the use of functions

For example, where col1> = '2017-10-26 'and col1 <= '2017-10-27 ′

And where datediff (day, col1, getdate () = 0

The latter uses function processing. So the index on col1 cannot be used again.

4. Try not to use OR

Generally, for conditions of OR

Optimizer generally uses full table Scan