SQL Server Performance

increasing performance of below query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by avril, Mar 27, 2009.

  1. avril New Member

    Select TOP 12'icd_Rec' As Rec_Type,'S11' AS CATG,Lastdate1,Date_End,Count1From(Select 'icd_Rec' As Rec_Type,(CASE WHEN TKNAME IN ('S11-TD-TWD','S11-TD-TWD-sfw') THEN 'S11' ELSE ' ' END) AS CATG ,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Date_End, 106), 8), ' ', '-') AS Lastdate1 ,CONVERT(CHAR(6),IR.Date_End, 112) AS Date_End ,Count(Reg_ID) As Count1FROM dbo.vsrdt_ttp_dt_comp IRWHERE Status ='Closed'AND PRT_ID is NULLAND TKNAME IN ('S11-TD-TWD','S11-TD-TWD-sfw')AND Date_End Between dateadd(mm,-12,getdate()) and getdate()group by (cASE WHEN TKNAME IN ('S11-TD','S11-sfw') THEN 'S11' ELSE ' ' END) ,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Date_End, 106), 8), ' ', '-') ,CONVERT(CHAR(6),IR.Date_End, 112)) aOrder by Date_End
  2. MohammedU New Member

    Welcome to the forum....
    If you use convert/dateadd or any other functions against the columns in where clause will cause the optimizer to table/cluster index scan instead using the index even if one exists...

Share This Page