Re: Filter in the Join vs Filter in the Where
- From: "Tom Cooper" <tomcooper@xxxxxxxxxxx>
- Date: Tue, 27 Oct 2009 23:33:37 -0400
I like your method, I don't like Bob's method. But that's a matter of style, not efficiency. Your method is by far the most commonly used.
But they are both equally efficient. If you examine the execution plans, you will see that they are identical. And, yes, the optimizer is allowed to and does change the order of the operations to the most efficient method it can find that returns the correct result. And since your query and Bob's query both return the same result, it is not suprising that the optimizer generates the same plan. And since the query plan is the same, they are equally efficient.
Tom
"Mike H" <mike8675309@xxxxxxxxx> wrote in message news:cc6e255a-e6a1-4b6f-8411-ca68c6c58ffa@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
To start off, this is with SQL Server 2005 data.
The next question comes from running into someone that likes to write
their
code a different way than I do. I've long ago established how I do
it, and I'm sure I had a good reason at the time, but I can't recall.
And this other person, I'll call Bob, also likes his way, and feels
it's better.
The actual data this question is based on is tens of millions of rows
in 3nf normalized database that stores data. Not quite OLTP, but not
quite datawarehouse. Typically people are working with 4 or 5 sets of
data from this database at any one time, with each set of data having
hundreds of thousands of rows. We can assume (for this discussion)
that appropriate indexes are in place, with sufficient memory, cpu,
and i/o available. Of course we simplify here for an example:
CREATE TABLE #One (CarBrand NVARCHAR(40), Employees INT)
INSERT INTO #one VALUES ('Ford',625)
INSERT INTO #one VALUES ('Chevrolet',865)
INSERT INTO #one VALUES ('Porche',700)
INSERT INTO #one VALUES ('Citroën',250)
CREATE TABLE #two (CarBrand NVARCHAR(40), PopularityRank SMALLINT)
INSERT INTO #two VALUES ('Ford',1)
INSERT INTO #two VALUES ('Chevrolet',3)
INSERT INTO #two VALUES ('Porche',2)
INSERT INTO #two VALUES ('Citroën',4)
CREATE INDEX #myindex ON #one(Carbrand)
CREATE INDEX #myindex3 on #one(Employees)
CREATE INDEX #myindex2 ON #two(Carbrand)
The conflict is that creating a query that joins multiple tables, and
requires filtering to be applied to various columns from these various
tables, I will more often than not use the Join On to join the common
fields, and place any filters in the Where clause. For example I
might write a query like this:
SELECT a.carbrand, a.Employees, b.Rank
FROM #One a
JOIN #Two b
ON a.CarBrand = b.carbrand
WHERE a.Employees > 500
Bob will write a query that looks like this:
SELECT a.carbrand, a.Employees, b.Rank
FROM (select 'x' as x) as x
JOIN #One a
on a.Employees > 500
JOIN #Two b
ON a.CarBrand = b.carbrand
Bob's theory being that since the order of operations for sql
databases is:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
sticking any filtering conditions into the FROM clause is going to
lead to higher performance through less data needing to be processed
as it is filtered before the where needs to deal with it. I've found
others preaching this as well:http://www.bennadel.com/blog/70-SQL-
Query-Order-of-Operations.htm
In general, that's never really something I've worried about. Should
I be? Won't SQL Server, Oracle, DB2 optimize your query and run it
however it feels is most optimal, regardless of where you put
filtering conditions? Or is there some incremental benefit that might
appear, so you should just try and avoid the where?
It was news to me and I hadn't found much discussion online so I
thought I'd see what folks around here say.
.
- References:
- Filter in the Join vs Filter in the Where
- From: Mike H
- Filter in the Join vs Filter in the Where
- Prev by Date: Filter in the Join vs Filter in the Where
- Next by Date: Data Cleansing
- Previous by thread: Filter in the Join vs Filter in the Where
- Next by thread: Re: Filter in the Join vs Filter in the Where
- Index(es):
Relevant Pages
|
