About SQL SELECT Statement Performance Tuning Tips
In this Article I am going to Explain about SQL SELECT Statement Performance Tuning Tips. Here You will learn About SQL Server and T-SQL Performance tuning techniques.
The DISTINCT clause creates a lot of extra work for SQL Server, and reduces the physical resources that other SQL statements have at their disposal. Because of this, only use the DISTINCT clause only if it is necessary.
Check to see if the data being returned has any chances of returning duplicates. If not, remove the DISTINCT clause.
Usually it is better to rewrite the query's FROM and WHERE clauses to use a sub query to filter the data correctly so that you only get back what you want.
Also, when using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set.
In other words, UNION takes the results of two like Recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows.
This process occurs even if there are no duplicate records in the final Recordset.
So, if you know that there will never be any duplicate rows, or if there are, and this presents no problem to your application, then you should use the UNION ALL statement instead of the UNION statement.
The advantage of UNION ALL is that is does not perform the SELECT DISTINCT function, which saves a lot of unnecessary SQL Server resources from being used.
Following are 2 simple & powerful SQL tips which will help in writing better performing queries
1. If you need to verify the existence of a record in a table, don't use SELECT COUNT (*) in your Transact-SQL code to identify it, which is very inefficient and wastes server resources. Instead, use the Transact-SQL IF EXISTS to determine if the record in question exists, which is much more efficient.
Here's how you might use COUNT(*):
IF (SELECT COUNT(*) FROM tablename WHERE columnname = 'ABC')
Here's a faster way, using IF EXISTS:
IF EXISTS (SELECT 1 FROM tablename WHERE columnname = 'ABC')
The reason IF EXISTS is faster than COUNT(*) is because the query can end immediately when the text is proven true, while COUNT(*) must count go through every record, whether there is only one, or thousands, before it can be found to be true.
2. EXISTS drives from out-to-in. i.e. For each row returned by the outer query, the database executes the join in the sub-query. INs drive from in-to-out. If the outer query is more selective than the inner one, use an EXISTs. If the inner query is more selective, use an IN. Accordingly, change this
SELECT ... FROM tablenameD D WHERE EXISTS (SELECT 1 FROM tablenameE E WHERE E.ID=D.ID)
to this …
SELECT ... FROM tablenameD D WHERE D.ID IN (SELECT E.ID FROM tablenameE E)
I think It will help you about SQL SELECT Statement Performance Tuning Tips. Thanks for reading my Article SQL SELECT Statement Performance Tuning Tips . if you have any query or you have any suggestion, let me know. I will appreciate you valuable feedback.
The SQL standard names are LOWER and UPPER, not LCASE and UCASE. Some pcoudrts like MySQL alias LCASE and UCASE to the LOWER and UPPER functions for increased compatibility with other non-standard pcoudrts and some pcoudrts that are not databases. MS Access uses LCASE and UCASE as does the non-database pcoudrts Excel and OOCalc. There are some programming languages which use LCASE and UCASE. There may be other DB pcoudrts that do not use the SQL standard LOWER/UPPER names for these functions. Oracle does use LOWER/UPPER. DB2 supports both. PostgreSQL uses LOWER/UPPER.
Thanks for sharing this information, it is useful for me.
Hi Sony Madhu,
Thanks for your Appreciation.