Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Union Vs Union All

    I have a stored procedure with a CTE that has about 40 unions as follows.

    Code:
     
    SELECT t1.pd_name
          ,t1.pd_type
          ,count(*) as total_1
          ,0 as total_2
          ,0 as total_3
          ,... other totals etc
    FROM tbl_products t1
    GROUP BY t1.pd_name
            ,t1.pd_type 
    UNION
    SELECT t1.pd_name
          ,t1.pd_type
          ,0 as total_1
          ,count(*) as total_2
          ,0 as total_3
          ,... other totals etc
    FROM tbl_products t1
    INNER JOIN tbl_orders t2 on t1.pd_id = t2.pd_id
    WHERE t2.order_dt between @FromDate and @ToDate
    GROUP BY t1.pd_name
            ,t1.pd_type 
    UNION
    SELECT t1.pd_name
          ,t1.pd_type
          ,0 as total_1
          ,0 as total_2
          ,count(*) as total_3
          ,... other totals etc
    FROM tbl_products t1
    INNER JOIN tbl_orders t2 on t1.pd_id = t2.pd_id
    WHERE t1.pd_type = 'A Type'
    AND t2.order_dt between @FromDate and @ToDate
    GROUP BY t1.pd_name
            ,t1.pd_type 
    UNION ...

    Should I be using 'union all' with this sort of query? When I execute this query using union, the first time it executes it takes around 2 mins. Subsequent re-run of the query by changing the parameter dates takes around 26 secs

    Using union all makes no difference in execution time?

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Definitely union all, as there are no duplicate rows. Union all eliminates unneeded sorting.

    Edit:
    I don't know your business requirement for this query, but I have a strange feeling that it could be solved in a better way, potentially eliminating even union all. The difference you experience between first run and second run of the query, is most likely caused by physical reads. But, you may still have tons of logical reads since you read from the same table(s) 40 times. If you describe what the intentions of the code are, we may be able to guide you towards a better solution.
    Last edited by roac; 08-22-11 at 07:07.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •