SQL query tuning tips

“A solution is a state when you are tired of thinking“
This proverb indeed holds good for us in majority of the situations. The solutions that we provide to our customers have room for improvement by all means.
I have tried to list down a few of observations I made in my SQL performance tuning exercises. And I hope it is useful for the readers as well..

Using a Subset in SELECT:
It is always advisable to use the subset of fields that you require to process in a SELECT statement rather than *. Using a SELECT * consumes more resource and time in turn reducing the overall performance of the system.

IN / BETWEEN – Which clause works well for me ?
In case of a choice of using the IN or the BETWEEN clauses in the query, it is advantageous to use the BETWEEN clause, as it is much more efficient.
For example:
SELECT empid,empnameFROM employeeWHERE empid in (1,2,3,4,5,6,7,8,9,10);
is less efficient than:

SELECT empid,empnameFROM employeeWHERE empid BETWEEN 1 and 10;

Assuming there is a useful index on empid, the Query Optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause. As it is implied, checking for the list is going to go for N comparisons and BETWEEN would ideally be going for a check against the lower limit and the upper limit.
LIKE clauseIf LIKE is used in a WHERE clause, it is better to try to use one or more leading character in the clause, if at all possible.
For example :
Use
LIKE 'a%' not LIKE '%'
If a leading character is used in the LIKE clause, the query optimizer has the ability to potentially use an index to perform the query thereby, speeding performance. But if the leading character in a LIKE clause is a wildcard, a complete table scan has to be performed to process the query which in turn would bring down the performance.

DISTINCT clauseAt times this clause is added to every SELECT statement, even when it is not necessary. The DISTINCT clause should be used in SELECT statements when it is known that there would be duplicates and having duplicate rows in the result set would cause problems. DISTINCT would return the complete resultset to the temporary space, performs a SORT on the rows and eliminates the duplicates. So it uses the resources exhaustively.The DISTINCT clause reduces the efficiency if not used properly.

I would share some more in near future…Happy Reading !!!