There are number of ways in which the most basic elements of SQL code can be constructed to ensure good processing performance. The best way to tune performance is to try to write your queries in a number of different ways and compare their reads and execution plans. Sometimes the reason for the slow response time is due to the fact that the query is not written to perform as efficiently as possible.
Following is the order in which SQL elements get executed as this will change the way we can optimize SELECT queries:
- FROM Clause
- GROUP BY
- ORDER BY
In the SELECT statement, the FROM clause is the first element that will execute. This is the place where we can narrow down possible record set sizes by implementing appropriate ON conditions in JOINS. This way we have a smaller record set to handle for the WHERE clause and can increase the query processing performance.
The SELECT command allows use of a WHERE clause, reducing the amount of data read. Again we can use this clause to filter down the record set and increase the performance of the query. There are different types of comparison conditions. The way records are filtered in a query can impact both the way a query executes and performance. Indexing has a significant effect on how well the WHERE clause filtering performs.
The GROUP BY clause is used to aggregate records into summarized groups of records retrieved from a database. Groupings are best achieved as a direct mapping onto one-to-many relationships between tables.
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. Try to avoid the HAVING Clause. In Select statements HAVING clause is used to filter the rows after all the rows are selected and is used like a filter.
Use specific column names in the SELECT clause instead of using ‘*’. This will help with optimizing the performance of the query.
The ORDER BY clause sorts the record set according to the provided columns.
The TOP clause picks up the TOP n records. TOP will execute after the ORDER BY clause and return top n sorted records. If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.