Hi All,

Distint or Group By which one is having better performance in informix database?

In my Select Query there is no aggregate function. Its direct select query with WHERE clause.
I am using Distinct method... But one of senior person told me, group by is having better performance than Distinct?
Is it true?
Please let me know in details or post any article url regarding this issue..
Thanks.
Antony

Hi,

Distinct you use when you want only a single record. When you use group by you aggregate information you are querying database to obtain several results. I´m not a specialist in the way how Informix work inside doing their job, but for my experience there is no great deference in performance, because in distinct the server must obtain the results of the query and discard what is no longer necessary, in group by, joins must be made and must be returned to you as the same. I´ve tried both and i couldn't obtain great performance difference.

Luis.

Sometimes a group by can use an index to avoid a sort. If there is no suitable index to use, then you are no better off with one over the other.
Dave