T-SQL: Optional table joins?
Let's say I have a stored procedure that will do complicated logic when the parameter @ComplicatedSearch = 1. When it is set to 1, I populate the variable table @ValidIds with valid rows that this procedure can return. When it's 0, the logic is bypassed and we don't need to filter out the rows to return.
So, past that logic, I end up with a statement like so:
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 fine; however, I believe it would be more efficient to join MyTable to @ValidIds when applicable opposed to using EXISTS(), especially when MyTable contains a large number of rows.
Is there any way of doing something like what I have below without writing multiple queries? (the actual query is very large, so having multiple versions with and without joins would 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
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'd need to do performance testing to see if it's efficient enough. Some quick tests showed that (on my data) the same query plan was generated regardless of whether the first call was for complex or not complex.
The "bifurcated" approach (separate procedures) should be most efficient. However, having the same code with just a minor modification in two different places can be a major pain to support, particularly when you have to add subsequent changes to all "instances" of that code. And if the overall size of the data (e.g. overall performance) isn't too great, the "one size mostly fits all" approach might be most effective.
If you are after efficiency you should note that stored procedures will calculate the query plan on the first run then cache it and use the same one thereafter. In this case it means that it will choose to use @ValidIds depending on the first value of @ComplicatedSearch
Thus I would write the procedure more like
if @ComplicatedSearch = 1 exec simple_search else exec complex_search
Where simple_search includes your first query and complex_search also joins to @ValidIds
Yest you get the query twice but to get over that I would 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 simple selects from that view and complex joins to @ValidIds