Stored procedure low performance, maybe due too many REPLACE

Transact-SQL
1

Hello everybody,
I have to investigate how an old "legacy" stored procedure never shows performance
unacceptable.
It is very long and with several joins.

In the code I noticed this endless series of REPLACE:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(title,'"',''),'”',''),'\',''),'/',''),':',''),'*',''),'?',''),'<',''),'	',''), '>', ''), '|', '') AS title,

Could this be a cause of low performance?

And most importantly, how could it be improved?

Thanks a lot to those who can give me an idea.

Luis

2

Those REPLACEs are not likely to cause much of a performance issue.

1 Like
3

If you are on SQL 2017 or later, you can use TRANSLATE function. That said, I agree with Scott that it is very unlikely that REPLACE is the cause of the performance issue.

4

Is under SQL Server 2008.
So Replace is not the culprit?
Give me some time and I report here the entire sp.

L.

5

This is the entire stored procedure:

ALTER PROC [dbo].[usp_GetActivityInvitee]
(@ActivityId INT = NULL)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ActivityTypeCode INT,
            @feeGridId INT,
            @activityDate DATE,
            @activityDate1 DATETIME,
            @ActivityIdTemp INT;

    SET @ActivityIdTemp = @ActivityId;

    SELECT @ActivityTypeCode = code
    FROM ActivityType at (NOLOCK)
        JOIN Activity a (NOLOCK)
            ON a.activityTypeId = at.activityTypeId
    WHERE activityId = @ActivityIdTemp;

    SELECT @activityDate = activityDate
    FROM ActivityDetails (NOLOCK)
    WHERE activityId = @ActivityIdTemp;
    SELECT @activityDate1 = activityDate
    FROM ActivityDetails (NOLOCK)
    WHERE activityId = @ActivityIdTemp;

    SET @feeGridId =
    (
        SELECT TOP 1
               ISNULL(feeGridId, 0)
        FROM FeeGrid (NOLOCK)
        WHERE Start_Date <= @activityDate
              AND Expiry_Date >= @activityDate
              AND ISACTIVE = 1
    );


    IF (@ActivityTypeCode = 109) --ISP        
    BEGIN


        SELECT DISTINCT
               ai.actvityInviteeId,
               ai.activityId,
               ai.inviteeId,
               i.firstname,
               i.lastname,
               i.uniqueCode,
               invFirstName,
               invLastName,
               i.inviteeTypeId,
               ISNULL(
               (
                   SELECT InviteeType FROM InviteeType WHERE inviteeTypeId = i.inviteeTypeId
               ),
               'HCP'
                     ) 'inviteeType',
               ISNULL(
               (
                   SELECT inviteeTypeCode
                   FROM InviteeType
                   WHERE inviteeTypeId = i.inviteeTypeId
               ),
               401
                     ) 'inviteeTypeCode',
               CASE
                   WHEN ISNULL(ai.ExpertLevelId, 0) = 0 THEN
                       i.[expertLevelId]
                   ELSE
                       ISNULL(ai.ExpertLevelId, 0)
               END 'expertLevelId',
               CASE
                   WHEN ISNULL(ai.ExpertLevelId, 0) = 0 THEN
                       ISNULL(
                       (
                           SELECT LevelAcronym FROM ExpertLevel WHERE expertLevelId = i.expertLevelId
                       ),
                       'NA'
                             )
                   ELSE
                       ISNULL(
                       (
                           SELECT LevelAcronym
                           FROM ExpertLevel
                           WHERE expertLevelId = ai.expertLevelId
                       ),
                       'NA'
                             )
               END 'expertLevel',
               CASE
                   WHEN ISNULL(ai.ExpertLevelId, 0) = 0 THEN
                       ISNULL(
                       (
                           SELECT expertLevelCode
                           FROM ExpertLevel
                           WHERE expertLevelId = i.expertLevelId
                       ),
                       'NA'
                             )
                   ELSE
                       ISNULL(
                       (
                           SELECT expertLevelCode
                           FROM ExpertLevel
                           WHERE expertLevelId = ai.expertLevelId
                       ),
                       'NA'
                             )
               END 'expertLevelCode',
               i.speciality,
               i.AlternateSpecialty,
               ai.MedicalApproverDate,
               ai.hcpRoleId,
               r.hcpRole,
               r.roleCode,
               IsHonorarium,
               travelHours,
               (
                   SELECT TOP 1
                          STR([fromRange], 10, 1) + ' - ' + STR([toRange], 10, 1) AS DisplayHours
                   FROM TimeCompensationMatrix
                   WHERE feegridId = @feeGridId
                         AND compensationCode = 800
                         AND sessionrate = travelHours
               ) AS DisplayHours,
               prepareHours,
               isFeePaid,
               timeSpent,
               facilitationCharge,
               fee,
               isLogisticsRequired,
               ai.VND_ID,
               vv.VND_CODE,
               vv.VND_NAME,
               comments,
               ai.createdOn,
               DATEPART(YEAR, ad.activityDate) AS createdOnYear,
               a.statusId AS ActivityStatusId,
               ai.createdBy,
               ai.modifiedOn,
               ai.modifiedBy,
               ac.activityContractId,
               ac.contractTypeId,
               ct.name [contractType],
               ct.Code [contractCode],
               ac.doctorAddress,
               ac.lectureSubject,
               ac.entityAddress,
               ac.targetAudience,
               ac.seniorDirector,
               ac.buId,
               bu.name 'buname',
               ac.panNumber,
               ac.advisoryBoardName,
               ac.startDate,
               ac.endDate,
               ac.numberofMeeting,
               ac.costPerMeeting,
               ac.drugName,
               ac.advisoryBoardTopic,
               ac.theraputicArea,
               ac.specialityArea,
               ac.travelPreparationTime,
               ac.location,
               ac.honorariumJustification,
               ac.legalDirector,
               ac.rbmUserId,
               vu.LASTNAME + ', ' + vu.FIRSTNAME 'rbmusername',
               ac.topicDiseaseArea,
               ac.dateOfContract,
               ac.diseaseTherapyName,
               ac.doctorsRegistrationNumber,
               ac.contractDocument,
               ac.closureDocumentFileName,
               ac.[periodOfActivity],
               ac.[descriptionOfSupport],
               ac.[objectivesOfEvent],
               ac.[isBankTransfer],
               ac.[accountNumber],
               ac.[bankName],
               ac.[paymentPeriod],
               ac.[codesOfPractice],
               ac.[isMaterialbenefitRequired],
               ac.[IsSanofiLogo],
               ac.[logoDetails],
               ac.[permittedPurpose],
               ac.NameOfCities,
               ac.expertName,
               ac.programName,
               ac.meetingTopic,
               ac.ProgramDate,
               ac.createdBy,
               ac.createdOn,
               ac.modifiedBy,
               ac.modifiedOn,
               ac.ContractServiceFileName,
               ac.EngagementName,
               ac.EngagementType,
               ac.DescriptionofService,
               ac.Deliverables,
               ac.ContactPersonOfSanofi,
               ac.ContactPersonEmailAddress,
               ISNULL(ac.statusId, 0) AS 'statusId',
               ISNULL(s.code, 0) AS 'statusCode',
               ac.taggedContractId,
               ac2.startDate 'taggedStartDate',
               ac2.endDate 'taggedEndDate',
               at.Code 'activityTypeCode',
               CONVERT(   BIT,
                          (CASE
                               WHEN
                               (
                                   (at.Code = 103)
                                   AND (ac.taggedContractId IS NOT NULL)
                                   AND (a.statusid IN ( 2, 5 ))
                               ) THEN
                                   dbo.udf_GetChildActivityContractStatus_Copy(ac.activityContractId)
                               ELSE
                                   1
                           END
                          )
                      ) 'CanAmend',
               CASE
                   WHEN
                   (
                       ac.statusId = 20
                       AND ct.Code IN ( 205, 208 )
                   ) THEN
                       CONVERT(BIT, dbo.udf_GetInviteesConsultantContractStatus_Copy(ai.inviteeId))
                   ELSE
                       0
               END 'IsContractExist',
               rt.RequestTypeId 'RequestId',
               rt.RequestType 'RequestName',
               rt.RequestTypeCode 'RequestCode',
               CASE
                   WHEN EXISTS
    (
        SELECT 1
        FROM ActivityContracts (NOLOCK)
        WHERE taggedContractId = ac.activityContractId
    )          THEN
                   (
                       SELECT COUNT('X')
                       FROM ActivityContracts (NOLOCK)
                       WHERE taggedContractId = ac.activityContractId
                   )
                   ELSE
                       0
               END AS 'TaggedContractNo',
               CASE
                   WHEN bill.activityInviteeId IS NULL
                        AND ai.hcpRoleId = 1 THEN
                       1
                   ELSE
                       0
               END AS ShowAddPayee,
               i.isPresentInMDM,
               CASE
                   WHEN (ISNULL(
                         (
                             SELECT CONVERT(DECIMAL, SUM(AIIQ.FEE))
                             FROM ActivityInvitee AIIQ (NOLOCK)
                                 LEFT JOIN Invitee IIQ (NOLOCK)
                                     ON AIIQ.INVITEEID = IIQ.inviteeId
                                        AND IIQ.inviteeTypeId = 1
                                 INNER JOIN Activity AIQ (NOLOCK)
                                     ON AIIQ.ActivityId = AIQ.ActivityId
                                        AND DATEPART(YEAR, AIQ.CreatedOn) = DATEPART(YEAR, GETDATE())
                                        AND AIQ.statusId IN ( 2, 3, 5, 6, 7, 8 )
                             WHERE IIQ.UNIQUECODE = i.uniqueCode
                         ),
                         0
                               )
                        ) <
                   (
                       SELECT ISNULL(H.MaxHonorariumAmount, 0)
                       FROM HonorariumAlert H (NOLOCK)
                       WHERE H.ExpertLevelCode =
                       (
                           SELECT LevelAcronym
                           FROM ExpertLevel (NOLOCK)
                           WHERE expertLevelId = i.expertLevelId
                       )
                             AND H.Year = DATEPART(YEAR, GETDATE())
                   ) THEN
                       0
                   ELSE
                       1
               END IsHonarariumCrossed,
               i.IsHospitalDr,
               (CASE
                    WHEN EXISTS
    (
        SELECT 1
        FROM V_VENDOR_MASTER vnd (NOLOCK)
        WHERE vnd.PREV_ACCOUNT_NO = i.uniqueCode
              AND VND_STATUS = 1
    )          THEN
                    (
                        SELECT COUNT(PREV_ACCOUNT_NO)
                        FROM V_VENDOR_MASTER vnd (NOLOCK)
                        WHERE vnd.PREV_ACCOUNT_NO = i.uniqueCode
                              AND VND_STATUS = 1
                    )
                    ELSE
                        0
                END
               ) AS isPresentInVendor,
               (CASE
                    WHEN EXISTS
    (
        SELECT 1
        FROM ActivityInvitee AI1 (NOLOCK)
            INNER JOIN ActivityDetails AD1 (NOLOCK)
                ON AI1.activityId = AD1.activityId
                   AND AI1.activityId NOT IN ( ai.activityId )
                   AND AI1.inviteeId = ai.inviteeId
                   AND AD1.activityDate = @activityDate1
    )          THEN
                    (
                        SELECT
                            (
                                SELECT A2.activityNumber + ' , '
                                FROM ActivityInvitee AI2 (NOLOCK)
                                    INNER JOIN ActivityDetails AD2 (NOLOCK)
                                        ON AI2.activityId = AD2.activityId
                                           AND AI2.inviteeId = ai.inviteeId
                                           AND AD2.activityDate = @activityDate1
                                    INNER JOIN Activity A2 (NOLOCK)
                                        ON AI2.activityId = A2.activityId
                                           AND A2.activityid NOT IN ( @ActivityIdTemp )
                                           AND A2.statusId NOT IN ( 23 )
                                           AND A2.activityNumber IS NOT NULL
                                FOR XML PATH('')
                            )
                    )
                    ELSE
                        NULL
                END
               ) AS AlertMessage
        FROM activityInvitee ai (NOLOCK)
            LEFT JOIN Invitee i (NOLOCK)
                ON ai.inviteeId = i.inviteeId
                   AND ai.activityId = @ActivityIdTemp
            JOIN activity a (NOLOCK)
                ON a.activityId = ai.activityId
                   AND a.cycleId >= 57
            JOIN ActivityDetails ad (NOLOCK)
                ON ad.activityId = a.activityId
            LEFT JOIN HcpRequestType (NOLOCK) rt
                ON ai.logisticRequest = rt.RequestTypeId
            JOIN ActivityType at (NOLOCK)
                ON at.activityTypeId = a.activityTypeId
            JOIN InviteeType it (NOLOCK)
                ON i.inviteeTypeId = it.inviteeTypeId
            LEFT JOIN HcpRole r (NOLOCK)
                ON r.hcpRoleId = ai.hcpRoleId
            LEFT JOIN V_VENDOR_MASTER vv (NOLOCK)
                ON ai.VND_ID = vv.VND_ID
            LEFT JOIN ActivityContracts ac (NOLOCK)
                ON ai.actvityInviteeId = ac.actvityInviteeId
            LEFT JOIN Status s (NOLOCK)
                ON ac.statusId = s.statusId
            LEFT JOIN ContractType ct (NOLOCK)
                ON ct.contractTypeId = ac.contractTypeId
            LEFT JOIN ContractGrid cg (NOLOCK)
                ON ct.contractTypeId = cg.contractTypeId
            LEFT JOIN BusinessUnit bu (NOLOCK)
                ON ac.buId = bu.buId
            LEFT JOIN V_USERS vu (NOLOCK)
                ON CAST(vu.[USER_ID] AS VARCHAR(25)) = ac.rbmUserId
            LEFT JOIN ActivityContracts ac2
                ON ac2.activityContractId = ac.taggedContractId
            LEFT JOIN
            (
                SELECT DISTINCT
                       activityInviteeId
                FROM dbo.ActivityBilling
                WHERE activityId = @ActivityIdTemp
                      AND billStatusCode IN ( 602, 603 )
            ) bill
                ON bill.activityInviteeId = ai.actvityInviteeId
            LEFT JOIN HonorariumAlert HA
                ON HA.ExpertLevelCode =
                (
                    SELECT LevelAcronym FROM ExpertLevel WHERE expertLevelId = i.expertLevelId
                )
                   AND HA.Year = DATEPART(YEAR, GETDATE());

    END;

    ELSE
    BEGIN
        SELECT DISTINCT
               ai.actvityInviteeId,
               ai.activityId,
               ai.inviteeId,
               i.firstname,
               i.lastname,
               i.uniqueCode,
               invFirstName,
               invLastName,
               i.inviteeTypeId,
               it.inviteeType,
               CASE
                   WHEN ISNULL(ai.ExpertLevelId, 0) = 0 THEN
                       i.[expertLevelId]
                   ELSE
                       ISNULL(ai.ExpertLevelId, 0)
               END 'expertLevelId',
               CASE
                   WHEN ISNULL(ai.ExpertLevelId, 0) = 0 THEN
                       ISNULL(
                       (
                           SELECT LevelAcronym
                           FROM ExpertLevel (NOLOCK)
                           WHERE expertLevelId = i.expertLevelId
                       ),
                       'NA'
                             )
                   ELSE
                       ISNULL(
                       (
                           SELECT LevelAcronym
                           FROM ExpertLevel
                           WHERE expertLevelId = ai.expertLevelId
                       ),
                       'NA'
                             )
               END 'expertLevel',
               CASE
                   WHEN ISNULL(ai.ExpertLevelId, 0) = 0 THEN
                       ISNULL(
                       (
                           SELECT expertLevelCode
                           FROM ExpertLevel (NOLOCK)
                           WHERE expertLevelId = i.expertLevelId
                       ),
                       'NA'
                             )
                   ELSE
                       ISNULL(
                       (
                           SELECT expertLevelCode
                           FROM ExpertLevel
                           WHERE expertLevelId = ai.expertLevelId
                       ),
                       'NA'
                             )
               END 'expertLevelCode',
               i.speciality,
               i.AlternateSpecialty,
               ai.MedicalApproverDate,
               ai.hcpRoleId,
               r.hcpRole,
               r.roleCode,
               IsHonorarium,
               travelHours,
               (
                   SELECT TOP 1
                          STR([fromRange], 10, 1) + ' - ' + STR([toRange], 10, 1) AS DisplayHours
                   FROM TimeCompensationMatrix (NOLOCK)
                   WHERE feegridId = @feeGridId
                         AND compensationCode = 800
                         AND sessionrate = travelHours
               ) AS DisplayHours,
               prepareHours,
               isFeePaid,
               timeSpent,
               facilitationCharge,
               fee,
               isLogisticsRequired,
               ai.VND_ID,
               vv.VND_CODE,
               vv.VND_NAME,
               comments,
               ai.createdOn,
               DATEPART(YEAR, ad.activityDate) AS createdOnYear,
               a.statusId AS ActivityStatusId,
               ai.createdBy,
               ai.modifiedOn,
               ai.modifiedBy,
               ac.activityContractId,
               ac.contractTypeId,
               ct.name [contractType],
               ct.Code [contractCode],
               ac.doctorAddress,
               ac.lectureSubject,
               ac.entityAddress,
               ac.targetAudience,
               ac.seniorDirector,
               ac.buId,
               bu.name 'buname',
               ac.panNumber,
               ac.advisoryBoardName,
               ac.startDate,
               ac.endDate,
               ac.numberofMeeting,
               ac.costPerMeeting,
               ac.drugName,
               ac.advisoryBoardTopic,
               ac.theraputicArea,
               ac.specialityArea,
               ac.travelPreparationTime,
               ac.location,
               ac.honorariumJustification,
               ac.legalDirector,
               ac.rbmUserId,
               dbo.udf_UserFullName(vu.FIRSTNAME, vu.LASTNAME, vu.CTY_ISO) 'rbmusername',
               ac.topicDiseaseArea,
               ac.dateOfContract,
               ac.diseaseTherapyName,
               ac.doctorsRegistrationNumber,
               ac.contractDocument,
               ac.closureDocumentFileName,
               ac.[periodOfActivity],
               ac.[descriptionOfSupport],
               ac.[objectivesOfEvent],
               ac.[isBankTransfer],
               ac.[accountNumber],
               ac.[bankName],
               ac.[paymentPeriod],
               ac.[codesOfPractice],
               ac.[isMaterialbenefitRequired],
               ac.[IsSanofiLogo],
               ac.[logoDetails],
               ac.[permittedPurpose],
               ac.NameOfCities,
               ac.expertName,
               ac.programName,
               ac.meetingTopic,
               ac.ProgramDate,
               ac.createdBy,
               ac.createdOn,
               ac.modifiedBy,
               ac.modifiedOn,
               ac.ContractServiceFileName,
               ac.EngagementName,
               ac.EngagementType,
               ac.DescriptionofService,
               ac.Deliverables,
               ac.ContactPersonOfSanofi,
               ac.ContactPersonEmailAddress,
               ISNULL(ac.statusId, 0) AS 'statusId',
               ISNULL(s.code, 0) AS 'statusCode',
               ac.taggedContractId,
               ac2.startDate 'taggedStartDate',
               ac2.endDate 'taggedEndDate',
               it.inviteeTypeCode,
               at.Code 'activityTypeCode',
               CONVERT(   BIT,
                          (CASE
                               WHEN
                               (
                                   (at.Code = 103)
                                   AND (ac.taggedContractId IS NOT NULL)
                                   AND (a.statusid IN ( 2, 5 ))
                               ) THEN
                                   dbo.udf_GetChildActivityContractStatus_Copy(ac.activityContractId)
                               ELSE
                                   1
                           END
                          )
                      ) 'CanAmend',
               CASE
                   WHEN
                   (
                       ac.statusId = 20
                       AND ct.Code IN ( 205, 208 )
                   ) THEN
                       CONVERT(BIT, dbo.udf_GetInviteesConsultantContractStatus_Copy(ai.inviteeId))
                   ELSE
                       0
               END 'IsContractExist',
               rt.RequestTypeId 'RequestId',
               rt.RequestType 'RequestName',
               rt.RequestTypeCode 'RequestCode',
               CASE
                   WHEN EXISTS
    (
        SELECT 1
        FROM ActivityContracts (NOLOCK)
        WHERE taggedContractId = ac.activityContractId
    )          THEN
                   (
                       SELECT COUNT('X')
                       FROM ActivityContracts (NOLOCK)
                       WHERE taggedContractId = ac.activityContractId
                   )
                   ELSE
                       0
               END AS 'TaggedContractNo',
               CASE
                   WHEN bill.activityInviteeId IS NULL
                        AND ai.hcpRoleId = 1 THEN
                       1
                   ELSE
                       0
               END AS ShowAddPayee,
               i.isPresentInMDM,
               CASE
                   WHEN (ISNULL(
                         (
                             SELECT CONVERT(DECIMAL, SUM(AIIQ.FEE))
                             FROM ActivityInvitee AIIQ (NOLOCK)
                                 LEFT JOIN Invitee IIQ (NOLOCK)
                                     ON AIIQ.INVITEEID = IIQ.inviteeId
                                        AND IIQ.inviteeTypeId = 1
                                 INNER JOIN Activity AIQ (NOLOCK)
                                     ON AIIQ.ActivityId = AIQ.ActivityId
                                        AND DATEPART(YEAR, AIQ.CreatedOn) = DATEPART(YEAR, GETDATE())
                                        AND AIQ.statusId IN ( 2, 3, 5, 6, 7, 8 )
                             WHERE IIQ.UNIQUECODE = i.uniqueCode
                         ),
                         0
                               )
                        ) <
                   (
                       SELECT ISNULL(H.MaxHonorariumAmount, 0)
                       FROM HonorariumAlert H (NOLOCK)
                       WHERE H.ExpertLevelCode =
                       (
                           SELECT LevelAcronym
                           FROM ExpertLevel (NOLOCK)
                           WHERE expertLevelId = i.expertLevelId
                       )
                             AND H.Year = DATEPART(YEAR, GETDATE())
                   ) THEN
                       0
                   ELSE
                       1
               END IsHonarariumCrossed,
               i.IsHospitalDr,
               (
                   SELECT COUNT(PREV_ACCOUNT_NO)
                   FROM V_VENDOR_MASTER vnd (NOLOCK)
                   WHERE vnd.PREV_ACCOUNT_NO = i.uniqueCode
                         AND VND_STATUS = 1
               ) AS isPresentInVendor,
               (CASE
                    WHEN EXISTS
    (
        SELECT 1
        FROM ActivityInvitee AI1 (NOLOCK)
            INNER JOIN ActivityDetails AD1 (NOLOCK)
                ON AI1.activityId = AD1.activityId
                   AND AI1.activityId NOT IN ( ai.activityId )
                   AND AI1.inviteeId = ai.inviteeId
                   AND AD1.activityDate = @activityDate1
    )          THEN
                    (
                        SELECT
                            (
                                SELECT A2.activityNumber + ' , ' + CONVERT(VARCHAR(12), A2.activityId) + ' ; '
                                FROM ActivityInvitee AI2 (NOLOCK)
                                    INNER JOIN ActivityDetails AD2 (NOLOCK)
                                        ON AI2.activityId = AD2.activityId
                                           AND AI2.inviteeId = ai.inviteeId
                                           AND AD2.activityDate = @activityDate1
                                    INNER JOIN Activity A2 (NOLOCK)
                                        ON AI2.activityId = A2.activityId
                                           AND A2.activityid NOT IN ( @ActivityIdTemp )
                                           AND A2.statusId NOT IN ( 23 )
                                           AND A2.activityNumber IS NOT NULL
                                FOR XML PATH('')
                            )
                    )
                    ELSE
                        NULL
                END
               ) AS AlertMessage
        FROM activityInvitee ai (NOLOCK)
            LEFT JOIN Invitee i (NOLOCK)
                ON ai.inviteeId = i.inviteeId
                   AND ai.activityId = @ActivityIdTemp --Performance Tuning E0378074 
            JOIN activity a (NOLOCK)
                ON a.activityId = ai.activityId
                   AND a.cycleId >= 57
            JOIN ActivityDetails ad (NOLOCK) --RITM2034217        
                ON ad.activityId = a.activityId
            LEFT JOIN HcpRequestType rt (NOLOCK)
                ON ai.logisticRequest = rt.RequestTypeId
            JOIN ActivityType at (NOLOCK)
                ON at.activityTypeId = a.activityTypeId
            LEFT JOIN ExpertLevel el (NOLOCK)
                ON i.expertLevelId = el.expertLevelId
            JOIN InviteeType it (NOLOCK)
                ON i.inviteeTypeId = it.inviteeTypeId
            LEFT JOIN HcpRole r (NOLOCK)
                ON r.hcpRoleId = ai.hcpRoleId
            LEFT JOIN V_VENDOR_MASTER vv (NOLOCK)
                ON ai.VND_ID = vv.VND_ID
            LEFT JOIN ActivityContracts ac (NOLOCK)
                ON ai.actvityInviteeId = ac.actvityInviteeId
            LEFT JOIN Status s (NOLOCK)
                ON ac.statusId = s.statusId
            LEFT JOIN ContractType ct (NOLOCK)
                ON ct.contractTypeId = ac.contractTypeId
            LEFT JOIN ContractGrid cg (NOLOCK)
                ON ct.contractTypeId = cg.contractTypeId
            LEFT JOIN BusinessUnit bu (NOLOCK)
                ON ac.buId = bu.buId
            LEFT JOIN V_USERS vu (NOLOCK)
                ON CAST(vu.[USER_ID] AS VARCHAR(30)) = ac.rbmUserId
            LEFT JOIN ActivityContracts ac2 (NOLOCK)
                ON ac2.activityContractId = ac.taggedContractId
            LEFT JOIN
            (
                SELECT DISTINCT
                       activityInviteeId
                FROM dbo.ActivityBilling (NOLOCK)
                WHERE activityId = @ActivityIdTemp
                      AND billStatusCode IN ( 602, 603 )
            ) bill
                ON bill.activityInviteeId = ai.actvityInviteeId --ITS-CHG0255281        
            LEFT JOIN HonorariumAlert HA (NOLOCK)
                ON HA.ExpertLevelCode =
                (
                    SELECT LevelAcronym
                    FROM ExpertLevel (NOLOCK)
                    WHERE expertLevelId = i.expertLevelId
                )
                   AND HA.Year = DATEPART(YEAR, GETDATE());
    END;
END;
6

Would need DDL for the tables -- base table definition and all indexes -- and the SQL query plan in order to analyze performance in any reasonable way.

7

From a very preliminary look I can say the REPLACE() is not even a blip on any performance issues with this procedure.

I counted 18 tables and 2 views referenced 83 times, plus at least 2 scalar UDFs that are probably also doing data access. As @ScottPletcher stated, we would absolutely need to see a query plan in order to figure out where to start tuning. You can paste query plans here:

And then post the link here on SQLTeam.

Generally speaking, things that are not helping performance:

  1. Multiple embedded subqueries for the same table (ExpertLevel especially)

  2. Multiple queries with the same criteria (V_VENDOR_MASTER) in a conditional expression (EXISTS). Just use the final expression and wrap an ISNULL() around it in case there's no result.

  3. Large segments of the query are repeated in both sections of an IF...ELSE statement. It might perform better to isolate the common segments and run them separately, then add the specific items only in a smaller IF...ELSE block. It would certainly be easier to read.

  4. JOINing 17 tables plus at least another subquery (with a DISTINCT) is going to be very difficult to tune, unless your indexing is absolutely perfect. Again, we'll need a query plan to even begin tuning this.

2 Likes
8

One Idea

is break up the joins .. 10 joins into 4, 4, 2

debugging
add one join at a time - which join when you add its taking long time
which table has the largest data how to make it small using filters

9

These should be all tables/views/functions involved in the stored procedure:



CREATE TABLE [dbo].[ActivityType](
	[activityTypeId] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](200) NULL,
	[code] [tinyint] NULL,
	[CTY_ISO] [char](2) NULL,
	[isActive] [bit] NULL,
	[createdOn] [datetime] NULL,
	[createdBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
 CONSTRAINT [PK_ActivityType] PRIMARY KEY CLUSTERED 
(
	[activityTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ActivityType] ADD  CONSTRAINT [DF_ActivityType_isActive]  DEFAULT ((1)) FOR [isActive]
GO

CREATE TABLE [dbo].[ActivityDetails](
	[activityDetailsId] [int] IDENTITY(1,1) NOT NULL,
	[activityId] [int] NOT NULL,
	[activityDate] [datetime] NULL,
	[location] [varchar](200) NULL,
	[title] [varchar](200) NULL,
	[activityNeedId] [int] NULL,
	[trimmRefNumber] [varchar](50) NULL,
	[description] [varchar](1000) NULL,
	[contractStartDate] [datetime] NULL,
	[contractEndDate] [datetime] NULL,
	[channelEventTypeId] [int] NULL,
	[posTypeId] [int] NULL,
	[mediaType] [varchar](10) NULL,
	[startDate] [datetime] NULL,
	[endDate] [datetime] NULL,
	[mediaTypeId] [int] NULL,
	[regionalExpenseCost] [float] NULL,
	[agencyName] [varchar](200) NULL,
	[isBillsSubmitted] [bit] NULL,
	[isActivityCompleted] [bit] NULL,
	[activityCompletionDate] [datetime] NULL,
	[isAllBillsReceived] [bit] NULL,
	[isActivityClosed] [bit] NULL,
	[IsCSRDigital] [bit] NULL,
	[createOn] [datetime] NULL,
	[createdBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
	[otherParticipant] [int] NULL,
	[reportFilename] [varchar](200) NULL,
	[activityFeedback] [varchar](500) NULL,
	[activityFollowupPlan] [varchar](500) NULL,
	[activityComments] [nvarchar](1000) NULL,
	[OtherAttendees] [int] NULL,
	[aliastitle] [varchar](200) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ActivityDetails]  WITH CHECK ADD  CONSTRAINT [FK_ActivityDetails_Activity] FOREIGN KEY([activityId])
REFERENCES [dbo].[Activity] ([activityId])
GO

ALTER TABLE [dbo].[ActivityDetails] CHECK CONSTRAINT [FK_ActivityDetails_Activity]
GO

CREATE TABLE [dbo].[FeeGrid](
	[feeGridId] [int] IDENTITY(1,1) NOT NULL,
	[CTY_ISO] [char](2) NULL,
	[CUR_ISO] [char](3) NULL,
	[rate] [float] NULL,
	[createdOn] [datetime] NULL,
	[createdBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
	[Start_Date] [datetime] NULL,
	[Expiry_Date] [datetime] NULL,
	[IsActive] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[feeGridId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TimeCompensationMatrix](
	[compensationId] [int] IDENTITY(1,1) NOT NULL,
	[feeGridId] [int] NULL,
	[compensationCode] [varchar](20) NULL,
	[fromRange] [float] NULL,
	[toRange] [float] NULL,
	[sessionRate] [float] NULL,
	[createdOn] [datetime] NULL,
	[createdBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[compensationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TimeCompensationMatrix]  WITH CHECK ADD  CONSTRAINT [FK_TimeCompensationMatrix_FeeGrid] FOREIGN KEY([feeGridId])
REFERENCES [dbo].[FeeGrid] ([feeGridId])
GO

ALTER TABLE [dbo].[TimeCompensationMatrix] CHECK CONSTRAINT [FK_TimeCompensationMatrix_FeeGrid]
GO


CREATE TABLE [dbo].[ActivityContracts](
	[activityContractId] [int] IDENTITY(1,1) NOT NULL,
	[contractTypeId] [int] NULL,
	[activityId] [int] NULL,
	[actvityInviteeId] [int] NULL,
	[inviteeId] [int] NULL,
	[doctorAddress] [varchar](200) NULL,
	[lectureSubject] [varchar](150) NULL,
	[entityAddress] [varchar](500) NULL,
	[targetAudience] [varchar](200) NULL,
	[seniorDirector] [varchar](125) NULL,
	[buId] [int] NULL,
	[panNumber] [varchar](125) NULL,
	[advisoryBoardName] [varchar](150) NULL,
	[startDate] [datetime] NULL,
	[endDate] [datetime] NULL,
	[numberofMeeting] [int] NULL,
	[costPerMeeting] [float] NULL,
	[drugName] [varchar](150) NULL,
	[advisoryBoardTopic] [varchar](125) NULL,
	[theraputicArea] [varchar](125) NULL,
	[specialityArea] [varchar](125) NULL,
	[travelPreparationTime] [varchar](250) NULL,
	[location] [varchar](150) NULL,
	[honorariumJustification] [varchar](150) NULL,
	[legalDirector] [varchar](125) NULL,
	[rbmUserId] [varchar](100) NULL,
	[topicDiseaseArea] [varchar](125) NULL,
	[dateOfContract] [datetime] NULL,
	[diseaseTherapyName] [varchar](125) NULL,
	[doctorsRegistrationNumber] [nvarchar](100) NULL,
	[statusId] [int] NULL,
	[contractDocument] [varchar](150) NULL,
	[createdBy] [int] NULL,
	[createdOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[expertName] [nvarchar](150) NULL,
	[programName] [nvarchar](150) NULL,
	[meetingTopic] [nvarchar](200) NULL,
	[programDate] [datetime] NULL,
	[taggedContractId] [int] NULL,
	[closureDocumentFileName] [varchar](150) NULL,
	[periodOfActivity] [int] NULL,
	[descriptionOfSupport] [varchar](200) NULL,
	[objectivesOfEvent] [varchar](100) NULL,
	[isBankTransfer] [bit] NULL,
	[accountNumber] [bigint] NULL,
	[bankName] [varchar](50) NULL,
	[paymentPeriod] [int] NULL,
	[codesOfPractice] [varchar](200) NULL,
	[isMaterialbenefitRequired] [bit] NULL,
	[IsSanofiLogo] [bit] NULL,
	[logoDetails] [varchar](200) NULL,
	[permittedPurpose] [varchar](200) NULL,
	[NameOfCities] [nvarchar](250) NULL,
	[ContractServiceFileName] [varchar](250) NULL,
	[EngagementName] [varchar](300) NULL,
	[EngagementType] [varchar](300) NULL,
	[DescriptionofService] [varchar](300) NULL,
	[Deliverables] [varchar](300) NULL,
	[ContactPersonOfSanofi] [varchar](300) NULL,
	[ContactPersonEmailAddress] [varchar](300) NULL,
 CONSTRAINT [PK__Activity__06BF5B1A65C116E7] PRIMARY KEY CLUSTERED 
(
	[activityContractId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ActivityContracts]  WITH CHECK ADD  CONSTRAINT [FK_ActivityContracts_Activity] FOREIGN KEY([activityId])
REFERENCES [dbo].[Activity] ([activityId])
GO

ALTER TABLE [dbo].[ActivityContracts] CHECK CONSTRAINT [FK_ActivityContracts_Activity]
GO


CREATE TABLE [dbo].[ActivityInvitee](
	[actvityInviteeId] [int] IDENTITY(1,1) NOT NULL,
	[activityId] [int] NOT NULL,
	[inviteeId] [int] NOT NULL,
	[hcpRoleId] [int] NULL,
	[IsHonorarium] [bit] NOT NULL,
	[travelHours] [float] NULL,
	[prepareHours] [float] NULL,
	[isFeePaid] [bit] NULL,
	[facilitationCharge] [float] NULL,
	[fee] [float] NULL,
	[isLogisticsRequired] [bit] NULL,
	[VND_ID] [int] NULL,
	[comments] [varchar](250) NULL,
	[timeSpent] [float] NULL,
	[logisticRequest] [int] NULL,
	[createdOn] [datetime] NULL,
	[createdBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
	[invFirstName] [varchar](200) NULL,
	[invLastName] [varchar](200) NULL,
	[ExpertLevelId] [int] NULL,
	[IsInvAttended] [bit] NULL,
	[MedicalApproverDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[actvityInviteeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ActivityInvitee]  WITH CHECK ADD  CONSTRAINT [FK_ActivityInvitee_Activity] FOREIGN KEY([activityId])
REFERENCES [dbo].[Activity] ([activityId])
GO

ALTER TABLE [dbo].[ActivityInvitee] CHECK CONSTRAINT [FK_ActivityInvitee_Activity]
GO


CREATE TABLE [dbo].[Invitee](
	[inviteeId] [int] IDENTITY(1,1) NOT NULL,
	[inviteeTypeId] [int] NULL,
	[refId] [nvarchar](50) NULL,
	[firstname] [nvarchar](255) NULL,
	[lastname] [nvarchar](255) NULL,
	[speciality] [nvarchar](255) NULL,
	[address1] [varchar](250) NULL,
	[address2] [varchar](250) NULL,
	[address3] [varchar](250) NULL,
	[state] [varchar](255) NULL,
	[city] [varchar](255) NULL,
	[pincode] [varchar](255) NULL,
	[uniqueCode] [varchar](255) NULL,
	[medicalRegCode] [varchar](255) NULL,
	[panNumber] [varchar](255) NULL,
	[isKOL] [bit] NULL,
	[VND_CODE] [nvarchar](255) NULL,
	[vatRegNo] [varchar](255) NULL,
	[tantinNo] [varchar](255) NULL,
	[status] [varchar](255) NULL,
	[CTY_ISO] [varchar](255) NULL,
	[expertLevelId] [int] NULL,
	[createdOn] [datetime] NULL,
	[modifiedOn] [datetime] NULL,
	[isPresentInMDM] [bit] NULL,
	[IsHospitalDr] [bit] NULL,
	[AlternateSpecialty] [nvarchar](255) NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[HonorariumAlert](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ExpertLevelCode] [varchar](5) NOT NULL,
	[MaxHonorariumAmount] [int] NOT NULL,
	[EmailAlert1] [int] NOT NULL,
	[EmailAlert2] [int] NOT NULL,
	[EmailAlert3] [int] NOT NULL,
	[createdOn] [datetime] NOT NULL,
	[createdBy] [varchar](50) NULL,
	[Year] [int] NOT NULL,
 CONSTRAINT [HonorariumAlert_pk] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HonorariumAlert] ADD  DEFAULT (getdate()) FOR [createdOn]
GO

CREATE TABLE [dbo].[ExpertLevel](
	[expertLevelId] [int] IDENTITY(1,1) NOT NULL,
	[expertLevel] [varchar](50) NOT NULL,
	[expertLevelCode] [tinyint] NOT NULL,
	[LevelAcronym] [varchar](4) NULL,
 CONSTRAINT [PK_ExpertLevel] PRIMARY KEY CLUSTERED 
(
	[expertLevelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[V_VENDOR_MASTER]
AS
SELECT     VND_ID, CTY_ISO, VND_REGNO, VND_CODE, VND_NAME, VND_BANKID, VND_BANKNAME, VND_BANKAC, VND_BANKBR, VND_CTRLKEY, VND_BANKACCHOLDER, 
                      VND_EMPNO, VND_ADDRESS, VND_SOCIALREGNO, VND_CTRLKEY AS 'VND_BANKACCTYPE', VND_ACCGRP, VND_STATUS, VND_CDATE, VND_MDATE, 
                      WH_TAXCODE, PREV_ACCOUNT_NO, ETY_CODE, RECONCILIATION_ACCOUNT
FROM         DB_ACCESS.dbo.vendor_master AS vendor_master_1 WITH (NOLOCK)

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "vendor_master_1"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 269
               Right = 225
            End
            DisplayFlags = 280
            TopColumn = 5
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 24
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 2340
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 2280
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_VENDOR_MASTER'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_VENDOR_MASTER'
GO

CREATE TABLE [dbo].[ContractType](
	[contractTypeId] [int] IDENTITY(1,1) NOT NULL,
	[CTY_ISO] [char](2) NULL,
	[name] [varchar](150) NULL,
	[Code] [tinyint] NULL,
	[isActive] [bit] NULL,
 CONSTRAINT [PK__Contract__DFEA4C2C3A6CA48E] PRIMARY KEY CLUSTERED 
(
	[contractTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ContractGrid](
	[contractGridId] [int] IDENTITY(1,1) NOT NULL,
	[activityTypeId] [int] NULL,
	[hcpRoleId] [int] NULL,
	[inviteeTypeId] [int] NULL,
	[honorarium] [bit] NULL,
	[consultantContract] [bit] NULL,
	[hcpRequest] [smallint] NULL,
	[contractTypeId] [int] NULL,
	[amountPayable] [bit] NULL,
	[CTY_ISO] [char](2) NULL,
	[feeGrid] [bit] NULL,
 CONSTRAINT [PK_ContractGrid] PRIMARY KEY CLUSTERED 
(
	[contractGridId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[BusinessUnit](
	[buId] [int] NOT NULL,
	[name] [varchar](200) NULL,
	[TrimmEntityId] [int] NULL,
	[CTY_ISO] [char](2) NULL,
	[createdOn] [datetime] NULL,
	[createdBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
	[isActive] [bit] NULL,
	[accronym] [varchar](10) NULL,
 CONSTRAINT [PK_BusinessUnit] PRIMARY KEY CLUSTERED 
(
	[buId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE VIEW [dbo].[V_USERS]

AS
SELECT     USER_ID, CTY_ISO, CUR_ISO, NTDOMAIN, NTLOGIN, ETY_ID, DEPARTMENT_ID, TITLE_ID, LASTNAME, FIRSTNAME, LASTNAME_LOCAL, FIRSTNAME_LOCAL, EMAIL, 
                      JOBFUNCTION_ID, MANAGER_USER_ID, COSTCENTER, COSTCENTER_DESC, COSTCENTER_DESC_LOCAL, VND_EMPNO, HR_EMPLOYEE_NUMBER, 
                      WORK_LOCATION_ID, POSITION_ID, EMPLOYEEGROUP_ID, LANG_CODE, SAPNTLOGIN, SIGNATURE_PATH, EPR_THRESHOLD, ISACTIVE, CREATED_ON, 
                      CREATED_BY, UPDATED_ON, UPDATED_BY, APPROVER_LEVEL_ID, BUSINESS_UNIT_ID
FROM         DB_ACCESS.dbo.USERS AS USERS_1


GO

CREATE TABLE [dbo].[ActivityBilling](
	[activityBillingId] [int] IDENTITY(1,1) NOT NULL,
	[activityId] [int] NOT NULL,
	[VND_ID] [int] NULL,
	[vendorName] [varchar](500) NULL,
	[isNewVendor] [bit] NULL,
	[activityInviteeId] [int] NULL,
	[activityServiceId] [int] NULL,
	[Comments] [varchar](500) NULL,
	[billRefNo] [varchar](50) NULL,
	[billdate] [datetime] NULL,
	[amount] [float] NULL,
	[billStatusCode] [int] NULL,
	[createOn] [datetime] NULL,
	[createdBy] [int] NULL,
	[modifiedOn] [datetime] NULL,
	[modifiedBy] [int] NULL,
	[RejectionComments] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED 
(
	[activityBillingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ActivityBilling]  WITH CHECK ADD  CONSTRAINT [FK_ActivityBilling_Activity] FOREIGN KEY([activityId])
REFERENCES [dbo].[Activity] ([activityId])
GO

ALTER TABLE [dbo].[ActivityBilling] CHECK CONSTRAINT [FK_ActivityBilling_Activity]
GO

USE [TRIMM_PRD_STG]
GO

create FUNCTION [dbo].[udf_GetChildActivityContractStatus]
(
	@ActivityContractId	int
	
   
)
RETURNS NVARCHAR(100)
AS
BEGIN
	DECLARE @CanAmend bit=1
	declare @Count int
	
	select @Count=Count(1) from Activity a
	join ActivityContracts ac on a.ActivityId=ac.ActivityId
	join Status s on a.statusId=s.statusId
	where ac.taggedContractId=@ActivityContractId and (s.code=202 or s.code=205)
	
	

	if(@Count >0)
	begin
		set @CanAmend=0
	end	
		
	RETURN @CanAmend

END
GO


create FUNCTION [dbo].[udf_GetInviteesConsultantContractStatus] --30
(
	@InviteeId	int   
)
RETURNS BIT
AS
BEGIN
	DECLARE @IsContractExist BIT = 0
	DECLARE @Count INT
	
	SELECT @Count = count('X') from ActivityContracts ac
	JOIN ContractType c ON ac.contractTypeId = c.contractTypeId
	JOIN [Status] cSt ON cSt.statusId = ac.[statusId]
	where inviteeId= @InviteeId and (c.Code = 205 or c.Code= 208) and cst.code = 403 AND cSt.statusIdentifier = 3	

	IF(@Count >0)
	BEGIN
		SET @IsContractExist=1
	END	
		
	RETURN @IsContractExist

END
GO
10

Here the link of the Actual Execution Plan:

Execution Plan

11

Depending on the default collation, REPLACE() can be absolutely horrible for performance. The default default-collation normally has no such issues but others can provide insane slowdowns when REPLACE() is used.

The workaround/fix, especially for this type of "punctuation-only" replace is to add the COLLATE hint with a binary collation to the REPLACE().