Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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
30421 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  
 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.06 seconds. Powered By: Snitz Forums 2000