Optimization of SQL queries

Generally we use SQL queries in stored procedures, views, triggers and functions.
The poorly designed queries significantly reduces the database performance. Even if we use fast servers, this has proven to be a factor which makes the response time of DB longer.
Therefore the solution lies in optimization.
Here are some useful tips to optimize queries.
* Avoid using Select *
- Instead specify the fields you need as you may not want to use all the fields.
Example:

Non optimal query:

Select * From Table1

Optimized Query:

Select Field1, Field2 From Table1


* Apply Filters as much as possible. (Where Clause in the Query)
Example:
To select the employee Name and DOB of Grade A

Non optimal query:

Select * From Employees Where Grade = 'A' - (Dont' Use)

Optimized Query:

Select EmpName, DOB From Employees Where Grade = 'A'

* Choose proper joins[INNER,LEFT,OUTER etc]
Joins in queries are very expensive. So we need to be very careful while creating Joins in queries.
Don't try to join two unrelated fields.
Always try to join on index fields.

This INNER JOIN query

Select E.EmployeeID, M.ManagerName
From Employee E INNER JOIN Manager M ON E.EmployeeID = M.EmployeeID

is efficient than

Select E.EmployeeID, M.ManagerName
From Employee E , Manager M WHERE E.EmployeeID = M.EmployeeID


* Make good use of Views
Filtered Views are like running query on query which will make the process slower.
For example
Create View Employees_Sel AS
Select * From Employees
If we run
Select * From Employees_Sel
This is just like running Select * From Employees twice.
You should not use the view in that case.
*Prefer Stored procedures to Queries

Stored procedures are fast as it is a compiled code
Stored procedures are run on the server which is typically faster
Even programmatically calculated values are not as fast as stored procedures in some cases.

Optimized query to retrieve large number of records

This is a more complicated example, but it illustrates filtering at its best.
We have two tables
-- Books (BookID, DescID, Price) and Description(DescID, LanguageID, Text).

There are 100,000 Products and unfortunately we need them all.
There are 100 languages (LangID = 1 = English). We only want the English descriptions for the products.

We are expecting 100 000 Products (BookName, Price).

First try:



Select D.Text As BookName, B.Price
From Books B INNER JOIN Description D On B.DescID = D.DescID
Where D.LangID = 1



That works but it will be really slow because your DB needs to match 100,000 records with 10,000,000 records and then filter that Where LangID = 1.
The solution is to filter On LangID = 1 before joining the tables.

Optimized Query:



Select D.Text As BookName, B.Price
From (Select DescID, Text From Description Where D.LangID = 1) D
INNER JOIN Books B On D.DescID = B.DescID



Now, that will be much faster. You should also make that query a Stored Procedure to make it faster.