optimizing query by removing distinct key word

Question

I am trying to tune the one query to increase the performance of the stored function by removing the distinct keyword.

in the process of query tuning, I came across the query in the stored function where the distinct keyword is using due to that performance is degrading, so I am trying to write the query with same functionality without using a distinct keyword.

Current code with distinct keyword:-

select distinct dm.strdatadest 
            from PUBLIC.temp te1
            JOIN PUBLIC.applicationconfiguration AC on AC.intapplicationid = te1.applicationid
            JOIN columnmapping cm on cm.intapplicationid = AC.intapplicationid
            JOIN datamapping dm on lower(dm.strcolumnsource) = lower(cm.strcolumnsource)
            JOIN srctable s on s.applicationid=AC.intapplicationid
            where lower(cm.strtablesource) = lower(s.tablename) and 
            lower(dm.strdatasource) = lower('||quote_literal(rec.status) ||') and lower( dm.strcolumndest ) LIKE ''strstatus'' and te1.applicationid='||quote_literal(rec.applicationid);

Trying code by removing distinct keyword:-

select  dm.strdatadest
from PUBLIC.temp te, 
PUBLIC.applicationconfiguration AC,
            PUBLIC.columnmapping cm,
            PUBLIC.datamapping dm
            where AC.intapplicationid = te.applicationid and cm.intapplicationid = AC.intapplicationid and lower(dm.strcolumnsource) = lower(cm.strcolumnsource)
            and lower(cm.strtablesource) = lower(s.tablename) and 
            lower(dm.strdatasource) = lower('||quote_literal(rec.priority) ||') and lower( dm.strcolumndest ) LIKE ''strpriority'' and te1.applicationid='||quote_literal(rec.applicationid)
            GROUP BY dm.strdatadest;

i required some sujjestion who to tune query by removing distinct keyword

GROUP BY instead of SELECT DISTINCT? What do the queries' execution plans say? — jarlh, Apr 05 '19 at 09:38
changing a distinct clause with an group by seems not a good idea .. in the better case you have the same performance . .. — ScaisEdge, Apr 05 '19 at 09:44
Your probably is probably not the `select distinct` but instead the `join` conditions that use functions. — Gordon Linoff, Apr 05 '19 at 14:52

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

There are two points about your query that should be mentioned

implicit join vs explicit join which approximately has the same performance.

people often ask if there is a performance difference between implicit and explicit joins. The answer is: “Usually not”

distinct vs group by which distinct is optimum for memory usage and group by is optimum for speed so the latter outperforms the former but requires a large amount of memory if needed.

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

An astute explanation on the links below.

implicit join vs explicit join

distinct vs group by

optimizing query by removing distinct key word

1 Answers1