Execution order in complicated queries

Posted on

Question :

I know the query execution order for simple queries.

However, I get confused understanding the exact execution order in this query :

select 
      M.DateKey  As DateKey ,
      PersonnelCode As PersonnelCode,
      Isnull((select top 1 BranchCode 
                  from [Financial].[viwPersonnelLocation]
              where PersonnelCode = p.PersonnelCode and DateKey <= M.DateKey 
                  order by DateKey desc)
                  ,p.BranchCode) As BranchCode,

          Isnull((select top 1 CertficateCode
                   from [Financial].[viwPersonnelEducation]
                   where PersonnelCode = p.PersonnelCode and DateKey <= M.DateKey 
                   order by DateKey desc)
                   ,p.Educationcode) As Educationcode

    from (select DateKey,MonthOfYearKey,EDATE 
          from [Common].[DimTime] 
          where LastDayOfMonthKey = 1) M

                Inner Join 

               [Financial].[viwPersonnelInfo] As P  
                   on m.DateKey >= p.DateOfEmployeement
    where (m.DateKey <= p.DateOfLeave) or (p.DateOfLeave='' or p.DateOfLeave is null) and m.EDATE <= getdate()

Answer :

You should look at the execution plan for the exact order. As the plan depends on heuristics based on indexes, statistics and the like, you can’t tell simply from the query.

So hit Ctrl-L in SSMS and look at the execution plan. Each operator calls the one on its right, requesting rows as needed.

The physical order in the execution plan may be quite different from the logical order expressed in the query. Itzik Ben-Gan has a good flowchart.

Leave a Reply

Your email address will not be published.