Rewrite of TSQL query

Yesuraj 41 Reputation points
2022-07-07T12:21:45.38+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi Nosonovsky 8,146 Reputation points
    2022-07-07T13:51:53.89+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 116.8K Reputation points MVP
    2022-07-07T12:44:31.563+00:00

    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.


  2. Bert Zhou-msft 3,436 Reputation points
    2022-07-08T02:22:37.197+00:00

    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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.