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.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|