sql tricky query with distinct

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

开发者_StackOverflow

   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/

---

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

---

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.

---

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