having vs where

hi,
In terms of speed, which one is faster? and what is the difference?  thx
mcrmgAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
sigmaconCommented:
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
 
sigmaconCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
mcrmgAuthor Commented:
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
 
mcrmgAuthor Commented:
actually, I take that back......the speed is the same. But I thought it would be faster if I group them...
0
 
sigmaconCommented:
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
 
mcrmgAuthor Commented:
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
 
sigmaconCommented:
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
 
mcrmgAuthor Commented:
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
 
sigmaconCommented:
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
 
mcrmgAuthor Commented:
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
 
sigmaconConnect With a Mentor Commented:
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
 
mcrmgAuthor Commented:
>>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
 
sigmaconCommented:
Well, post a bunch of actual SQL queries, including DDL and some sample data and give it a shot.
0
 
arbertConnect With a Mentor Commented:
"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
 
sigmaconCommented:
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
 
arbertCommented:
"(it doesn't HAVE to have one) "

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

0
 
sigmaconCommented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
All Courses

From novice to tech pro — start learning today.