Distinct is taking a lot of time and want to avoid that

• skmoh2

  SSChasing Mays

  Points: 625

  More actions

  February 13, 2019 at 11:05 pm

  #382721

  Hi , I have a query which is taking a lot of time because of a Distinct column.Ca any one suggest how can i remove the distinct and replace with any other functions like PARTITION BY to get the output.
  
My Query:

  SELECT
      date
      , NAME
      , LONG
      , LAT
     , COUNT ( DISTINCT IMEI) + COUNT ( DISTINCT CASE WHEN IMEI IS NULL THEN 1 END) AS NUMBER_OF_DISTINCT_USERS
     , MAX(MAT) MARKET
      FROM poc.CP_TEST
      WHERE date = to_timestamp('20190130','YYYYMMDDHH24') - INTERVAL '1 DAY'
      and MAT like '%Light%'
     GROUP BY
      date
     ,NAME
     ,LONG
     ,LAT

  I want to replace that distinct with some other functions...

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 14, 2019 at 5:01 am

  #2022040

  skmoh2 - Wednesday, February 13, 2019 11:05 PM

  Hi , I have a query which is taking a lot of time because of a Distinct column.Ca any one suggest how can i remove the distinct and replace with any other functions like PARTITION BY to get the output.
  
  My Query:

  SELECT
        date
        , NAME
        , LONG
        , LAT
       , COUNT ( DISTINCT IMEI) + COUNT ( DISTINCT CASE WHEN IMEI IS NULL THEN 1 END) AS NUMBER_OF_DISTINCT_USERS
       , MAX(MAT) MARKET
        FROM poc.CP_TEST
        WHERE date = to_timestamp('20190130','YYYYMMDDHH24') - INTERVAL '1 DAY'
        and MAT like '%Light%'
       GROUP BY
        date
       ,NAME
       ,LONG
       ,LAT

  I want to replace that distinct with some other functions...

  This kind of exercise is mostly guesswork without some details of the data distribution and number of rows encountered by the query, most of which can be provided by an execution plan. Can you attach one please? Best would be an "actual"plan, as a .sqlplan attachment.
  Thanks.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  February 14, 2019 at 5:55 am

  #2022051

  I think you are asking a question about Oracle in a SQL Server forum.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 14, 2019 at 6:51 am

  #2022059

  Forget about getting the benefit of indexes because of the following snippet:
  and MAT like '%Light%'"

  Every row is going to have to be read, and with a large table, that's going to take a lot of time.
  Probably not much you could to this for that reason.   The DISTINCT count is also part of the
  problem, but an index on that column would eliminate that piece.   Just don't expect that to be
  the solution to the problem.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)