Is there any way to speed up a query with 3x UNION on the same large table?

Question

Assuming that the following query is legitimate and executes successfully, is there any way to speed it up?

SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes
UNION
SELECT bar from T1
UNION
SELECT baz from T1
UNION
SELECT qux from T1

The four columns shown above are all of type nvarchar(128), non-sparse and nullable. I am using SQL Server 2008. Thanks!

EDIT: Provided more info. I cannot add indices because it is a Stage table. Also, removing the DISTINCTs; I totally forgot about that.

Phil: How do I consolidate this into a single query?

Answer 1

There are alternatives to @Remus Rusanu's CROSS APPLY suggestion. Even though I myself incline to using CROSS APPLY for your kind of problem, I think it may be worth knowing about other ways of achieving the same result without introducing multiple scans of the same table.

1. UNPIVOT:

   WITH selectedcolumns AS (
     SELECT foo, bar, baz, qux FROM T1
   )
   SELECT DISTINCT v
   FROM selectedcolumns
   UNPIVOT (
     v FOR colname IN (foo, bar, baz, qux)
   ) u
   ;
   

2. CROSS JOIN to an inline view (essentially a pre-UNPIVOT method of unpivoting):

   SELECT DISTINCT
     v = CASE x.i
       WHEN 1 THEN foo
       WHEN 2 THEN bar
       WHEN 3 THEN baz
       WHEN 4 THEN qux
     END
   FROM T1
   CROSS JOIN (
     VALUES (1), (2), (3), (4)
     -- or SELECT 1 UNION ALL SELECT 2 UNION ALL ... in 2005 and older versions
   ) x (i)
   ;
   

The latter seems least attractive to me of the three (including CROSS APPLY) methods, but I still think it may be useful to know about it as a pattern to recognise in older scripts, so that you can transform it to a probably more efficient CROSS APPLY or, where more suitable, UNPIVOT.

Answer 2

You can certainly project foo, bar, baz and qux in a single scan (SELECT foo, bar, baz, qux FROM T1). A CROSS APPLY TVF can transform each row (4 columns) into 4 rows (1 column) and then apply DISTINCT on top. If the original plan had 4 scans of T1 which would dominate the cost, this may be better.