where versus inner join - 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)

where versus inner join

Author

Topic

marginerazvan
Starting Member

14 Posts

Posted - 2009-01-30 : 03:44:32

I have a table with 3000 records. (ID is the primary column)
I want to query something in this table. Usually my selection is 10 positions.

I have two scenarios
1. SELECT ... FROM Table WHERE ID IN (selection_list)
OR
2.
- save selections into a temporary table. This table can be used by many users, so it has a USER_ID column.
INSERT INTO Temp_Table (ID, USER_ID) SELECT selection_list .....
- query table
SELECT ... FROM Table INNER JOIN Temp_table ON Table.ID = Temp_table.ID WHERE Temp_table.USER_ID = ...

Question.
Which solution is quicker?
Using WHERE clause
OR
Using INNER JOIN clause, but this solution requires that first I have to save my selection into a temporary table

Thank your for your answer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 03:47:36

There is no absolute truth in "which is quicker".
It depends on few factors, mainly how many records there are in the IN list.
Also, a join can produce duplicate records if there are duplicates in the JOINed table.

So, we are not able to tell you which is best before we get more background information.

E 12°55'05.63"
N 56°04'39.26"

marginerazvan
Starting Member

14 Posts

Posted - 2009-01-30 : 04:32:41

In my case, in Temp_table there will not be any duplicates for a user. So the Join operation will not produce duplicate records.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 04:53:13

[code]CREATE TABLE #Superset
(
ID INT PRIMARY KEY CLUSTERED,
Rnd INT
)

INSERT #Superset
(
ID,
Rnd
)
SELECT v1.Number + 100 * v2.Number AS ID,
ABS(CHECKSUM(NEWID())) AS Rnd
FROM master..spt_values AS v1
INNER JOIN master..spt_values AS v2 ON v2.Type = 'P'
AND v2.Number BETWEEN 0 AND 29
WHERE v1.Type = 'P'
AND v1.Number BETWEEN 1 AND 100
ORDER BY v1.Number + 100 * v2.Number

CREATE TABLE #Subset
(
UserID INT,
ID INT,
PRIMARY KEY CLUSTERED
(
UserID,
ID
)
)

-- Run this statement until no primary key error occur
INSERT #Subset
(
UserID,
ID
)
SELECT v1.Number AS UserID,
ABS(CHECKSUM(NEWID())) % 3000 AS ID
FROM master..spt_values AS v1
INNER JOIN master..spt_values AS v2 ON v2.Type = 'P'
AND v2.Number BETWEEN 0 AND 9
WHERE v1.Type = 'P'
AND v1.Number BETWEEN 1 AND 100
ORDER BY v1.Number,
2

-- Join method
SELECT a.ID,
a.Rnd
FROM #Superset AS a
INNER JOIN #Subset AS b ON b.ID = a.ID
WHERE b.UserID = 55

-- IN method
SELECT a.ID,
a.Rnd
FROM #Superset AS a
WHERE a.ID IN (SELECT b.ID FROM #Subset AS b WHERE b.UserID = 55 AND b.ID = a.ID)

DROP TABLE #Superset,
#Subset[/code]Execution plans are the following[code]INNER JOIN method
|--Nested Loops(Inner Join, OUTER REFERENCES:([b].[ID]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(55)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), SEEK:([a].[ID]=[tempdb].[dbo].[#Subset].[ID] as [b].[ID]) ORDERED FORWARD)

IN method
|--Merge Join(Right Semi Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID] AND [tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(55)) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), ORDERED FORWARD)[/code]SQL Profiler results are the following[code]INNER JOIN method
Reads 22
Parse & compile 1 ms
Execution 0 ms
IN method

Reads 11
Parse & compile 9 ms
Execution 1 ms[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 04:59:07

I rerun the code again with 10,000 sample records in #Superset, and for 200 users with 10 random values each in #Subset.
Now I got these results

INNER JOIN method

Parse & compile   1 ms
Execution         0 ms

IN method

Parse & compile   7 ms
Execution       146 ms

So the IN method will quickly degrade in performance.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 05:07:17

Here are the execution plans for comparison:

SQL Server 2000

  |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset].[PK__#Subset__63484916] AS [b]), SEEK:([b].[UserID]=77) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Superset].[PK__#Superset__5F77B832] AS [a]), SEEK:([a].[ID]=[b].[ID]) ORDERED FORWARD)

  |--Merge Join(Right Semi Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([b].[ID]=[a].[ID] AND [b].[ID]=[a].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset].[PK__#Subset__63484916] AS [b]), SEEK:([b].[UserID]=77) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Superset].[PK__#Superset__5F77B832] AS [a]), ORDERED FORWARD)


SQL Server 2005

  |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(77)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), SEEK:([a].[ID]=[tempdb].[dbo].[#Subset].[ID] as [b].[ID]) ORDERED FORWARD)

  |--Merge Join(Right Semi Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID] AND [tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(77)) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), ORDERED FORWARD)


SQL Server 2008

  |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(55)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), SEEK:([a].[ID]=[tempdb].[dbo].[#Subset].[ID] as [b].[ID]) ORDERED FORWARD)

  |--Merge Join(Right Semi Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID] AND [tempdb].[dbo].[#Subset].[ID] as [b].[ID]=[tempdb].[dbo].[#Superset].[ID] as [a].[ID]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Subset] AS [b]), SEEK:([b].[UserID]=(55)) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Superset] AS [a]), ORDERED FORWARD)

E 12°55'05.63"
N 56°04'39.26"

marginerazvan
Starting Member

14 Posts

Posted - 2009-01-30 : 07:39:19

Dear Peso,
I thank you for your interest. It seems that you have plenty of time available ;-), but for sure you are a gifted SQL programmer.
I will consider what you wrote.
Thank you once again,
Razvan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources