1

I have a query with the following structure. In some specific cases the query will return data only from 1 or 2 of the tables.

For example: 

DECLARE @Var1 int --Parameter in store procedure

SELECT several fields FROM T1 WHERE @Var IN(0,1) AND Conditions 
 UNION 
SELECT several fields FROM T2 WHERE @Var IN(0,1,2) AND Conditions 
 UNION
SELECT several fields FROM T3 WHERE @Var IN(0,2) Conditions

My question is regarding performance. What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

IF @Var = 0
    SELECT several fields FROM T1 WHERE Conditions 
     UNION 
    SELECT several fields FROM T2 WHERE Conditions 
     UNION
    SELECT several fields FROM T3 WHERE Conditions 
ELSE 
 BEGIN
  IF @Var = 1
     SELECT several fields FROM T1 WHERE Conditions 
       UNION 
     SELECT several fields FROM T2 WHERE Conditions 
  ELSE 
     SELECT several fields FROM T2 WHERE Conditions 
       UNION
     SELECT several fields FROM T3 WHERE Conditions 
 END

I'd rather not to use the IF option because the query is quite big with several Fields and Conditions, however, I did check the execution plan of the first query and it shows an Index Scan.

I will appreciate any suggestion or comment

Improve this question
2
  • 1
    Regardless of the question, are you sure you need UNION? If you can't have same data from different tables, you can use UNION ALL, which is often a perfromance gain.
    – ypercubeᵀᴹ
    Commented Sep 25, 2019 at 21:24
  • In fact the real query uses UNION ALL @ypercubeᵀᴹ
    – Ayorus
    Commented Sep 25, 2019 at 21:27

1 Answer 1

Reset to default
3

What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

In SQL Server a query plan may not vary based on the parameters passed (unless you disable plan caching with OPTION RECOMPILE). So whenever you want different plans, you need seperate queries.

Improve this answer
3
  • I am looking for better performance. I don't think the UNION query is a good option because It producess Index Scans. Even though the scalar variable should avoid the whole query. The question is whether that Index Scan really executes or it is avoided by the relational engine
    – Ayorus
    Commented Sep 26, 2019 at 14:40
  • 1
    "The question is whether that Index Scan really executes or it is avoided by the relational engine" You can answer that yourself by running with SET STATISTICS IO ON, and SET STATISTICS TIME ON.
    – David Browne - Microsoft
    Commented Sep 26, 2019 at 17:20
  • Thanks for the guidance
    – Ayorus
    Commented Sep 26, 2019 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.