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 2005 Forums
 Transact-SQL (2005)
 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:45:42  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.

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 01/30/2009 :  04:07:10  Show Profile  Reply with Quote
duplicate post
see in
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118899
Go to Top of Page

Kokkula
Starting Member

India
41 Posts

Posted - 01/30/2009 :  04:13:01  Show Profile  Reply with Quote
Where clause will be faster when compared to Joins. It may be better in performance if you use a proper join criteria. But when taken into consideration Where clase will be faster in execution.

--
Thanks,
Pavan
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 01/30/2009 :  04:17:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What do you base that on?



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:35:44  Show Profile  Reply with Quote
This topic is not a duplicate of the other topic. The problem relates to SQL Server 2000 and SQL Server 2005. I suspect that what is quicker for one SQL, is slower for the other. That's why I wrote the same question in boths SQL 2000 and 2005
Go to Top of Page

Kokkula
Starting Member

India
41 Posts

Posted - 01/30/2009 :  04:45:32  Show Profile  Reply with Quote
Refer to the link mentioned below where each and every clause has its own importance in improving the performance.

http://www.sql-server-performance.com/tips/tsql_main.aspx

--
Thanks,
Pavan

Thanks,
Pavan
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 01/30/2009 :  05:05:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by marginerazvan

That's why I wrote the same question in boths SQL 2000 and 2005
Why do you think there will be a difference in SQL Server 2000 and SQL Server 2005 or even SQL Server 2008?

Go to the other post and use the testing code I posted.
Run it for both SQL Server 2000 and SQL Server 2005. It will return same execution plan.

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"

Edited by - SwePeso on 01/30/2009 05:06:36
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