sqlsql-serverjoinleft-joininner-query

SQL Performance: Filter first or Join first

I am having three tables viz., Employees, Department and Grievance. The Employees table has over a million records. I need to find the details of the employee, his/her department and grievances raised by him/her.

I could think of the below two queries to find the result:

1. Filtering the records first to get only the records of the employee whose data is required:

SELECT * FROM (SELECT * FROM Employees WHERE EmployeeID= @EmployeeID) Emp    
LEFT JOIN Department Dpt ON Emp.EmployeeID= Dpt.EmployeeID    
LEFT JOIN Grievance Grv ON Emp.EmployeeID= Grv.EmployeeID;

2. Join first:

SELECT * FROM Employees Emp    
    LEFT JOIN Department Dpt ON Emp.EmployeeID= Dpt.EmployeeID    
    LEFT JOIN Grievance Grv ON Emp.EmployeeID= Grv.EmployeeID    
WHERE EmployeeID= @EmployeeID);

If we consider the SQL logical processing order which starts with FROM>INNER JOIN>OUTER JOIN>WHERE>....SELECT, the first query should perform better/fast as there will be only one record from the Inner Query and will be joined with the further tables. However, on executing both the queries I do not find any performance difference and both queries takes almost same time.

Could you please check and let me know where I am thinking wrong?

Solution

  • Don't worry about it. The processing of the query occurs in three phases:

    1. Parsing
    2. Compilation
    3. Execution

    A key part of the compilation phase is optimization. This is when the SQL engine determines the optimal execution plan.

    In your first query, SQL Server is going to ignore the subquery. The two queries should have the same execution plan.

    Note: This is not true in all databases. Some simpler databases actually materialize the subquery.

    From an aesthetic perspective, I prefer the second query -- just to avoid an unnecessary subquery and so all the filtering is in the outer where clause (where it is expected).