Sql server performance issue with aggregate function

Question

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

I take more than one minute to return the result

here is the aggregate function code

ALTER FUNCTION [dbo].[GetParticipationEvaluation](@Participation_ID int)
RETURNS @TabEvaluation TABLE (
   JudgeCount               int      NOT NULL,
   JudgeEvaluationCount     int     NOT NULL,
   Evaluation               float

) 
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;

and this is the stored procedure 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

I would like to know the part that causes this bad performance

Check the query plan see if you can find some obviously issue, like lack of index etc. — ljh
– ljh, Commented Mar 20, 2013 at 7:44
We don't have your table definitions (including indexes), nor your data - so its unlikely that we can analyze the issue. As ljh says, looking at the execution plan is an obvious place to start. One thing that leaps out to me is that the two SELECT inside the function look like they could be replaced with a single one (with judicious use of CASE to restrict one set of AVG/COUNT to only apply to some of the values) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 20, 2013 at 7:50

ljh · Accepted Answer · 2013-03-20 07:47:38Z

1

Normally you need index on 'join columns' and 'where clause', try to get the execution plan, see if there are index missing.

answered Mar 20, 2013 at 7:47

ljh

2,6341 gold badge16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

moustafa malik Over a year ago

your answer is right the performance is optimized from 1 minute to less than 5 seconds by creating index on the Participation_ID column which I use it in the where caluse in the select statement in table-valued function

Oleksandr Fedorenko · Accepted Answer · 2013-03-20 08:37:05Z

Try this procedure without slow function

CREATE procedure [dbo].[GetEvalutionStatistics]
as
select  p.[Participation_ID]
    ,p.[Name_Ar]
    ,p.[Name_En]
    ,p.[Is_Submitted]
    ,p.[Student_ID]
    ,p.[Result_ID]
    ,p.[Field_ID]
    ,p.[Category_ID]
    ,p.[Actual_Field_ID]
    ,p.[Current_Member_ID]
    ,p.[Sys_Is_Active]
    ,p.[Turnitin_Value]
    ,p.[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] p
    left join [Participation_Field_List] as [Actual_Field_ID] on [Actual_Field_ID].[Field_ID]= p.[Actual_Field_ID]
    left join [Participation_Result] as [Result_ID] on [Result_ID].[Result_ID]= p.[Result_ID]
OUTER APPLY (
             SELECT COUNT(CASE WHEN Sys_Is_Active=1 and status_ID = 2 THEN 1 END) AS JudgeCount,
                    AVG(CASE WHEN Sys_Is_Active=1 THEN Evaluation END) AS Evaluation,
                    COUNT(CASE WHEN Sys_Is_Active=1 THEN 1 END) AS JudgeEvaluationCount      
             FROM Judge_Participation 
             WHERE Participation_ID = p.participation_ID and (Sys_Is_Active=1 OR status_ID =2)
             ) Eval
where p.Sys_Is_Active=1 and p.is_submitted=1

Mark Kremers · Accepted Answer · 2013-03-20 07:56:12Z

0

Well, i see you have quite some logic in your function that you use in the outer apply. Did you test it without this function? Ofcourse you will be missing logic, but to pinpoint if the function or the rest of the query is the problem it is a possiblity.

The use of functions is something that i try not to, unless i really have to. For what i can see you can put all the logic into the stored procedure.

I would look into the query plan that this query will generate. If you don't know how to do that, at http://plixa.nl/how-to-read-query-execution-plans/ you can find how to.

answered Mar 20, 2013 at 7:56

Mark Kremers

1,79712 silver badges21 bronze badges

Comments

Thangamani Palanisamy · Accepted Answer · 2013-03-20 09:25:16Z

0

I thought the performance cost due to usage of functions in this query. Since the calling the user defined function in the query it will degrade the performance.

so think try alternate way to avoid functions.

And please check the indexes in the table.

please refer following links

http://www.mindfiresolutions.com/SqlServer-Query-Optimization-Tips-470.php

http://www.mssqltips.com/sql-server-tip-category/37/query-optimization/

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

answered Mar 20, 2013 at 9:25

Thangamani Palanisamy

5,3104 gold badges36 silver badges39 bronze badges

Collectives™ on Stack Overflow

Sql server performance issue with aggregate function

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related