Why this Function is Slow?!

• peace2007

  SSCrazy

  Points: 2154

  More actions

  February 21, 2009 at 5:45 am

  #383990

  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'

• Lowell

  SSC Guru

  Points: 323482

  More actions

  February 21, 2009 at 6:26 am

  #948144

  both sqls in the union seem to be filtering on Application name, so If they ran alone, I would think they'd be fairly quick.

  the union instead of union all could be a little slower, since it would try to merge duplicates...

  is it necessary to union and not union all?

  last that i can suggest, is there an index on tb_ProjectRoles.PR.FullRoleName? since you are setting that to a known value in the second part of the query, it might require an table scan if there is no index.

  And on the first part of the query, an index on tb_UsersInProjectApplication.username?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  February 21, 2009 at 7:30 am

  #948152

  peace2007 (2/21/2009)

  ---

  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.

  Execution plan?

  Table definitions and index definitions?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass