聚合函数的 SQL Server 性能问题

Question

我有一个存储过程，可以生成一条选择语句(5000 条记录)。在 select 语句中，我调用了表值函数。表值函数执行一些聚合计算

我花了一分多钟才返回结果

这里是聚合函数代码

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;

这是存储过程代码

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

我想知道导致这种糟糕表现的部分

Answer 1

通常你需要'join columns'和'where clause'上的索引，尝试获取执行计划，看是否缺少索引。