SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 where versus inner join		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marginerazvan
Starting Member

Romania
14 Posts
Posted - 01/30/2009 :  03:44:32  Show Profile  Reply with Quote
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

Sweden
29138 Posts
Posted - 01/30/2009 :  03:47:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

marginerazvan
Starting Member

Romania
14 Posts
Posted - 01/30/2009 :  04:32:41  Show Profile  Reply with Quote
In my case, in Temp_table there will not be any duplicates for a user. So the Join operation will not produce duplicate records.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 01/30/2009 :  04:53:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
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
Execution plans are the following
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)
SQL Profiler results are the following
INNER JOIN method
Reads		22
Parse & compile	1 ms
Execution	0 ms
IN method

Reads		11
Parse & compile	9 ms
Execution	1 ms


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 01/30/2009 :  04:59:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 01/30/2009 :  05:07:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

marginerazvan
Starting Member

Romania
14 Posts
Posted - 01/30/2009 :  07:39:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000