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 2 10386
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.
"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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
by: Gaurav |
last post by:
http://www.sys-con.com/story/print.cfm?storyid=45250
Any comments?
Thanks
Gaurav
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |