inner query vs jon - 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 2005 Forums

Transact-SQL (2005)

inner query vs jon

Author

Topic

Jomypgeorge
Starting Member

31 Posts

Posted - 2011-03-02 : 00:46:13

hi friends,

i have a doubt regarding inner query.

my query is like


select id from dbo.student_base
where id in (Select id from dbo.student_sem where semid = 1)

as we know we can make this query using join as


select a.id
from dbo.student_base a join dbo.student_sem b on a.id = b.id
where b.semid = 1

my doubt is about performance difference between these two queries.
is inner query is slower than join version?
how these two are executed by database?

thanks for any suggestions in advance....

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-02 : 01:37:41

See the execution plan. This will clearly give you idea on how the query is processed.

Jomypgeorge
Starting Member

31 Posts

Posted - 2011-03-02 : 02:01:00

thanks pk_bohra
but could you explain what does it mean

when i checked execution plan it shown as

for first query
table scan student_base 48%
table scan student_sem 50%
nested loop 2%

for second query
table scan student_base 13%
table scan student_sem 13%
Hash match 73%

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-03-02 : 04:50:00

Before you start....Have you any indices on the tables?
With indices you will get different execution plan results from what you currently see.

columns of use to the query include
a.id
b.id
&
b.semid

Jomypgeorge
Starting Member

31 Posts

Posted - 2011-03-02 : 06:56:46

i would like to know if there is any difference in above two queries without indexing?

will inner query version run faster?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-03 : 10:43:47

quote:
Originally posted by Jomypgeorge

i would like to know if there is any difference in above two queries without indexing?

will inner query version run faster?

I don't know which would run faster. The total cost from the Execution Plans would give you a good idea. Or you could time them by running profiler and looking at duration.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources