SQL Queries that are mostly used to select /retrieve data from the database. There are different ways to write the same SQL query result as per the requirment, but use of the best approach is important when results are needed correct and faster. If we use the correct method for correct result is definetly improve the performance of query excution and ultimatly it affects the overall applications performance. I have tried in this Article to help the beginners in writing SQL server queries effectively for database testing.
Tips for writing optimized SQL Queries
1. Use of column names instade of '*'
The sql query will give the faster result if you use the exact columns names in SELECT statement instead of '*'. Hence remove * from SELECT and use only those columns which are necessary.
Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
SELECT empid, emp_name, age, deptno
2. Use joins and Subqueries as per the requirment.
SELECT * from employee
Joins and Subqueries both have there own advantages. Most of the time Joins re provide the better performance as subqueries have some restrictions.Try to select the best option for your query.
Join Advantages :
Joins are helpful when we need to retrive the data from multiple tables. If we try to use subqueries in this senario it will affect performance.
If you want the data for Employee with department then use Correct Use of joins
Select emp.Empname , dept.DeptName
from Employee emp
inner join Department dept on emp.deptno=dept.deptno
Wrong use of Sub queries (avoiding the Join)
In this case Sub queries are using full table scan which results in poor performance than above
3. Advantages of Subqueries :
Select emp.Empname ,
( select deptname from Department dept where emp.deptno = dept.deptno) as deptname
Subqueries provide the better performance when we need use expressions , which experssion values used in outer query for comparision. In this case we do not have pther option that sub query
For Example :
To Get the employee ID's of all employees with minimum salary.
This query is difficult to convert in joins.
WHERE salary= (SELECT min(Salary) FROM Employee)
Limitations of Subqueries
1. Subqueries must return Sing values which will use for comparision.
2. The Distinct clause can not be used in subqueries that include group by.
3. view created by subquery can not be updated.
4. ntext , text , image data types cannot use in select list of subqueries.
If you want to learn more about subuery fundamentals refer to: