Steps to write SQL queries effectively to improve performance

1)      If you want to join a table using INNER JOIN, Do it as a first step in SQL.

2)      Whenever you join tables, give join conditions in below order

                                I.            Condition on Integer (number) fields

                              II.            Condition on String (Character) fields

                            III.            Range conditions (Like >=, <=, BETWEEN, IN etc.)

3)      If you want to join a table using LEFT OUTER JOIN, Do it after INNER JOIN in SQL.

4)      Should not use functions in join conditions (like CASE, SUBSTR, COALESCE etc.).

5)      Add filter condition (WHERE clause) to eliminate unwanted data.

6)      Use derived queries to filter data before joining with another table.

7)      Create an index on most frequently used columns on table.

8)      Collect statistics on index fields and join columns.

9)      Select only useful fields in SELECT clause.

Example:

SELECT

(Useful fields)

FROM Table1

INNER JOIN Table2

ON (Condition on integer fields if any)

AND (Condition on character fields if any)

AND (condition on range conditions if any)

INNER JOIN

(

SELECT (Useful fields) FROM Table3

WHERE (condition to filter unwanted data)

) Table3

ON (Condition on integer fields if any)

AND (Condition on character fields if any)

AND (condition on range conditions if any)

LEFT OUTER JOIN  Table4

ON (Condition on integer fields if any)

AND (Condition on character fields if any)

AND (condition on range conditions if any)

LEFT OUTER JOIN

(

SELECT (Useful fields) FROM Table5

WHERE (condition to filter unwanted data)

) Table5

ON (Condition on integer fields if any)

AND (Condition on character fields if any)

AND (condition on range conditions if any)

WHERE (Condition to filter unwanted data)

Note: Integer data comparison will be done soon, and after that string comparison will be done soon.

If you have any questions, please feel free to write to kkrishnareddychp@gmail.com