Why this query is so slow? - 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

SQL Server Administration (2005)

Why this query is so slow?

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-02-21 : 07:48:09

Hi,

I've found out that each query that calls this function is very slow. I'd like to know why this function is so slow.
any idea is appreciated

ALTER Function [membership].[fn_GetAllProjectPerUserApplication](@UserName varchar(50),@ConsortiumParty varchar(100), @ApplicationName varchar(50))
Returns table as
return
	
	select ProjectCode ProjectCode from membership.tb_UsersInProjectApplication
	where UserName = @UserName and [Application] = @ApplicationName
		union
	select P.Code from membership.tb_ProjectRoles PR
	inner join base.tb_Project_Application PA
	on PR.ProjectApplicationID = PA.ID
	inner join base.tb_Projects P
	on PA.ProjectID = P.ID
	where  PR.FullRoleName = @ApplicationName + '.ALL.' + @ConsortiumParty + '.ALL.ALL.ALL.ALL'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 09:27:22

What does execution plan say?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 01:57:14

look for costly steps in execution plan as a start. also does the tables involved have proper indexes? how large are those tables?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 02:02:22

one thing you could do is if you're sure the values coming from both the selects wont be same, then you can use union all which will perform better than union

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources