SQL Server Stored Procedure - "IF statement" vs "Where Criteria"

Question

The question has been boiling for quite some time in my head that of the following two stored procedures that could be performed better.

Proc 1

CREATE PROCEDURE GetEmployeeDetails @EmployeeId uniqueidentifier, @IncludeDepartmentInfo bit AS BEGIN SELECT * FROM Employees WHERE Employees.EmployeeId = @EmployeeId IF (@IncludeDepartmentInfo = 1) BEGIN SELECT Departments.* FROM Departments, Employees WHERE Departments.DepartmentId = Employees.DepartmentId AND Employees.EmployeeId = @EmployeeId END END 

Proc 2

 CREATE PROCEDURE GetEmployeeDetails @EmployeeId uniqueidentifier, @IncludeDepartmentInfo bit AS BEGIN SELECT * FROM Employees WHERE Employees.EmployeeId = @EmployeeId SELECT Departments.* FROM Departments, Employees WHERE Departments.DepartmentId = Employees.DepartmentId AND Employees.EmployeeId = @EmployeeId AND @IncludeDepartmentInfo = 1 END 

The only difference between the two is the use of "if statment".

if proc 1 / proc 2 is called with @IncludeDepartmentInfo variables, then from my understanding proc 2 will work better because it will keep the same query plan, regardless of the value of @IncludeDepartmentInfo, whereas proc1 will change the query plan for every call

the answers are actually appericated

PS: this is just a scenario, please do not go to the explicit results of the query, but the essence of the example. I am really special with regard to the result of the query optimizer (in both cases “if and where” and their difference), many aspects that, as I know, can affect the performance that I want to avoid in this matter.

Answer 1

 SELECT Departments.* FROM Departments, Employees WHERE Departments.DepartmentId = Employees.DepartmentId AND Employees.EmployeeId = @EmployeeId AND @IncludeDepartmentInfo = 1 

When SQL compiles such a query, it must be compiled for any @IncludeDepartmentInfo value. The above plan may be one that scans the tables and performs the join, then checks the variable, which leads to unnecessary I / O. The optimizer may be smart and move the variable check before actual I / O operations in terms of execution, but this is never guaranteed. That's why I always recommend using explicit IFs in T-SQL for queries that need to be executed differently based on the value of a variable (a typical example is the OR condition).

gbn observation is also important: from an API design point of view, it is better to have a consistent return type (i.e. always return the same form and number of result sets).

Answer 2

In terms of consistency, number 2 will always return 2 data sets. Overloading to the side, you will not have a client code method that can return a result, or maybe not.

If you reuse this code, the other calling client will also need to know this flag.

If the code does 2 different things, then why not 2 different stored processes?

Finally, it is much better to use modern JOIN syntax and a separate join from filtering. In this case, I personally would use EXISTS too.

 SELECT D.* FROM Departments D JOIN Employees E ON D.DepartmentId = E.DepartmentId WHERE E.EmployeeId = @EmployeeId AND @IncludeDepartmentInfo = 1 

Answer 3

When you use the if statement, you can only run one request instead of two. I would think that one request will almost always be faster than two. Your point about query plans can be valid if the first request was complex and time-consuming, and the second was trivial. However, the first query looks like this: it retrieves one row based on the primary key - probably pretty quickly every time. So, I would keep the "if", but I would check to check.

Answer 4

The difference in performance will be too small for anyone to notice.

Premature optimization is the root of all evil. Beware of performance and start introducing features that make your customers smile.