Better Performance of DISTINCT and GROUP BY

Asked By a_kim34 200 points N/A Posted on - 07/22/2011

Hello,

I am confused. DISTINCT or GROUP BY which one has a better performance in Informix database? I am new to the Informix database. By the way, in my Select query there is no aggregate function. It’s a direct Select query with WHERE clause and I used DISTINCT, but one of my seniors told me that GROUP BY has a better performance than DISTINCT. Is it true? Please let me know the details or post any article url explaining this issue.

Thanks.

• Status: Open
• Question Views: 1694
• Answer Count: 2
• Vote Up 20 Vote Down

• Answer Accepted: Yes
• Question Category: Informix

Best Answer by Bartholomew

Go To Solution

Best Answer

Best Answer

Answered By Bartholomew 0 points N/A #84929

Better Performance of DISTINCT and GROUP BY

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. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:
SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:
SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:
SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

About Bartholomew

Questions
0

Answers
4

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 07/25/2011
• Question Category: Informix

Answered By JamesAlexander 0 points N/A #84930

Better Performance of DISTINCT and GROUP BY

DISTINCT & GROUP BY are each meant for its own purpose. They cannot be interchangeably used. DISTINCT is used for removing duplicates from the query result based on some attribute. Whereas GROUP BY is used to separate the result based on some attribute when we apply aggregate functions on the query.

As far as performance is concerned, both DISTINCT & GROUP BY gives the same query plan, ie there are hardly any performance difference.

So my suggestion is to use the keyword which best suits your requirement.

About JamesAlexander

Questions
0

Answers
10

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 09/19/2011
• Question Category: Informix