Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0332 - SQL Server - Myths - Is DISTINCT faster than GROUP BY?

Apr 14 2014 12:00AM by Nakul Vachhrajani   

I have often seen a recommendation that using DISTINCT instead of GROUP BY is better from a performance perspective. DISTINCT is definitely a more readable alternative, but I wanted to test for myself as to what the truth was.

To compare the performance of the two, I wrote the following simple queries.

The query below helps in identifying the number of records that SQL Server will need to process. On my machine, when executed without the GROUP BY or the DISTINCT clause, the query generates records in excess of 380,000.

USE AdventureWorks2012;
GO
SELECT  COUNT(so1.object_id)
FROM    sys.objects AS so1
        CROSS JOIN sys.objects AS so2 ;
GO

The queries below fetch the same information – one uses the GROUP BY whereas the other query uses a DISTINCT.

USE AdventureWorks2012;
GO
--Clean the buffers and procedure cache
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO

--Evaluate GROUP BY
SELECT  so1.object_id,
        so1.name
FROM    sys.objects AS so1
        CROSS JOIN sys.objects AS so2
GROUP BY so1.object_id, so1.name ;
GO

--Clean the buffers and procedure cache
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO


--Evaluate DISTINCT
SELECT  DISTINCT
        so1.object_id,
        so1.name
FROM    sys.objects AS so1
        CROSS JOIN sys.objects AS so2 ;
GO

Before executing the two queries (one with the GROUP BY and the other with the DISTINCT), I had turned on the actual execution plan.

image

Upon studying the execution plan, the following observations can be made:

  1. When the SQL server database engine detected that the 2nd query did not have any aggregation function, it was smart enough to convert the GROUP BY into a DISTINCT when the query plan is generated
  2. There is no difference in the execution plan across both the queries

In conclusion,

DISTINCT is not faster than GROUP BY. In fact, Both DISTINCT and GROUP BY demonstrate equal performance impact.

When aggregations are not being used, DISTINCT is more readable when compared to GROUP BY.

Until we meet next time,

Be courteous. Drive responsibly.

Tags: #SQLServer, SQL Server, Administration, DBA, T-SQL, #TSQL, Development, Performance Tuning, Guidance, Myths


Nakul Vachhrajani
4 · 36% · 11648
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"#0332 - SQL Server - Myths - Is DISTINCT faster than GROUP BY?" rated 5 out of 5 by 2 readers
#0332 - SQL Server - Myths - Is DISTINCT faster than GROUP BY? , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]