July 2, 2009 at 5:32 am
Is there any difference in performance between:
SELECT A.*
FROM A INNER JOIN B
ON A.ID = B.ID
WHERE (B.Sex = 'M')
and
SELECT * FROM A WHERE
A.ID in (select ID from B where B.Sex = 'M')
July 2, 2009 at 6:36 am
Before you think about performance, those are logically different queries anyway.
If you have 2 rows in B for the same ID, the first query will return 2 rows, but the second query will only return 1 row.
Because of that, you will get different plans for the 2 queries.
July 2, 2009 at 7:27 am
You are right. Probably i should put an DISTINCT at the first select.
July 2, 2009 at 8:39 am
I would say yes, because in the first query you are only reading one table, but in the second you are reading 2 tables. On small tables it may not be noticeable, but I would be on larger ones it would.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 2, 2009 at 8:50 am
Just to provide an example check out this code in AdventureWorks:
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
SalesOrderDetailID
FROM
Sales.SalesOrderDetail AS SOD
WHERE
SalesOrderID = 43659
SELECT
SalesOrderDetailID
FROM
Sales.SalesOrderDetail AS SOD
WHERE
SalesOrderID = (SELECT SalesOrderId FROM Sales.SalesOrderHeader AS SOH WHERE SalesOrderId = 43659)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
On my system there is no noticeable difference in return time, but the first query has 1 scan of SalesOrderDetail requiring 3 reads while the second has an additional 3 reads of SalesOrderHeader.
In AdventureWorks SalesOrderID is the Clustered Index on SalesOrderHeader and is the left most column in the Clustered Index on SalesOrderDetail
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 2, 2009 at 11:45 am
I would say yes, because in the first query you are only reading one table, but in the second you are reading 2 tables.
Err, Jack, isn't
SELECT A.*
FROM A INNER JOIN B
ON A.ID = B.ID
WHERE (B.Sex = 'M') 2 tables?
July 2, 2009 at 11:59 am
Ian Scarlett (7/2/2009)
I would say yes, because in the first query you are only reading one table, but in the second you are reading 2 tables.
Err, Jack, isn't
SELECT A.*FROM A INNER JOIN B
ON A.ID = B.ID
WHERE (B.Sex = 'M') 2 tables?
Oops my bad, that what I get for not reading it well. I missed the JOIN. Of course, I wouldn't have missed it if it was in a Code block like you put it in.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply