Use JOIN or WHERE? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Use JOIN or WHERE?

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-13 : 12:43:31

Consider these 2 SQL Statements...


-- With Top x...
SELECT Vendor,ItemNo,Quantity,(SELECT TOP 1 p.Price
FROM Price p WHERE p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo AND  p.MinQty <= t.Quantity) AS Price FROM TRX t

-- With Inner Join
SELECT Vendor,ItemNo,Quantity,(SELECT MIN(p.Price)
FROM Price LEFT JOIN PRICE p ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo
WHERE p.MinQty <= t.Quantity) AS Price FROM TRX t

Running a Trace, to my surprise I see that the Join actually does more reads than the WHERE. I tried it without indexes (WHERE Reads: 36, JOIN: 42) and with Indexes (WHERE: 23 JOIN: 37).

Does this mean that joining tables with a WHERE will be faster than a JOIN due to the number of reads?

Is it possibly due to the join having 2 conditions, where the second condition (p.ItemNo = t.ItemNo) is processed regardless of the result of the first condition (p.Vendor = t.Vendor)?

KTB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 12:48:40

Even though they return the same results, these 2 statements are not the same. Your third column is different. Try comparing the results with 2 like statements:

SELECT t1.Column1, t2.Column2
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ColumnA = t2.ColumnA

SELECT t1.Column1, g2.Column2
FROM Table1 t1, Table2 t1
WHERE t1.ColumnA = t2.ColumnA

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-13 : 13:16:44

OK...

-- With Top x...
SELECT t.Vendor,t.ItemNo,t.Quantity,p.price
FROM TRX t, PRICE p
WHERE p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo

-- With Inner Join
SELECT t.Vendor,t.ItemNo,t.Quantity,p.price
FROM TRX t
LEFT JOIN PRICE p ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo

JOIN resulted in 36 reads as opposed to 42 reads for the WHERE.

My original reason for testing this was to determine if a MIN() or a TOP 1 was a better solution when grabbing one piece of information from a db. I was testing WHERE vs. JOIN to take that out of the conclusion. What do you think here?...

-- With Top x... (8 Reads)
SELECT TOP 1 p.price
FROM TRX t, PRICE p
WHERE p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo
ORDER BY p.price

SELECT TOP 1 p.price (36 Reads)
FROM TRX t
LEFT JOIN PRICE p ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo
ORDER BY p.price

-- With MIN() (36 Reads)
SELECT MIN(p.price)
FROM TRX t
LEFT JOIN PRICE p ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo

SELECT MIN(p.price) (42 reads)
FROM TRX t, PRICE p
WHERE p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo

-- Straight MIN vs. TOP 1 (Both resulted in 6 reads)
SELECT MIN(p.price) FROM PRICE p
SELECT TOP 1 p.price FROM PRICE p

Why are there 36 reads on the Left Join/MIN and only 8 on the TOP 1/WHERE? Is there a standard for which is better? What about the 42 reads for the MIN/WHERE combo?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 13:59:59

First off, if you are are going to use TOP, then you must use an ORDER BY. You can not guarantee which TOP row you are getting without the ORDER BY. With MIN, you are guaranteed the minimum value for that column.

Between the two versions of TOP x, both have the same query cost. Between the two versions of MIN, the first using a JOIN has a lower query cost. To see this put all of the statements in Query Analyzer and check out the execution plan. Query cost will be displayed as a percentage. I am not sure that it matters about reads. Maybe someone else can you out on that one.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-13 : 14:42:05

Hmmm. I get different results than you...

TOP 1 WHERE - 22.83% Reads 32
TOP 1 JOIN - 28.19% Reads 56
MIN() JOIN - 24.49% Reads 56
MIN() WHERE - 24.49% Reads 56

Here's my testing tables along with SQL Statements
DROP TABLE Price
DROP TABLE TRX

CREATE TABLE Price
(

pIDNo INT IDENTITY,
Vendor INT,
ItemNo INT,
Price INT,
MinQty INT)

CREATE TABLE TRX
(
tIDNo INT IDENTITY,
Vendor INT,
ItemNo INT,
Quantity INT
)

INSERT INTO Price VALUES(1,122, 100, 500)
INSERT INTO Price VALUES(1,122, 200, 200)
INSERT INTO Price VALUES(1,122, 300, 100)
INSERT INTO Price VALUES(1,122, 450, 50)
INSERT INTO Price VALUES(1,122, 600, 1)

INSERT INTO Price VALUES(1,1232, 1200, 200)
INSERT INTO Price VALUES(1,1232, 1300, 100)
INSERT INTO Price VALUES(1,1232, 1450, 50)
INSERT INTO Price VALUES(1,1232, 1600, 1)
INSERT INTO Price VALUES(1,1232, 1450, 50)
INSERT INTO Price VALUES(1,1232, 1600, 1)

INSERT INTO TRX VALUES(1, 122, 200)
INSERT INTO TRX VALUES(1, 122, 300)
INSERT INTO TRX VALUES(1, 122, 400)
INSERT INTO TRX VALUES(1, 122, 1)
INSERT INTO TRX VALUES(1, 122, 500)

-- With Top x...
SELECT TOP 1 p.price
FROM TRX t, PRICE p
WHERE p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo
ORDER BY p.price

SELECT TOP 1 p.price
FROM TRX t
LEFT JOIN PRICE p ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo
ORDER BY p.price

-- With MIN
SELECT MIN(p.price)
FROM TRX t
LEFT JOIN PRICE p ON p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo

SELECT MIN(p.price)
FROM TRX t, PRICE p
WHERE p.Vendor = t.Vendor AND p.ItemNo = t.ItemNo

It seems to me that TOP 1 with WHERE has been beating all of the rest consistently, both in the Execution Plan and Trace Reads. Am I off here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 14:52:26

Yes, I get the same values as you now.

With an index added to price, the first select is even better.

CREATE INDEX IDX_Price ON PRICE(price)

Whether you do the JOIN in the WHERE clause or with the word JOIN, they will have the same execution plan. So it's a preference thing. As far as TOP with an ORDER BY versus MIN, well that's left up to the developer to test the performance.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-13 : 15:16:21

Yep, your CREATE INDEX of course speeds up the first query due to the order by on an indexed column.
So, I was wondering if an index on the child price table would have an effect. I did this after creating the tables...

CREATE INDEX IDX_Vendor ON PRICE(Vendor)
CREATE INDEX IDX_ItemNo ON PRICE(ItemNo)

And reran my queries. Here's the results now...
TOP 1 WHERE - 28.97% Reads 100
TOP 1 JOIN - 26.34% Reads 100
MIN() JOIN - 21.03% Reads 100
MIN() WHERE - 23.67% Reads 100

Interesting how the reads increase, and how now the TOP 1 WHERE is the least efficient as far as cost of the entire batch goes. Just what is this a percentage of? Processing Time?

I guess what I have learned today is that there is no straight answer to my question. It all depends on how the data is stored and created.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 15:20:32

Percentage of the batch of queries. You've got 4 queries that will add up to 100%. If you only put one query in the window, the cost will always be 100%.

Personally, I would go with the MIN/JOIN query.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-13 : 15:28:44

Yeah that was my first inclination also. I guess I just wasted 4 hours playing around with this. Then again, I guess it's not wasted since I did learn something. Probably "don't fix it if it a'int broke" comes to mind.

Anywway, thanks Tara.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-13 : 20:31:14

INNER JOINs and WHERE are comparable -- but not OUTER JOINs !!!! they are very different, almost apples-to-oranges. Do not attempt to compare execution plans, they return different results and often require different methods of processing.

- Jeff

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-14 : 09:30:46

So which is better performance wise?

1. LEFT OUTER JOIN ChildTable ON ChildTable.KeyField = ParentTable.KeyField
or
2. WHERE ChildTable.KeyField = ParentTable.KeyField

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-14 : 10:11:26

The are COMPLETELY different. (well, maybe not COMPLETEly but definitely different enough to not compare!)

the first returns all rows from ParentTable, and if there happens to be matches in ChildTable, return mathcing rows as well.

The second returns ONLY matches from BOTH tables.

They return different results and should never be interchanged. Like I said, you can exchange a WHERE with an INNER JOIN because they are essentially the same, but not OUTER joins.

Does this help/make sense?

- Jeff

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-14 : 10:50:25

WheeeeeeeeeeeeeBzzzzzzz. That's the sound of the drill in my head.

Understood.

Thanks Jeff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources