none
Bassa performance per REPLACE infiniti? RRS feed

 > 

  • Domanda

  • Question
    Registrati per votare
    0
    Registrati per votare

    Ciao a tutti,
    devo indagare come una mai una vecchia stored procedure "legacy" mostra performance 
    inaccettabili. 
    E' molto lunga e con parecchi join. 

    Nel codice ho notato questa serie infinita di REPLACE: ,

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

    Potrebbe questo essere una causa di bassa performance? 

    E, soprattutto, come si potrebbe migliorare? 

    Grazie mille a chi potra' darmi uno spunto. 

    Luigi


        

    giovedì 20 ottobre 2022 07:41
    |

Risposte

  • Question
    Registrati per votare
    1
    Registrati per votare

    Nel piano di esecuzione si osservano criticità sulle tabelle di seguito indicate; suggerisco gli indici da creare sulla base del piano di esecuzione.

    Per la dbo.Invitee sembra utile un indice univoco sul campo InviteeID, meglio se PK; dal codice presumo che sia univoco, ma se così non fosse ometti la keywork "UNIQUE".

    Ulteriori ottimizzazioni sono possibili avendo il codice delle funzioni che richiami nelle query.

    1. dbo.FeeGrid create index IX_IsActive on dbo.FeeGrid(IsActive, Start_Date, Expiry_Date) INCLUDE(feeGridId);

    2. dbo.Invitee CREATE UNIQUE CLUSTERED INDEX UCX ON dbo.Invitee(inviteeId)

    3. dbo.ActivityContracts create index IX_taggedContractId on dbo.ActivityContracts(taggedContractId) include(..campi PK..); create index IX_actvityInviteeId on dbo.ActivityContracts(actvityInviteeId) include(..campi PK..);

    4. dbo.ContractGrid create index IX_contractTypeId on dbo.ContractGrid(contractTypeId) include(contractTypeId, ..campi PK..);

    ciao g.

    • Modificato gnic lunedì 24 ottobre 2022 13:41
    • Contrassegnato come risposta Ciupaz lunedì 24 ottobre 2022 15:52
    lunedì 24 ottobre 2022 13:41
    |

Tutte le risposte

  • Question
    Registrati per votare
    0
    Registrati per votare

    Ciao,

    potrebbe ma anche no: cose c'è intorno alla replace (tutta la query)? Il campo title come è definito?

    Giorgio

    giovedì 20 ottobre 2022 15:15
    |
  • Question
    Registrati per votare
    0
    Registrati per votare
    La riporto qua per intero: 



    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;



    giovedì 20 ottobre 2022 17:48
    |
  • Question
    Registrati per votare
    0
    Registrati per votare

    Nella procedura che alleghi non vedo il punto con le replace della tua prima domanda.

    Colgo l'occasione per due note: 

    1. puoi evitare quel continuo ripetere (NOLOCK) mettendo tra le prime righe della stored lo statement SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    2. è sempre molto opportuno indicare lo schema davanti ai nomi degli oggetti (es: tabelle, viste, stored): dbo.V_Vendor_Master

    g.


    venerdì 21 ottobre 2022 08:36
    |
  • Question
    Registrati per votare
    0
    Registrati per votare

    Scusa Giorgio, in realtà la stored problematica è questa, che non ha il REPLACE infinito (era relativo ad un'altra stored). 

    La riporto qua sotto depurata da un po' tutte quelle inutili parti commentate. 

    Il SQL Server di riferimento è il 2008.

    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  --ITS-CHG0255281      
            
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)     
    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
								GROUP BY IIQ.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   --ITS-CHG0255281      
  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
								GROUP BY IIQ.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 --RITM2034218            
  ,(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
    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 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

    venerdì 21 ottobre 2022 09:01
    |
  • Question
    Registrati per votare
    0
    Registrati per votare

    E' difficile esprimermi su quella procedura senza avere alcuna idea di come sono definite le tabelle (colonne, tipo, indici, numero di righe, definizione delle user defined function); posso però suggerirti di verificare che per le tabelle coinvolte nelle query che riporto di seguito esista un indice che abbia come primo campo di sinistra nella chiave il campo indicato:

    1. tabella dbo.Activity, campo activityId
    2. tabella dbo.ActivityDetails, campo activityId
    3. tabella dbo.activityInvitee, campo activityId
    4. tabella dbo.ActivityBilling, campo activityId
    select @ActivityTypeCode=code  
from ActivityType at 
join Activity a  on a.activityTypeId=at.activityTypeId 
where activityId=@ActivityIdTemp  --ITS-CHG0255281      
            
SELECT @activityDate = activityDate from ActivityDetails  where activityId = @ActivityIdTemp        
SELECT @activityDate1 = activityDate from ActivityDetails where activityId = @ActivityIdTemp    


FROM activityInvitee ai
left JOIN Invitee i ON ai.inviteeId = i.inviteeId  AND ai.activityId    = @ActivityIdTemp       


select distinct activityInviteeId 
from dbo.ActivityBilling 
where activityId=@ActivityIdTemp 
and billStatusCode in (602,603)

    Se le tabelle indicate avessero ò già gli indici sui campi suggeriti allora è necessario vedere i piani di esecuzione delle varie query o - se riesci ad individuarle - delle query più lente.

    Ciao

    g.

    domenica 23 ottobre 2022 14:08
    |
  • Question
    Registrati per votare
    0
    Registrati per votare

    Questo e' il piano di esecuzione della stored, casomai potesse servire: 

    https://www.brentozar.com/pastetheplan/?id=HyGHOCQ4j


    • Modificato Ciupaz lunedì 24 ottobre 2022 09:18
    lunedì 24 ottobre 2022 09:18
    |
  • Question
    Registrati per votare
    1
    Registrati per votare

    Nel piano di esecuzione si osservano criticità sulle tabelle di seguito indicate; suggerisco gli indici da creare sulla base del piano di esecuzione.

    Per la dbo.Invitee sembra utile un indice univoco sul campo InviteeID, meglio se PK; dal codice presumo che sia univoco, ma se così non fosse ometti la keywork "UNIQUE".

    Ulteriori ottimizzazioni sono possibili avendo il codice delle funzioni che richiami nelle query.

    1. dbo.FeeGrid create index IX_IsActive on dbo.FeeGrid(IsActive, Start_Date, Expiry_Date) INCLUDE(feeGridId);

    2. dbo.Invitee CREATE UNIQUE CLUSTERED INDEX UCX ON dbo.Invitee(inviteeId)

    3. dbo.ActivityContracts create index IX_taggedContractId on dbo.ActivityContracts(taggedContractId) include(..campi PK..); create index IX_actvityInviteeId on dbo.ActivityContracts(actvityInviteeId) include(..campi PK..);

    4. dbo.ContractGrid create index IX_contractTypeId on dbo.ContractGrid(contractTypeId) include(contractTypeId, ..campi PK..);

    ciao g.

    • Modificato gnic lunedì 24 ottobre 2022 13:41
    • Contrassegnato come risposta Ciupaz lunedì 24 ottobre 2022 15:52
    lunedì 24 ottobre 2022 13:41
    |