Rewrite of TSQL query

Question

Hello Team,

Can below query be rewritten in a better way to improve performance?

SELECT DISTINCT HRA.Role AS RoleID,RR.RoleName
from [FIndb].DBO.VW_Finance_Attract HRA WITH(NOLOCK)
LEFT OUTER JOIN (SELECT JOB_TASK AS RoleID,DESCR AS RoleName
FROM (SELECT DISTINCT DENSE_RANK() OVER (PARTITION BY JOB_TASK ORDER BY EFFDT DESC) AS RANK ,
JOB_TASK,DESCR,STATUS
FROM [FIndb].[dbo].[vw_FIndb_JobTask] WITH(NOLOCK)
where STATUS='C'
) A where A.Rank=1
group by JOB_TASK,DESCR
) RR ON LTRIM(RTRIM(RR.RoleID))=LTRIM(RTRIM(HRA.Role))

What I see is the Table inside view vw_FIndb_JobTask has close to 2 Million records and all 2 million records are read when this query executes. Any help be much appreciated.

Regards;
Yesu

Answer 1

I think you can use OUTER APPLY in your query and simplify it, e.g.

SELECT DISTINCT HRA.Role AS RoleID, RR.DESCR AS RoleName  
from [FIndb].DBO.VW_Finance_Attract HRA   
OUTER APPLY (select top (1) * from   
[FIndb].[dbo].[vw_FIndb_JobTask]  t where t.JobTask = HRA.Role  
and t.Status = 'C' ORDER BY EFFDT DESC) RR  
  
  

Hopefully it is the same result.

Answer 2

We don't know the tables, and we don't know the definition of the views, which leaves us quite much in the dark. As a start, you can upload the actual execution plan on http://www.pastetheplan.com.

There are two things I can say about this query though:

1) The NOLOCK hints means "I don't care if I get correct results or not". I strongly recommend that you remove these hints.
2) The condition LTRIM(RTRIM(RR.RoleID))=LTRIM(RTRIM(HRA.Role)) will render any indexes on these columns useless. RTRIM should not be needed anyway, because trailing blanks are not significant in the = operation. The LTRIM may be needed if you have data with leading blanks, but if so, this is something you should look into.

Answer 3

Hi,@Yesuraj

Please try this way:

;with cte as  
(  
  SELECT DISTINCT DENSE_RANK() OVER (PARTITION BY JOB_TASK ORDER BY EFFDT DESC) AS RANK ,  
    JOB_TASK,DESCR,STATUS  
  FROM [FIndb].[dbo].[vw_FIndb_JobTask]   
  where STATUS='C'  
),cte1 as  
(  
   SELECT cte.JOB_TASK AS RoleID,cte.DESCR AS RoleName,a.role as role  
   FROM cte  
  join [FIndb].DBO.VW_Finance_Attract  a on a.Role=cte.JOB_TASK  
and  cte.RANK=1  
group by JOB_TASK,DESCR  
)  
  
select DISTINCT cte1.Role AS RoleID,cte1.RoleName from cte1  

Bert Zhou