3

I have a large SQL query with multiple statements and UNION ALL. I am doing something like this now:

DECLARE @condition BIT;
SET @condition = 0;

SELECT * FROM table1
WHERE @condition = 1;

UNION ALL

SELECT * FROM table2

In this case, table1 won't return any results. However, that query is complex with many joins (such as FullTextTable). The execution plan's estimate shows a high cost, but the actual number of rows and time to execute seems to show otherwise. Is this the most efficient way of filtering a whole query, or is there a better way? I don't want anything in the first select to run, if possible.

Nelson Rothermel
  • 9,436
  • 8
  • 62
  • 81
  • so, what you're trying to do is, if @condition is true, select from the first query and join to the second, otherwise just select the second query? – DForck42 Nov 08 '10 at 15:57
  • @DForck42: That's correct, but each select is complex and there are many selects, so think of a "scalable" solution, not a bunch of if/elses. – Nelson Rothermel Nov 08 '10 at 16:39

3 Answers3

2

I would imagine that your eventual SQL query with all of the unions and conditions that depend on pre-calculated values gets pretty complicated. If you're interested in reducing the complexity of the query (not to the machine but for maintenance purposes), I would go with a moving the individual queries into views or table valued functions to move that logic elsewhere. Then you can use the if @condition = 1 syntax that has been suggested elsewhere.

Jeff Hornby
  • 12,948
  • 4
  • 40
  • 61
1

i think you might be better off with this:

if (@condition=1)
begin

select * from table1
union all
select * from table2

end
else
begin

select * from table2

end
DForck42
  • 19,789
  • 13
  • 59
  • 84
  • In my case I have a lot of unions and the select statements are complex (30+ lines each). It's not feasible to create an if/else for each combination. I think your answer is reasonable for a simpler case, +1. – Nelson Rothermel Nov 08 '10 at 16:44
1

The best way to solve this is by using Dynamic SQL. The problem with DForck's solutions is that it may lead to parameter sniffing. Just to give a rough idea, your query might look something like this

DECLARE @query VARCHAR(MAX);

IF (@condition = 0) SET @query = 'SELECT * FROM table1 UNION ALL '

SET @query = @query + 'SELECT * FROM table2'

sp_executesql @query

This is just a simplified case, but in actual implementation you would parameterize the dynamic query which will solve the problem of parameter sniffing. Here is an excellent explanation about this problem Parameter Sniffing (or Spoofing) in SQL Server

Community
  • 1
  • 1
Sadhir
  • 413
  • 4
  • 13
  • 1
    +1 for parameter sniffing. I am not a DBA, but I had no idea SQL server did this sort of thing. Can you explain parameter sniffing a little more? Does the stored procedure only cache one plan total? What if the sproc has two completely unrelated queries? In DForck's answer, the execution plan for the first query could not be [fully] used for the second one, so how would that lead to parameter sniffing? Thanks! – Nelson Rothermel Nov 08 '10 at 20:15
  • yeah, sql server creates a plan for each stored proc and it should be just one for each stored proc. so the first time you run the stored procedure lets say @condition = 1. Then the plan generated would be optimized for the first query in DForck's answer. Now if you again call the stored procedure, but with @condition = 0, SQL server would use the same plan that was generated before, which is not what we want. – Sadhir Nov 08 '10 at 20:27
  • One other way to solve this problem is by creating two separate stored procedure - one that does SELECT * FROM table1 and the other that does SELECT * FROM table1 UNION SELECT * FROM table2 and then have a wrapper stored procedure that calls one of the two depending on the value for @condition. This way SQL server creates two separate plans for the two stored procedures. Hope that answers your question – Sadhir Nov 08 '10 at 20:28