SQL Tuning Tips for performance

SQL Tuning Tips for performance:

1) Concatenation of Different Data Types
An SQL query involving concatenation of different datatypes takes more time to execute.

2) Usage of “WHERE” Instead of “HAVING”
Using a “where” clause in place of “having” is often effective, in group by statements.
The where clause is applied before selecting the data, whereas the having clause is
applied after data retrieval.

3) Position of Table with Fewer Rows in the “SELECT…FROM” Query
It is advisable to put the table that returns the fewest rows at the end of the from list.

4) Usage of “BETWEEN” in Place of Comparison Operators
If a query involves a range of values to be searched on, then usage of “between” is
advisable over the comparison operators.

5) Usage of Table Aliases
If more than one table is used in a query, then it is advisable to use table aliases, as they
would enhance the speed of the parse phase of the query.

5) Index
Index enables faster retrieval of data, but is an overhead when insertion, updating, and
deletion processes are involved.
The index is a separate structure attached to a table. This structure holds the indexed
column value and a pointer to the physical data.
Hence, any query which involves searching based on indexes would first access the index
structure and then would retrieve the data from the respective table.
But if the table contains more than 4 to 5 indexes, then the performance comes down.
The selectivity for index is determined by the ratio of unique values in a given column
to the total number of values.
If the value is nearer to 0, then usage of index is not advisable.
If the value is nearer to 1, then usage of index would enhance the performance of the
system.

6) Usage of “ORDER BY”
Avoid “ORDER BY” wherever possible as it works on output of the query and hence
involves double processing. The exception is if any of the columns to be sorted in DESC
order are wanted.


7 ) Resource Intensive Operations
Avoid using resource intensive operations like UNION, MINUS, DISTINCT, INTERSECT,
ORDER BY, and GROUP BY. DISTINCT uses one sort whereas other operators use
two sorts or more.

8) Usage of NULL
Null values are never stored in index structure.
Any query using the clause “IS NULL”, does not make use of index and does a FTS (Full
Table Scan), thereby taking more time to execute.


9) Usage of “EXISTS” and “NOT EXISTS” Clauses
Wherever it is possible,”EXISTS” or “NOT EXISTS” should be used. Using the
“EXISTS” clause may eliminate unnecessary table accesses.