CODEWITHSUNDEEP
sqlsql-servert-sql
Corovei Andrei
Corovei Andrei

Reputation: 1668

sql tricky query with distinct

Can anyone help me rewrite this query without a distinct clause?

   select     
       a.rec_type ,
       count(distinct a.a_tel_id) as count_distinct,       
       a.time_key as hour 
   from v_f_view as a with (nolock)
   group by rec_type,time_key

I foud with the execution plans that this query takes too long and I want to optimize it.

query plan : http://postimage.org/image/p1myi9tw/

Upvotes: 1

Views: 469

Answers (3)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

Indexes

Your problem is not the DISTINCT clause but the lack of indexes. The DISTINCT clause could be replaced by two GROUP BY clauses but this will most likely suffer the same performance penalty.

SQL Statement

SELECT  a.rec_type
        , COUNT(*) as count_distinct
        , a.time_key as hour
FROM    (        
          SELECT a.rec_type
                  , a.a_tel_id
                  , a.time_key
          FROM    v_f_Logicacdrs21 a        
          GROUP BY
                  a.rec_type
                  , a.a_tel_id
                  , a.time_key
        ) a                  
GROUP BY
        a.rec_type
        , a.time_key

Upvotes: 2

catalinux
catalinux

Reputation: 1452

Do you have rec_type,time_key indexed as a key? This is a must.

Upvotes: 2

Petar Ivanov
Petar Ivanov

Reputation: 93090

If a_tel_id has duplicates and you want to count them as one, then you can't optimize it by somehow magically remove distinct.

You might try to add index on the columns on which you group by.

Upvotes: 2

Related Questions