Basic Tips for Tuning SQL Statement

Subquery versus Joins:

The main difference between subquery and join is:

• subquery is faster when we have to retrieve data from large number of tables. Because it becomes tedious to join more tables. Join is faster to retrieve data from database when we have less number of tables
• subquery retrieve the data depending on certain condition or manipulation in inner query. Where as joins will join the entire data depending on the conditions.

While using Order by Operator:

• Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead.

While Using AND operator:

If you have a WHERE clause that includes expressions connected by two or more AND operators, Oracle Server will evaluate them from left to right in the order they are written.
Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time. If both parts of an AND expression are equally likely being false, put the least complex AND expression first.

Using Where Clause:

• When you have a choice of using the IN or the EXISTS clause in your SQL, Use the EXISTS clause, as it is usually more efficient and its performance is good.
• If you use LIKE in your WHERE clause, try to use one or more leading character in the clause, if at all possible For example, use: LIKE 'm%' not: LIKE '%m‘.
• If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query.
• If the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a table scan must be run, which will reduce performance and will take more time.
• When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause, as it is much more efficient.

• For example:

SELECT emp_id, emp_name FROM employee WHERE emp_id IN (1000, 1001, 1002, 1003, 1004);

is much less efficient than this:

SELECT emp_id, emp_name FROM employee WHERE emp_id BETWEEN 1000 and 1004;

If your SELECT statement includes an IN operator along with a list of values to be tested in the query :-

• Order the list of values so that the most frequently found values are placed at the first of the list, and the less frequently found values are placed at the end of the list.
• This can speed performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes.

Returning data from Select Statement:

• Do not return the column data that you do no need in you queries.
• You should avoid using SELECT * when returning data from tables.
• Select only the columns that you need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column.
• In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance.

Using the UNION statement:

• The UNION statement performs equivalent of a SELECT DISTINCT on the final result set.
• UNION takes the results of two recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. Example :-

SELECT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2 FROM table_name1 WHERE column_name2 = some_value;

• You can write the same query as:

SELECT DISTINCT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value OR column_name2 = some_value

It will boost the performance of your sql queries.