is UNION ALL on 2 SELECTs better performance-wise than 2 INSERTs into @Table and then SELECT * FROM @Table variable?

Question

is UNION ALL on 2 SELECTs better performance-wise than 2 INSERTs into @Table and then SELECT * FROM @Table variable?

VoldemarG

Hall of Fame

Points: 3883
More actions
December 26, 2013 at 1:43 pm

#301649

IF I have a SP in which the following actions occur:
1 - Declare @Table table (.......)
2 - INSERT INTO@Table SELECT F1, F2, F3, ... From ..Joins... WHERE Somefield= @Somefield_value
3 - INSERT INTO@Table SELECT F1, F2, F3, ... From ..Joins... WHERE SomeOTHERfield= @SomeOTHERfield_value
4 - SELECT * FROM @Table
Question: Can I expect some performance benefit if I rewrite the above 4 steps as one SELECT with OR in WHERE clause or
if I use 2 SELECTS with UNION ALL on 2 SELECT Statements?
Should UNION ALL or UNION be in most cases necessarily better performance-wise than using @Table variable or one big select statement?
Thanks for your ideas!
Likes to play Chess

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 1

vladisaev@hotmail.com (12/26/2013)
IF I have a SP in which the following actions occur:
1 - Declare @Table table (.......)
2 - INSERT INTO@Table SELECT F1, F2, F3, ... From ..Joins... WHERE Somefield= @Somefield_value
3 - INSERT INTO@Table SELECT F1, F2, F3, ... From ..Joins... WHERE SomeOTHERfield= @SomeOTHERfield_value
4 - SELECT * FROM @Table
Question: Can I expect some performance benefit if I rewrite the above 4 steps as one SELECT with OR in WHERE clause or
if I use 2 SELECTS with UNION ALL on 2 SELECT Statements?
Should UNION ALL or UNION be in most cases necessarily better performance-wise than using @Table variable or one big select statement?
Thanks for your ideas!

You'll have to test all three methods on representative sample data to know for sure which one will perform better in your environment and with your data. The availability of indexes on the tables will have a huge impact on performance, too. All other things being equal, I would expect that the SELECT . . . UNION ALL construct will usually perform better than the other two because it avoids the overhead of the table variable and the performance hit that sometimes accompanies OR conditions in WHERE clauses. In a simple test on some high-volume data (150+ million rows) with indexes on the columns in the WHERE clauses, I found this to be true. Again, YMMV.

Jason Wolfkill