Optimizing queries in SQL

Optimizing queries in SQL

In the previous SQL post we had covered a few tips on how to enhance SQL servers' performance by having a quick glimpse of certain critical metrics. This post is aimed at enabling faster queries so you can save a lot time while getting the same desired output.

 

Tip 1: Right use of indexing.

Indexes are generally quite useful when your query is designed to run full table scans since they can help save huge amounts of time.

 

Consider this example:


SELECT ID, FIRSTNAME FROM BookDetails ORDER BY PURCHASEDATE LIMIT 10


10 rows in set (9.96 sec)

(Using file sorting on PURCHASEDATE column for over 7.5 million rows)

 

Clearly, this cannot be a viable method as the time taken to fetch the information indicates. Here's what we can do.

 

Solution:

> Create a secondary index on the JOINDATE column

Query: CREATE INDEX

BookDetails_IDX1 ON BookDetails (PURCHASEDATE)

 

After index creation:

SELECT ID, FIRSTNAME FROM BookDetails ORDER BY PURCHASEDATE

LIMIT 10

10 rows in set (0.01 sec)

 

Notice the difference?

 

Tip 2: Using joins instead of subqueries


The values returned as a result of using subqueries in SELECT statements are usually distinct. And distinct values often require additional processing thereby slowing down your queries.

Also, if you are looking for many rows it is advisable not to use subqueries although in some cases using correlated subqueries could be inevitable.

 

Tip 3: Only retrieve the data that is needed!


Using the * symbol is a common way of retrieving the desired columns even when not all columns are really required.


SELECT * FROM users WHERE height > 5;

 

Assuming there are large data sets, it is always recommended that you specify the columns in order to save a lot of computation time.


SELECT id, last_name, sex, nationality FROM users WHERE height > 5;

 

Similarly, when it comes to rows, it is recommended that the LIMIT clause is used whenever possible. This again allows the database to stop its search after retrieving information within the specified limit.

 

 

Improving SQL query performance is a topic that has been subject to a lot of discussion over the years. Users always look for a fast response on their data retrieval actions. There is however, no simple straightforward method to achieve this. Re-writing SQL query, adding and removing indexes are only a few methods to achieve faster querying. A thorough understanding of the SQL environment and analyzing usage patterns regularly could also go a long way in optimizing query performance. 


More on SQL in the next edition!

              New to ADManager Plus?

                New to ADSelfService Plus?