Solved

having vs where

Posted on 2004-10-27
438 Views
Last Modified: 2006-11-17
hi,
In terms of speed, which one is faster? and what is the difference?  thx
0
Question by:mcrmg
    19 Comments
     
    LVL 8

    Expert Comment

    by:sigmacon
    Everything else being equal, in most cases, WHERE is faster, because HAVING also requires a GROUP BY. But in real-life the answer is: depends. Run the query of concern with both versions and look at the execution plan. Make sure you got proper indexes.
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    I should word this differently for clarification: You cannot have a HAVING clause without a GROUP BY clause. Most often, HAVING clauses have aggregate statements in them - that's why one would be using GROUP BY in the first place. So often, HAVING cannot use existing indexes, which the WHERE clause can - provided those indexes exist.

    These are really 2 different clauses with two different functions - even if the have the same purpose for the end result, which is to restrict or filter it.
    0
     

    Author Comment

    by:mcrmg
    okay, thx

    I use view in my sp, in view, at first, I use WHERE, then I was thiking if I group them first, it might be faster (id this true?????)

    Then when I group them with having or where, it runs slower than before w/o grouping.

    thx
    0
     

    Author Comment

    by:mcrmg
    actually, I take that back......the speed is the same. But I thought it would be faster if I group them...
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    If you don't use aggregates or calculations in your having clause - so that it essentially looks like a where clause - they should perform about they same, because in this case they do the same: Restrict which rows are selected from a table by means of index seek, index scan or table scan.
    0
     

    Author Comment

    by:mcrmg
    1. Can grouping increase performace?

    2.>> Restrict which rows are selected from a table by means of index seek, index scan or table scan.
    I have no idea what this is??  l0l

    thx
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    1.) Compared to an equivalent WHERE clause, USUALLY not.
    2.) There is three ways for the server to find a row in a table:

        a) The column(s) mentioned in the WHERE or HAVING clause have an index on them
            a.1) If the statement is specific enough, the server can SEEK out a row in the index directly - fasted way of doing it
            a.2) If the statement only helps the server to restrict row within a range, it has to SCAN that range for the rows that match - for small result sets, this is still ok
        b) There is index, or the estimated number of rows returned is high, then the server SCANs the ENTIRE TABLE to find matching rows, which for large tables can be slow.

    The magic is in looking at the execution plan in query analyzer!
    0
     

    Author Comment

    by:mcrmg
    I think it will be very helpful...
    I did create index in tables.  But, how do you create index in view?  thx

    Also, how do you use execution plan

    thx
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    Assuming you are using Query Analyzer to run your queries, Menu Query > Show Execution Plan (Ctrl + K) does the job. Indexed views are restricted to SQL Server Enterprise for a production environment. So even though you can set them up in the Developer Edition, if - in production - you use the Standard Edition, you cannot use indexed views there. There are other restrictions for indexed views, see books online for details.

    Indexes on views are created just like normal indexed, using the create index statement
    0
     

    Author Comment

    by:mcrmg
    I will take a look at Execution Plan ........thx

    Please bear with me lol........
    I still do not know how to create index in view

    in view, I have something like this:

    Select firstname, lastname
    from names
    where state='abc'

    Can you use this as an example?  thx
    0
     
    LVL 8

    Assisted Solution

    by:sigmacon
    Are you sure that this is what you want - an indexed view?

    You will need to name the view in your example above. Indexing a view is not trivial:

    A) ALL dependent objects must have been created with ANSI_NULL and QUOTED_IDENTIFIER ON and WITH SCHEMABINDING option
    B) There are many restrictrion to the things that ARE NOT ALLOWED in indexed views, YOU MUST READ Books Online to understand them
    C) ALL dependent objects needed to have the owner / catalog qualified when referenced (required for SHEMABINDING)
    D) The first index on a view needs to be unique clustered, in your example above it is highly likely that the rows are not unique (dublicate names)

    Assuming they are currently and let's say you named your view NamesInABC, and you want to index firstname and lastname:

    create unique clustered index on dbo.NamesInABC ( firstname, lastname )

    If this statement fails, one of the MANY requirements is not fullfilled and you will have to investigate another way of getting good performance.

    Before you try any of this above, what is it that you actually are trying to accomplish?
    0
     

    Author Comment

    by:mcrmg
    >>Before you try any of this above, what is it that you actually are trying to accomplish?   lol

    okay, I have a report using ASP, that contains more than several sps, in those sps, I use views.  and have  a sp to combine those sps into a temp table,
    In views, I have tried to filter out those parameters as possible as I could. The spped did improve.

    But, I am still to find if there are any way to get it faster......thx
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    Well, post a bunch of actual SQL queries, including DDL and some sample data and give it a shot.
    0
     
    LVL 34

    Assisted Solution

    by:arbert
    "I should word this differently for clarification: You cannot have a HAVING clause without a GROUP BY clause. Most often, HAVING clauses have aggregate statements in them - that's why one would be using GROUP BY in the first place. So often, HAVING cannot use existing indexes, which the WHERE clause can - provided those indexes exist.

    These are really 2 different clauses with two different functions - even if the have the same purpose for the end result, which is to restrict or filter it."


    And to really clarify, you can get totally different results using one instead of the other.  I know it was mentioned to use having you must have an aggregate, but that also means that records with having are filtered AFTER the aggregation where as the records with a where clause are filtered BEFORE the aggregation--makes a big difference some times...
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    mcrmg, I hope this is not confusing ;-) Here is what Book Online says:

    --------------------------------

    HAVING
    Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. It is usually used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. For more information, see SELECT.

    --------------------------------

    Be clear about what arbert said: IF it has an aggregate (it doesn't HAVE to have one), then it is applied AFTER the aggregation, and - as I pointed out - no index is being used (which makes it slow for large result sets), but the query plan actually shows 'Filter'.
    0
     
    LVL 34

    Expert Comment

    by:arbert
    "(it doesn't HAVE to have one) "

    I requoted you and your above statement saying an aggregate was required...

    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    I think I misunderstand the term aggregate. Sorry, English is my second language. With arberts continuous correction, I now understand where my termininology is messed up. I always thought of aggregates as the aggregate functions SUM, AVG, MAX, etc. But the GROUP BY clause itself is an aggregate. Thanks for being so persistent, arbert.

    BTW, I did not know that you can have a HAVING w/o a GROUP BY, I just looked it up in BOL because I got confused based on what arbert was pointing out. I have not tried a HAVING statement without GROUP BY, yet though.

    Otherwise, I think my discussion of HAVING vs. WHERE from a performance perspective is still valid. ASFAIK.
    0
     
    LVL 68

    Assisted Solution

    by:ScottPletcher
    If you need to compare column(s) from an individual row, you should use WHERE.  It is much more efficient to eliminate individual rows from consideration as soon as possible.


    Use HAVING only if you have to compare a total or other aggregate (MIN/MAX/SUM/AVG/etc.) value.

    For example, say you want to list all orders that have an orderAmount >= $100:

    SELECT *
    FROM orders
    WHERE orderAmount >= 100

    But, now say you want to list the total of order amounts for each customer but only if the total orders for that customer are >= 5000:

    SELECT customerId, SUM(orderAmount) AS [Total Order Amounts]
    FROM orders
    GROUP BY customerId
    HAVING SUM(orderAmount) >= 5000

    Note that you *cannot* do this comparison using a WHERE: WHERE can only compare to a single row's column, not the total of several rows.
    0
     
    LVL 68

    Accepted Solution

    by:
    As a simple rule:

    Always use WHERE if you can; if you can use WHERE, specifying HAVING instead is *wrong* because it can hurt performance.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    If you are an Active Directory administrator working with AD data in SQL Server, then this article is for you! INTRODUCTION As AD admins or those having to deal with AD data, you probably have had to convert a timestamp or two like last logo…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now