SQL Server Performance

Optimize SELECT UNION

Discussion in 'SQL Server 2005 General DBA Questions' started by CanadaDBA, Nov 4, 2010.

  1. CanadaDBA New Member

    There is a stored procedure in one of DBs (and the DB size is about 70 GB) which is as below.

    SELECT ...
    FROM {Inner Join of 10 tables and views}
    GROUP BY ...
    UNION
    SELECT ...
    FROM {Inner joins of 10 tables and views}
    GROUP BY ...
    UNION
    SELECT ...
    FROM {Inner joins of 10 tables and views}
    GROUP BY ...
    UNION
    SELECT ...
    FROM {Inner joins of 10 tables and views}
    GROUP BY ...
    I don't know the DB yet and have recently inherited it. Is there a better way to code the SP in order to optimize and increase its performance?
    thanks,
  2. FrankKalis Moderator

    UNION removes duplicates. Maybe it could be changed to a UNION ALL? But sine you don't know that db yet, I would be very careful doing anything yet without seeing more of the big picture.
  3. Adriaan New Member

    If it's basically the same query statement each time, but with a different WHERE clause, then I'd check if procedure parameters are also part of the criteria, like this:
    CREATE PROC MyProc (@p1 BIT, @p2 BIT)
    AS
    SELECT col1, col2 FROM MyTable WHERE @p1 = 1 AND col1 = 'x'
    UNION
    SELECT col1, col2 FROM MyTable WHERE @p2 = 1 AND col1 = 'y'
    For this kind of conditional filtering a better option might be to use dynamic SQL in your procedure, by calling sp_ExecuteSQL with proper parameters.
  4. Madhivanan Moderator

    Another method would be use optional parameters
    SELECT col1, col2 FROM MyTable
    WHERE
    (@p1 = 1 AND col1 = 'x')
    OR
    (@p2 = 1 AND col1 = 'y')
    OR
    .
    .
    .

Share This Page