Re: UNION ALL

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 17:46:45 -0600

First of all, I am assuming that you are talking about in the where clause.
If the cases are in the select clause there is no comparison, the single
select will operate faster.

Second, when you say CASE statements, it would depend on what you mean.
Would they be very complex expressions, or just one boolean expression that
might better be represented by mulitple expressions connected by an OR?

It all depends on how optimizable the queries are that you are unioning. If
they use indexes, and the single statement does not, then the union will be
faster (based on table size of course) If any of the unioned statement fail
to optimize to use an index, then the single select will likely be the
victor! 

-- 
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"JMNUSS" <anonymous@discussions.microsoft.com> wrote in message
news:660601c40163$4d639b20$a001280a@phx.gbl...
> A coleague of mine and I are in disagreement over what
> runs faster, a query that has multiple CASE statements or
> a UNION ALL.  I believe that multiple CASE statements will
> parse faster because you are only using one main query as
> opposed to running two queries and UNION'ing them
> together.  Which approach is better and it there any
> documentation that can help lead either of us in the right
> direction?
>
> TIA, Jordan.

Relevant Pages

  • Re: Suggestions
    ... In code you would loop though the ItemsSelected, ... where clause with each iteration. ... > clause query. ... I have multiple types of SERVICE_PROVIDED and want the ...
    (microsoft.public.access.queries)
  • Re: the "having" clause
    ... Start in the FROM clause and build a working table from all of the ... rest of the containing query. ... e) Go to the SELECT clause and construct the expressions in the list. ... As you can see, things happen "all at once" in SQL, not from left to ...
    (microsoft.public.sqlserver.programming)
  • Re: GROUP BY and performance
    ... use distinct while acknowledging that the query could return unexpected ... If the FROM clause had more ... e) Go to the SELECT clause and construct the expressions in the list. ... the innermost queries can reference columns and tables in the ...
    (microsoft.public.sqlserver.programming)
  • Re: calculating SUM
    ... Here is how a SELECT works in SQL ... ... > a) Start in the FROM clause and build a working table from all of the ... > rest of the containing query. ... > e) Go to the SELECT clause and construct the expressions in the list. ...
    (microsoft.public.sqlserver.programming)
  • Re: error while executing this query
    ... So You can only use aggregate functions there. ... The filter conditions in your query can be achieved using the where clause. ... e) Go to the SELECT clause and construct the expressions in the list. ...
    (microsoft.public.sqlserver.programming)