Wednesday, August 06, 2008

I read the article SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN today and was interested in the relative speed of Inner join vs Intersect for simple matcjhes and so thought I'd make another use of my timing code to see the difference.

I produced a couple of very simple tables:
Summary ([ID] INT, [Name] NVARCHAR(10))
Detail ([ID] INT, [Age] INT)
both with primary keys on ID and populated them both as follows:

INSERT INTO Summary
SELECT 1, 'Jane' UNION ALL
SELECT 2, 'Richard'

INSERT INTO Detail
SELECT 1, 36 UNION ALL
SELECT 2,40

I chose to do a very simple task - retrieve the ID for an entry which is in both Summary and Detail - note: this assumes that the ID is a foreign key constraint and the IDs are relating to the same item.

Using my rudimentary timing code, I think that intersect is more performant than the inner join - based on running the same query 100000 times

SELECT Summary.ID
FROM Summary
INNER JOIN Detail
ON Summary.ID = Detail.ID

and

SELECT ID FROM Summary
INTERSECT
SELECT ID FROM Detail

which has the following results:
11746

Description	TimeInMS
Join
Intersect	10203

I ran them both together and took a look at the actual execution plans and it does indicate that the intersect takes a little less effort - which must be down to the Seek instead of Scan in the Join'd table detail


So, another option for me to remember to consider when determining the best way to get at some data

Labels: SQL, SQLServer2005

// posted by Jane @ 6:28 PM   

Comments:

Post a Comment