By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

HAVING and WHERE clauses: Performance considerations in SQL

P: n/a
Hi,

Performance-wise, is there a difference between these 2 clauses?
I assume a large database...

1) With the WHERE clause:

Select FLD1, FLD2, FLD3
From myFile
Where FLD1 = 1 And FLD2 = 1
Group By FLD1, FLD2, FLD3

2) With the HAVING clause:

Select FLD1, FLD2, FLD3
From myFile
Group By FLD1, FLD2, FLD3
Having FLD1 = 1 And FLD2 = 1

thx

Jack
Oct 8 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
First of all, I hope that you know that fields are not anything like
columns and tables are not anything like files. Please tell me that
this was just an unfortunate bunch of data element names and not how
you think of data.

1) With the WHERE clause:

SELECT fld1, fld2, fld3
FROM MyFile
WHERE fld1 = 1
AND fld2 = 1
GROUP BY fld1, fld2, fld3;

The query is to act as if the WHERE clause is done first on the table
named (ugh!) MyFile. Then the resulting work set is grouped --
subsets of rows are reduced to a single row.

That is the model, but the optimizer is free to do this any way it
wants to do it. But since you violated the basic SQL Newsgroup
netiquette, and did not show us any DDL, we have no idea if there is
UNIQUE constraint on any of the columns, what the indexing is, etc.

But a smart optimizer will see the GROUP BY and that fld1 and fld2 are
constants, so we need "buckets" for the fld3 values. If fld3 is
indexed, then we know those values! That means looking at just the
index. Should be fast!

2) With the HAVING clause:

SELECT fld1, fld2, fld3
FROM MyFile
GROUP BY fld1, fld2, fld3
HAVING fld1 = 1
AND fld2 = 1;

This query is to act as if we make all possible groupings of (fld1,
fld2, fld3) in a working table, then applied the having clause.

That is the model, but the optimizer is free to do this any way it
wants to do it. But since you violated the basic SQL Newsgroup
netiquette, and did not show us any DDL, we have no idea if there is
UNIQUE constraint on any of the columns, what the indexing is, etc.

But a smart optimizer will see constants in the HAVING and convert
them into a WHERE clause. The two queries should work out about the
same.

Oct 8 '08 #2

P: n/a
"Jack" <ja***********@gmail.comwrote in message
news:3b**********************************@i20g2000 prf.googlegroups.com...
Hi,

Performance-wise, is there a difference between these 2 clauses?
I assume a large database...

1) With the WHERE clause:

Select FLD1, FLD2, FLD3
From myFile
Where FLD1 = 1 And FLD2 = 1
Group By FLD1, FLD2, FLD3

2) With the HAVING clause:

Select FLD1, FLD2, FLD3
From myFile
Group By FLD1, FLD2, FLD3
Having FLD1 = 1 And FLD2 = 1

thx

Jack
Generally speaking, the sooner you filter the rows, the less work DB2 will
need to do. That means filtering out the rows in where clause will generally
be faster, or at least not slower.
Oct 9 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.