Tips for Writing Efficient SQL Queries

In order to improve overall application performance, it’s very important to construct SQL queries in the most efficient way. There can be many different ways to write a SQL query. Here are few tips that can help you in writing efficient SQL queries.

Name the Columns in a Query: There are three good reasons why it is better to name the Columns in a query rather than to use “select * from... 

1. Network traffic is reduced.
This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column (both of which can be up to 2 GB in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.
2. The code is easier to understand
3. It could save the need for changes in the future. If any columns is added to or removed from the base table/view, then select * statement can produce wrong results set and statement may fail.

Use table alias: Always use table alias and prefix all column names with the aliases when you are using more than one table.

Use EXISTS instead of DISTINCT: Use EXISTS in place of DISTINCT if you want the result set to contain distinct values while joining tables.

For example:

1
2
3
SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE d.deptno = e.deptno;

The following SQL statement is a better alternative.

1
2
3
4
5
SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (SELECT e.deptno
FROM emp e
WHERE d.deptno = e.deptno);