Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Consolidate into a single query if they're all coming from the same table? Hard to advise without query plans etc –  Phil Jul 30 '12 at 21:00
2  
Also your DISTINCTs are uneeded - a UNION will filter out duplicates from the final result set. –  JNK Jul 30 '12 at 21:00
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Andriy, the two examples return different rowcounts. The UNPIVOT example returns one less row than the CROSS JOIN example. What could be the possible reasons? EDIT: It is because CROSS JOIN has a NULL record, whereas the UNPIVOT example does not. =) –  Anonymous Maximus Jul 31 '12 at 16:21
1  
Ah, indeed, I forgot about NULLs. Thanks for finding that out and sharing the results! By the way, in view of your find, do you think CROSS APPLY would be more suitable than either of the methods mentioned in my answer? I mean, it would return the same results as the CROSS JOIN method but it would likely be more efficient (even if just slightly). (In case you are still struggling with the correct syntax: SELECT DISTINCT v FROM T1 CROSS APPLY (VALUES (foo), (bar), (baz), (qux)) x (v);.) –  Andriy M Aug 1 '12 at 4:49
add comment

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.

share|improve this answer
1  
+1, but did you really mean a TVF? A simple CROSS APPLY (SELECT foo UNION ALL SELECT bar UNION ALL ...) would seem to me enough. –  Andriy M Jul 31 '12 at 8:08
    
@AndriyM: I reckon I haven't thought about your proposed improvement. Sure, it would be better than a TVF. In my defense I was writing the response from my Mac and I didn't bother to fire up a VM to test it... if being lazy is an acceptable defense ;) –  Remus Rusanu Jul 31 '12 at 8:34
    
If nothing else, it's a kind of defence that suits me perfectly at the moment. :) I only wanted to make sure I got the general idea (which, incidentally, I find brilliant) right. –  Andriy M Jul 31 '12 at 9:35
    
Remus, Andriy: Thank you both for your comments. I am a SQL newbie and have never used CROSS APPLY before. Reading the Online Help in SSMS, I don't yet fully understand what it does. Can you please provide the full syntax? –  Anonymous Maximus Jul 31 '12 at 14:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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