July 11, 2025 at 11:02 am
Hi Experts,
Thanks in advance. I am new to SQL. Can you please help me in optimizing the query?
CREATE FUNCTION [dbo].[UDF_GET_REVIEWERS_INFO_FOR_COI](@awardId NUMERIC, @nomineeIds varchar(max),@phaseSettingId NUMERIC_ID)
RETURNS TABLE
AS
RETURN
SELECT
ReviewerMasterCustomerId=Reviewer.MASTER_CUSTOMER_ID,
ReviewerName=Reviewer.First_Name+' '+Reviewer.Last_Name,
--START Nominator Centric
COICount= CASE WHEN Prog.IS_NOMINATOR_CENTRIC =1 THEN
(SELECT [dbo].UDF_GET_NOMINATOR_CENTRIC_COI_COUNT(@phaseSettingId,NomineeReviewer.REVIEWER_MAST_CUST_ID,1))
ELSE sum(CASE WHEN NomineeReviewer.COI_STATUS=1 THEN 1 ELSE 0 END) END,
--END Nominator Centric
[Status]=Code.Descr FROM
PROGRAM Prog
JOIN PHASE_SETTING PhaseSetting ON Prog.AWARD_ID=PhaseSetting.AWARD_ID
JOIN WORKFLOW_PHASE Phase ON PhaseSetting.AWARD_WORKFLOW_PHASE_ID=Phase.AWARD_WORKFLOW_PHASE_ID
LEFT JOIN WORKFLOW_PHASE ParentPhase ON Phase.PARENT_WORKFLOW_PHASE_ID=ParentPhase.AWARD_WORKFLOW_PHASE_ID
LEFT JOIN PHASE_SETTING ParentPhaseSetting ON ParentPhase.AWARD_WORKFLOW_PHASE_ID=ParentPhaseSetting.AWARD_WORKFLOW_PHASE_ID
JOIN NOMINEE_REVIEWER NomineeReviewer ON NomineeReviewer.AWARD_ID=Prog.AWARD_ID AND (NomineeReviewer.AWARD_PHASE_SETTING_ID=PhaseSetting.AWARD_PHASE_SETTING_ID OR NomineeReviewer.AWARD_PHASE_SETTING_ID=ParentPhaseSetting.AWARD_PHASE_SETTING_ID )
JOIN CUSTOMER_MASTER Reviewer ON NomineeReviewer.REVIEWER_MAST_CUST_ID=Reviewer.MASTER_CUSTOMER_ID
JOIN CODE_MASTER Code ON NomineeReviewer.[STATUS]=Code.Code AND Code.TYPE='NOMINEE_STATUS'
WHERE
(Prog.AWARD_ID=@awardId OR Prog.AWARD_GROUP_ID=@awardId)
AND ((@nomineeIds is null) or ((@nomineeIds is not null) and (NomineeReviewer.[AWARD_NOMINEE_ID] in (select * from UDF_SPLITAWARDIDS(@nomineeIds)))))
AND ((@phaseSettingId is null) OR ((@phaseSettingId is not null) AND (ParentPhaseSetting.AWARD_PHASE_SETTING_ID=@phaseSettingId OR PhaseSetting.AWARD_PHASE_SETTING_ID=@phaseSettingId)))
--START Nominator Centric
GROUP BY Reviewer.MASTER_CUSTOMER_ID,Code.DESCR,Reviewer.First_Name,Reviewer.Last_Name,
CASE WHEN Prog.IS_NOMINATOR_CENTRIC=1 THEN NomineeReviewer.COI_STATUS ELSE Prog.IS_NOMINATOR_CENTRIC END,Prog.IS_NOMINATOR_CENTRIC,NomineeReviewer.REVIEWER_MAST_CUST_ID
--END Nominator Centric
July 11, 2025 at 1:18 pm
At first glance you use a lot of user defined functions. That makes it difficult for the SQL Server to optimize the execution as it is hidden in function logic.
Have a look at "SARGABLE" queries / how to think like the engine for indexing basics
You may want to post some SQL (DDL / sampledata insert scripts / expected outcome) so that the forum user easily can reproduce the situation.
August 14, 2025 at 7:21 pm
I would guess the answer lies in upgrading the design of the database so less of the values you are after need to be calculated with an inner select that itself applies a function. The optimizer cannot determine ahead of time what values it will receive so it cannot apply optimizations here.
Also , I would see if the join columns that you join on have non clustered (or a clustered) indexes on them.
----------------------------------------------------
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply