alternate of Case statement for performance improvement of Query

• sushil_dwid

  Ten Centuries

  Points: 1241

  More actions

  October 12, 2015 at 1:53 am

  #301345

  Performance issue on Query. Here is query which i ran and it takes around 7-8 min to give the output. Could you please suggest the alternate way to write it.

  SELECT COUNT,MESS,

  count(distinct(CASE WHEN EVTIME=@D1 THEN EVENTUID ELSE null END)) AS 'D1'

  ,count(distinct(CASE WHEN EVTIME=@D2 THEN EVENTUID ELSE null END)) AS 'D2'

  ,count(distinct(CASE WHEN EVTIME =@D3 THEN EVENTUID ELSE null END)) AS 'D3'

  ,count(distinct(CASE WHEN EVTIME =@D4 THEN EVENTUID ELSE null END)) AS 'D4'

  ,count(distinct(CASE WHEN EVTIME =@D5 THEN EVENTUID ELSE null END)) AS 'D5'

  ,count(distinct(CASE WHEN EVTIME =@D6 THEN EVENTUID ELSE null END)) AS 'D6'

  ,count(distinct(CASE WHEN EVTIME =@D7 THEN EVENTUID ELSE null END)) AS 'D7'

  ,count(distinct(CASE WHEN EVTIME =@D8 THEN EVENTUID ELSE null END)) AS 'D8'

  ,count(distinct(CASE WHEN EVTIME =@D9 THEN EVENTUID ELSE null END)) AS 'D9'

  ,count(distinct(CASE WHEN EVTIME =@D10 THEN EVENTUID ELSE null END)) AS 'D10'

  ,count(distinct(CASE WHEN EVTIME =@D11 THEN EVENTUID ELSE null END)) AS 'D11'

  ,count(distinct(CASE WHEN EVTIME =@D12 THEN EVENTUID ELSE null END)) AS 'D12'

  ,count(distinct(CASE WHEN EVTIME =@D13 THEN EVENTUID ELSE null END)) AS 'D13'

  ,count(distinct(CASE WHEN EVTIME =@D14 THEN EVENTUID ELSE null END)) AS 'D14'

  FROM DBO.#TEMP C WITH(NOLOCK)

  GROUP BY COUNT,MESS

  ORDER BY 1,2

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 12, 2015 at 2:36 am

  #1832753

  Duplicate post. No replies here please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1727146-3077-1.aspx

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass