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);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s