Talk to Webmaster Tony John |
Resources » SQL Server
SQL SELECT Statement Performance Tuning Tips
In this Article I am going to Explain about SQL SELECT Statement Performance Tuning Tips. Performance Tuning is important for Increase the Performance of the application. Here You will learn About SQL Server and T-SQL Performance tuning techniques. Learn SQL SELECT Statement Performance Tuning Tips
|
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.
For example:
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.
Thanks
S.Suresh
|
Did you like this resource? Share it with your friends and show your love!
|
|
|
| Author: SonyMadhu 12 Jul 2012 | Member Level: Gold Points : 0 | Hi suresh,
Thanks for sharing this information, it is useful for me.
| | Author: Suresh Sellathambi 13 Mar 2013 | Member Level: Gold Points : 0 | Hi Sony Madhu,
Thanks for your Appreciation.
|
|
Active MembersTodayLast 7 Daysmore... |