473,289 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

HAVING and WHERE clauses: Performance considerations in SQL

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
2 10377
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

47
by: Andrey Tatarinov | last post by:
Hi. It would be great to be able to reverse usage/definition parts in haskell-way with "where" keyword. Since Python 3 would miss lambda, that would be extremly useful for creating readable...
133
by: Gaurav | last post by:
http://www.sys-con.com/story/print.cfm?storyid=45250 Any comments? Thanks Gaurav
7
by: Rich | last post by:
CREATE TABLE Sales1 ( varchar (10) CustID, varchar (10) TransID, datetime SaleDate, money S1, money S2, money S3, money S4, numeric V1
14
by: Darin | last post by:
I have a table that I want to delete specific records from based on data in other tables. I'm more familiar with Access '97, but am now using 2003, but the database is in 2000 format. In '97, I...
18
by: Rune B | last post by:
Hi Group I was considering using a Generic Dictionary<> as a value container inside my business objects, for the reason of keeping track of fields changed or added and so on. - But how...
4
by: =?Utf-8?B?V2lsc29uIEMuSy4gTmc=?= | last post by:
Hi Experts, I am doing a prototype of providing data access (read, write & search) through Web Service. We observed that the data storing in SQL Server 2005, the memory size is always within...
8
by: Henri.Chinasque | last post by:
Hi all, I am wondering if there are any quick/efficient ways to look at a piece of c++ code and determine if it would run faster if it was compiled to native code. I ask this because all of my...
12
by: =?ISO-8859-1?Q?Ren=E9?= | last post by:
Hi, is there a rule of thumb what is better/faster/more performant in SQL Server 2005? a) SELECT * FROM A INNER JOIN B ON B.ID = A.ID AND B.Cond1 = 1 AND B.Cond2 = 2 b) SELECT * FROM A INNER...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.