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
SELECT bar from T1
SELECT baz from T1
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?

  • 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

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.


    WITH selectedcolumns AS (
      SELECT foo, bar, baz, qux FROM T1
    FROM selectedcolumns
      v FOR colname IN (foo, bar, baz, qux)
    ) u
  2. CROSS JOIN to an inline view (essentially a pre-UNPIVOT method of unpivoting):

      v = CASE x.i
        WHEN 1 THEN foo
        WHEN 2 THEN bar
        WHEN 3 THEN baz
        WHEN 4 THEN qux
    FROM T1
      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.

| 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

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.

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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