title: Sql server performance issue with aggregate function
body: <p>I have a stored procedure that make a select statement ( 5000 record).
  inside the select statement I make a call to table-valued function.
  the table-valued function perform some aggregation calculation</p>
  
  <p>I take more than one minute to return the result </p>
  
  <p>here is the aggregate function code
  <pre><code>ALTER FUNCTION [dbo].[GetParticipationEvaluation](@Participation_ID int)
  RETURNS @TabEvaluation TABLE (
     JudgeCount               int      NOT NULL,
     JudgeEvaluationCount     int     NOT NULL,
     Evaluation               float<br>
  ) 
  AS
  BEGIN
     declare @JudgeCount int
     declare @JudgeEvaluationCount int
     declare @Evaluation float
     SELECT 
     @JudgeCount=COUNT(*),@Evaluation= AVG(Evaluation) from Judge_Participation where Participation_ID=@Participation_ID and Sys_Is_Active=1
      and status_ID =2
      SELECT 
     @JudgeEvaluationCount=COUNT(*),@Evaluation= AVG(Evaluation) from Judge_Participation where Participation_ID=@Participation_ID and Sys_Is_Active=1
      INSERT INTO @TabEvaluation (JudgeCount, JudgeEvaluationCount, Evaluation)
      select @JudgeCount,@JudgeEvaluationCount,@Evaluation
     RETURN;
  END;
  </pre></code>
  and this is the stored procedure code
  <pre><code>ALTER procedure [dbo].[GetEvalutionStatistics]
  as
  select  [Participation].[Participation_ID]
      ,[Participation].[Name_Ar]
      ,[Participation].[Name_En]
      ,[Participation].[Is_Submitted]
      ,[Participation].[Student_ID]
      ,[Participation].[Result_ID]
      ,[Participation].[Field_ID]
      ,[Participation].[Category_ID]
      ,[Participation].[Actual_Field_ID]
      ,[Participation].[Current_Member_ID]
      ,[Participation].[Sys_Is_Active]
      ,[Participation].[Turnitin_Value]
      ,[Participation].[Turnitin_Link]
      ,Eval.JudgeCount
      ,Eval.JudgeEvaluationCount
      ,Eval.Evaluation
      ,[Actual_Field_ID].[Name_Ar] as 'Actual_Field_ID.Name_Ar'
      ,[Actual_Field_ID].[Name_En] as 'Actual_Field_ID.Name_En'
      ,[Result_ID].[Name_Ar] as 'Result_ID.Name_Ar'
      ,[Result_ID].[Name_En] as 'Result_ID.Name_En'
      --,dbo.getjudgecount(participation_ID,null) 'JudgeCount'
      --,dbo.getjudgecount(participation_ID,2) 'JudgeEvaluationCount'
      --,dbo.GetAvgParticipationEvaluation(Participation_ID) 'Evaluation'
      --,dbo.getParticipationSpecialist(Participation_ID) as 'specialist'
  from [Participation] 
      left join [Participation_Field_List] as [Actual_Field_ID] on [Actual_Field_ID].[Field_ID]=[Participation].[Actual_Field_ID]
      left join [Participation_Result] as [Result_ID] on [Result_ID].[Result_ID]=[Participation].[Result_ID]
  OUTER APPLY dbo.GetParticipationEvaluation([Participation].participation_ID) Eval
  where 
       participation.Sys_Is_Active=1 and participation.is_submitted=1
  </pre></code>
  I would like to know the part that causes this bad performance </p>