T-SQL: adding an extra table?

Question

Let's say I have a stored procedure that will execute complex logic when the @ComplicatedSearch = 1 parameter. When it is set to 1, I populate the @ValidIds variable table @ValidIds valid lines that this procedure can return. When it is 0, the logic bypasses, and we do not need to filter the returned rows.

So, skipping this logic, I get the following statement:

 SELECT m.* ,a.* FROM MyTable m INNER JOIN AdditionalInfoTable a ON m.Id = a.MyTableId WHERE (@ComplicatedSearch = 0 OR EXISTS(SELECT * FROM @ValidIds WHERE Id = m.Id)) 

This works great; however, I believe that it would be more efficient to attach MyTable to @ValidIds, if applicable, against using EXISTS() , especially when MyTable contains a large number of lines.

Is there a way to do something like the one below, without recording a few requests? (the actual query is very large, so having multiple versions with and without merging will not be ideal)

 SELECT m.* ,a.* FROM MyTable m ONLY DO THIS IF ComplicatedSearch = 1 PLEASE: INNER JOIN @ValidIds v ON m.Id = v.Id INNER JOIN AdditionalInfoTable a ON m.Id = a.MyTableId 

Answer 1

Another option:

 SELECT m.* ,a.* FROM MyTable m INNER JOIN @ValidIds v ON m.Id = case when @ComplicatedSearch = 1 then v.Id -- Filter rows else m.Id -- Select all rows end INNER JOIN AdditionalInfoTable a ON m.Id = a.MyTableId 

You will need to perform performance testing to ensure that it is efficient enough. Some quick tests showed that (according to my data) the same query plan was created regardless of whether the first call was complex or not complicated.

A “bifurcated” approach (separate procedures) should be the most effective. However, having the same code with little modification in two different places can be a serious support problem, especially when you need to add subsequent changes to all the "instances" of this code. And if the total size of the data (for example, the overall performance) is not too large, the approach “one size basically suits everyone” may be most effective.

Answer 2

If you are after efficiency, note that the stored procedures will calculate the query plan at the first start, and then cache it and use the same one for this. In this case, this means that it will use @ValidIds depending on the first value of @ComplicatedSearch

Thus, I would write a procedure more similar to

 if @ComplicatedSearch = 1 exec simple_search else exec complex_search 

If simple_search includes your first request and complex_search also joins @ValidIds

Yes, you get a request twice, but to understand that I will create a view

 create view helper as begin SELECT m.* ,a.* FROM MyTable m INNER JOIN AdditionalInfoTable a ON m.Id = a.MyTableId end 

and then a simple choice from this view and complex joins in @ValidIds