CODEWITHSUNDEEP
sqlsql-serverperformanceaggregate
moustafa malik
moustafa malik

Reputation: 78

Sql server performance issue with aggregate function

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

Upvotes: 3

Views: 2085

Answers (4)

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5290

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/

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 1

Mark Kremers
Mark Kremers

Reputation: 1789

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.

Upvotes: 0

ljh
ljh

Reputation: 2594

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

Upvotes: 1

Related Questions