DISTINCT vs. GROUP BY: Microsoft Access Speed Test (2023)

FAQs

Is distinct slower than GROUP BY? ›

DISTINCT is used to filter unique records out of all records in the table. It removes the duplicate rows. SELECT DISTINCT will always be the same, or faster than a GROUP BY.

What is difference between DISTINCT and GROUP BY? A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT.

Yes, the application needs to compare every record to the "distinct" records cache as it goes. You can improve performance by using an index, particularly on the numeric and date fields.

GROUP BY is a powerful statement, but it tends to slow down queries. Over time, my team and I have used it many times and defined SQL indexes to avoid the performance issues introduced by the GROUP BY clause, especially when dealing with large tables.

GROUP BY is slightly faster than SELECT DISTINCT

The difference is hardly noticeable to users. These tests were done on Access back-end database tables and may not hold for other types of data sources (e.g., SQL Server linked tables)

GROUP BY can (again, in some cases) filter out the duplicate rows before performing any of that work. The performance metrics, however, are interesting to compare. The DISTINCT variation took 4X as long, used 4X the CPU, and almost 6X the reads when compared to the GROUP BY variation.

For MAX and MIN , you probably shouldn't ever use DISTINCT because the results will be the same as without DISTINCT , and the DISTINCT function will make your query substantially slower to return results.

UNION ALL does return duplicates: this results in a faster query and could be useful for those who want to know what is in both SELECT statements. UNION DISTINCT is used in scenarios when we need unique based on the where conditions in the query.

When and where to use GROUP BY and DISTINCT. DISTINCT is used to filter unique records out of the records that satisfy the query criteria. The "GROUP BY" clause is used when you need to group the data and it should be used to apply aggregate operators to each group.

It's slow because the database is iterating over all the logs and all the dashboards, then joining them, then sorting them, all before getting down to real work of grouping and aggregating.

Is distinct faster than ROW_NUMBER? ›

In my experience, an aggregate (DISTINCT or GROUP BY) can be quicker then a ROW_NUMBER() approach.

A COUNT(DISTINCT key) basically has to pre-sort your data by key and sorting can be (very) expensive, especially on highly-cardinal data (with a lot of distinct values. A simple COUNT(*) just has to count number of rows - no sorting involved, so it will always be faster than COUNT(DISTINCT) .

The way to make a query run faster is to reduce the number of calculations that the software (and therefore hardware) must perform. To do this, you'll need some understanding of how SQL actually makes calculations.

“DISTINCT”: An Expensive Keyword

This requires: a lot of CPU — comparing large amounts of rows is hard work… loads of memory to store all rows — all rows need to be compared to each other, which means you need them all available in memory (even if only a hash, depending on low level algorithm implementation)

1.17 GROUP BY Optimization. The most general way to satisfy a GROUP BY clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any).

Group By with not be always be faster than Partition by... its more important to understand the semantics of how the work. - Group BY with hashout the keys and then apply distinct on it.. so If you have nested queries or Views then its a never ending story.

With 500 000 records in HSQLDB with all distinct business keys, the performance of DISTINCT is now better - 3 seconds, vs GROUP BY which took around 9 seconds.

The index helps group records with indexed columns used with the GROUP BY clause. SQL queries with the GROUP BY clause sort the columns included in the clause to find the result.

Does GROUP BY get rid of duplicates? ›

GROUP BY only treats two rows as duplicates if all the column values in both the rows are the same. If even a single column value in either of the row is non-matching, they are treated as unique.

Most of the SELECT DISTINCT queries will perform exactly as fast as their simple SELECT counterparts, because the optimizer will do away with the step necessary for eliminating duplicates.

So, is SQL DISTINCT good or bad in removing duplicates in results? The results say that it's good. It's not better or worse than GROUP BY because the plans are the same. But it's a good habit to check the execution plan.

Query performance also depends on data volume and transaction concurrency. Executing the same query on a table with millions of records requires more time that performing the same operation on the same table with only thousands of records.

Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.

Both UNION and UNION ALL operators combine rows from result sets into a single result set. The UNION operator removes eliminate duplicate rows, whereas the UNION ALL operator does not. Because the UNION ALL operator does not remove duplicate rows, it runs faster than the UNION operator.

Preserving performance through UNION

The UNION operation allows us to merge the results of two queries. Since we know that query #1 and query #3 are each significantly faster than query #2, we would expect that the results of the UNION operation will be fast as well.

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

The DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values. In other words, the DISTINCT keyword retrieves unique values from a table. (If you find the above syntax confusing, consider LearnSQL.com's SQL Basics course.

The distinct keyword is used in conjunction with select keyword. It is helpful when there is a need of avoiding duplicate values present in any specific columns/table. When we use distinct keyword only the unique values are fetched.

How do you optimize count distinct? ›

Some things to try: 1) change COUNT(subscriber_id) to COUNT(*) and see if performance improves. 2) try getting rid of COUNT(DISTINCT subscriber_id) and see if performance improves. Run each of the three subqueries you're combining with UNION ALL and see if one of them has poorer performance than the other two.

No, COUNT(*) will not go through the whole table before returning the number of rows, making itself slower than COUNT(1) .

Is COUNT(*) slow in MySQL? TL;DR: COUNT(*) is optimized to be fast, you should use it. You have probably read in a bunch of different places that you shouldn't use SELECT(*) in MySQL when you don't need all the data. SELECT(*) selects all the columns in the table, not just the ones that you might need.

GROUP BY lets you use aggregate functions, like AVG , MAX , MIN , SUM , and COUNT . On the other hand DISTINCT just removes duplicates. This will give you one row per department, containing the department name and the sum of all of the amount values in all rows for that department.

Some common synonyms of distinct are apparent, clear, evident, manifest, obvious, patent, and plain. While all these words mean "readily perceived or apprehended," distinct implies such sharpness of outline or definition that no unusual effort to see or hear or comprehend is required.

There is no difference. "1" is a non-null expression: so it's the same as COUNT(*) . The optimizer recognizes it for what it is: trivial.

With the help of the SQL count statement, you can get the number of records stored in a table.

Question: What is Faster, SUM or COUNT? Answer: Both are the same.

Indexing makes columns faster to query by creating pointers to where data is stored within a database. Imagine you want to find a piece of information that is within a large database. To get this information out of the database the computer will look through every row until it finds it.

What are the two query optimization techniques? ›

There are two types of query optimization in DBMS: Cost-Based Optimization and Adaptive Query Optimization.

Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance.

The architecture forms a massively parallel distributed tree for pushing down a query to the tree and then aggregating the results from the leaves at a blazingly fast speed.

Use a cache solution

Basically, when you cache a query, the database engine don't repeat the same query the next time it is requested. That, of course, saves time, reduces latency, and improves performance. You can apply your cache at different levels: At the Database engine level.

Increase Memory

One way DBAs tackle sql performance tuning issues is by increasing the memory allocation of existing databases. When SQL databases possess ample memory, boosts in efficiency and performance often follow.

For MAX and MIN , you probably shouldn't ever use DISTINCT because the results will be the same as without DISTINCT , and the DISTINCT function will make your query substantially slower to return results.

“DISTINCT”: An Expensive Keyword

This requires: a lot of CPU — comparing large amounts of rows is hard work… loads of memory to store all rows — all rows need to be compared to each other, which means you need them all available in memory (even if only a hash, depending on low level algorithm implementation)

In my experience, an aggregate (DISTINCT or GROUP BY) can be quicker then a ROW_NUMBER() approach.

Is distinct an expensive operation? ›

As you can see DISTINCT is an expensive CPU (and also memory) intensive operation.

The DISTINCT clause is used in the SELECT statement to filter out duplicate rows in the result set. You can use DISTINCT when you select a single column, or when you select multiple columns as we did in our example.

SQL COUNT Syntax:

If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count(*) is considerably faster than Count([Column Name]). If we use the following example table, we can demonstrate the use of the COUNT function. This will return the result of: 5 records.

As a general rule, SELECT DISTINCT incurs a fair amount of overhead for the query. Hence, you should avoid it or use it sparingly. The idea of generating duplicate rows using JOIN just to remove them with SELECT DISTINCT is rather reminiscent of Sisyphus pushing a rock up a hill, only to have it roll back down again.