SQL Performance - Avoiding Full Table Scans and Other Tips

When and How to Avoid Full Table Scans

You need to avoid full tables scans because they usually slow down the performance of your application. Therefore, you should avoid using them when reading large tables and this can be achieved by judicious use of indexes.

Data that you should index:

• Primary Key Columns. 
• Columns that are Foreign Keys. 
• Columns frequently used to join tables. 
• Columns that are frequently used as conditions in a query. 
• Columns that have a high percentage of unique values.

Use Full Table Scans When...

• The table size is small 
• Your queries return a high percentage of the rows

You can force a full table scan by not having an index.

Like Operator and Wild-Cards

When using wild-cards and LIKE operators, try to avoid putting a wild-card before the first characters of the search criteria. For example:

Select LAST_NAME from TNAMES where FIRST_NAME like '%Steve%'; -- Avoid the first '%'.

Avoid the OR Operator

Write your SQL statements to avoid the OR operator. Try to replace it with an IN operator.

select LAST_NAME from TNAMES
    where FIRST_NAME = 'Phil'
       or FIRST_NAME = 'Kate'
       or FIRST_NAME = 'Bill';

This is the same as:

select LAST_NAME from TNAMES
    where FIRST_NAME in ('Phil','Kate','Bill');

This is a rule of thumb and you may wish to check your system.

Avoid Using Having

Avoid this if you can. Enough Said!

Avoid Large Sort Operations

Sort operations using ORDER BY, GROUP BY and HAVING clauses are slow. Avoid them if you can.

Use Stored Procedures

Create stored procedures for statements that are used on a regular basis. The advantage is that they are compiled and required no parsing. As standard practice use them to maintain tables, in INSERT, UPDATE and DELETE statements.