Query Optimization - 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

SQL Server Development (2000)

Query Optimization

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-29 : 02:56:10

Dear All,

How this query can be optimized

select isno from sessiondetails where sessionid=(select sessionid from Processdetails_1 where ( fileid=(select fileid from filedetails where filename='00012') and activityid=(select activityid from Activitydetails where activitycode='Stage1')))

Thanks in Advance
Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-29 : 03:08:20

You never heard of INNER JOIN ?


select 	d.isno
from	sessiondetails d inner join Processdetails_1 p
	on	d.sessionid	= p.sessionid
	inner join filedetails f
	on	p.fileid	= f.fileid
	inner join Activitydetails a
	on	p.activityid	= a.activityid
where	f.[filename]	= '00012'
and	a.activitycode	= 'Stage1'

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-29 : 08:25:15

Thanks Khtan

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-29 : 08:43:17

Dear Khtan,
I noticed one point.
The query which i have posted took 0 milli second to return the query from sql
But the query with inner join took 13 ms to return the value for the same condition

So considering this what query I have to use

Dana

Kristen
Test

22859 Posts

Posted - 2007-05-29 : 08:53:10

Not relevant. Your old query was probably cached, the new one probably not.

However, the inner join approach may give more (duplicate) results if some of the relationships are one:many

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 00:28:33

use DISTINCT or GROUP BY if there is duplciate isno

Kristen
Test

22859 Posts

Posted - 2007-05-30 : 13:27:27

I don't think that's a good approach. It just disguises the problem.

Where there is a one:many and the only purpose of the "JOIN" is to establish the existence of an Associated Record (i.e. NO columns are used in the SELECT from that table) then it is better to use EXISTS (or just maybe "IN").

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources