• The optimizer often does a better job with UNION than OR, but that applies to conditions like:

    thiscol = @value OR thatcol = @value

    Which does not seem to be the case here. And when it does, you should not duplicate the full query, but put the condition in a derived table which you use in the rest of the query:

    JOIN (SELECT ...

    FROM sometable

    WHERE thiscol = @value

    UNION

    SELECT sometable

    WHERE thatcol = @value) AS u ON ....

    But as Chris alludes, this is not your scenario here. You query includes these conditions:

    (isnull(10172,0)=0 ) and

    (isnull('CRITICAL','ALL')='ALL'

    Which is kind of nonsense. I suspect that the real queries has variables/parameters. Restore the original query and add OPTION (RECOMPILE) at the end of the query. A query with conditions like these benefits from a recompile everytime, since depending on the parameter value, different plans are called for.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]