Huge performance difference when using GROUP BY vs DISTINCT

by

The two queries express the same question. Apparently the query optimizer chooses two different execution plans. My guess would be that the distinct approach is executed like:

  • Copy all business_key values to a temporary table
  • Sort the temporary table
  • Scan the temporary table, returning each item that is different from the one before it

The group by could be executed like:

  • Scan the full table, storing each value of business key in a hashtable
  • Return the keys of the hashtable

The first method optimizes for memory usage: it would still perform reasonably well when part of the temporary table has to be swapped out. The second method optimizes for speed, but potentially requires a large amount of memory if there are a lot of different keys.

Since you either have enough memory or few different keys, the second method outperforms the first. It’s not unusual to see performance differences of 10x or even 100x between two execution plans.

More Related Contents:

  1. Is there any difference between GROUP BY and DISTINCT
  2. Group by minimum value in one field while selecting distinct rows
  3. Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
  4. What is the best way to paginate results in SQL Server
  5. Best way to select random rows PostgreSQL
  6. SQL/mysql – Select distinct/UNIQUE but return all columns?
  7. must appear in the GROUP BY clause or be used in an aggregate function
  8. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  9. GROUP BY to combine/concat a column [duplicate]
  10. JOIN two SELECT statement results
  11. How to use DISTINCT and ORDER BY in same SELECT statement?
  12. SQL ‘like’ vs ‘=’ performance
  13. How many rows in a database are TOO MANY?
  14. SQL Group by & Max
  15. SQL to Entity Framework Count Group-By
  16. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  17. Eliminating duplicate values based on only one column of the table
  18. Why is a UDF so much slower than a subquery?
  19. How to see all the tables in an HSQLDB database?
  20. GROUP BY without aggregate function
  21. SQL Server SELECT LAST N Rows
  22. Improving performance of cluster index GUID primary key
  23. Get values from first and last row per group
  24. How to return a incremental group number per group in SQL
  25. sql group by versus distinct
  26. How to get list of values in GROUP_BY clause?
  27. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  28. Challenge,how to implement an algorithm for six degree of separation?
  29. Entity Framework Skip/Take is very slow when number to skip is big
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment