1
\$\begingroup\$

This inserts into 3 tables really quickly. My SELECT at the bottom appears to take an age, I assumed it would be fairly quick but is not. Is the XML path the right way to go?

DECLARE @TEAMMEMBER TABLE
        (CAREPROVIDEROID BIGINT, Team VARCHAR(MAX))
DECLARE @MAINSPECIALTY TABLE
        (CAREPROVIDEROID BIGINT, MainSpecialty VARCHAR(MAX))
DECLARE @TREATSPECIALTY TABLE
        (CAREPROVIDEROID BIGINT, TreatSpecialty VARCHAR(MAX))


INSERT INTO @TEAMMEMBER 
    SELECT 
    TEAMMEM.CAREPROVIDEROID as CAREPROVIDEROID,
    CAST(REPLACE(TEAM.NAME,'&','AND')+'('+TEAM.IDENTIFIER+')' AS NVARCHAR(MAX)) as Team
    FROM LZO_TEAMMEMBER TEAMMEM
    INNER JOIN LZO_TEAM TEAM
    ON TEAM.OID = TEAMMEM.TEAMOID
    AND TEAM.STATUS = 'A'
    --ORDER BY TEAMMEM.CAREPROVIDEROID


INSERT INTO @MAINSPECIALTY
    SELECT USERRP.USERSOID as USERSOIDMAINSPECIALTY,
    CAST(s.NAME +'('+s.MAINIDENTIFIER+')' AS NVARCHAR(MAX)) as MainSpec
    FROM LZO_USERROLEPROFILE USERRP

    INNER JOIN LZO_ROLEPROFILEENTERPRISEOBJECT RPEO
    ON USERRP.ROLEPROFILEOID = RPEO.ROLEPROFILEOID
    AND 'SPECIALTY' = RPEO.IDENTIFYINGTYPE 
    AND RPEO.STATUS = 'A'
    INNER JOIN LZO_Specialty s
    ON s.OID = RPEO.IdentifyingOID and s.SPETYCode = 'CC_CPSPE' --Specialty
    AND RPEO.STATUS = 'A'
    WHERE 1=1
    AND USERRP.STATUS='A'
    --ORDER BY USERRP.USERSOID

INSERT INTO @TREATSPECIALTY
    SELECT
    USERRP.USERSOID as USERSOIDTREATSPECIALTY,
    CAST(s.NAME +'('+s.MAINIDENTIFIER+')' AS NVARCHAR(MAX)) as TreatSpec
    FROM LZO_USERROLEPROFILE USERRP
    INNER JOIN LZO_ROLEPROFILEENTERPRISEOBJECT RPEO
    ON USERRP.ROLEPROFILEOID = RPEO.ROLEPROFILEOID
    AND 'SPECIALTY' = RPEO.IDENTIFYINGTYPE 
    AND RPEO.STATUS = 'A'
    INNER JOIN LZO_Specialty s
    ON s.OID = RPEO.IdentifyingOID and s.SPETYCode = 'CC_TRTFN' --Treatment Function
    AND RPEO.STATUS = 'A'
    WHERE 1=1
    AND USERRP.STATUS='A'
    --ORDER BY USERRP.USERSOID




Select OID, 
       (SELECT sub.Team + ', ' AS [data()] 
            from @TEAMMEMBER as sub
            Where sub.CAREPROVIDEROID = LZO_USERS.OID
            Order by sub.Team asc
                  FOR XML PATH('')                
        ) as [Teams]

FROM LZO_USERS WHERE STATUS = 'A'
\$\endgroup\$
1
\$\begingroup\$

Removing the ORDER BY worked wonders and slightly altering the code:

,SUBSTRING(STUFF((SELECT ', '+TEAM FROM @TEAMMEMBER WHERE E.OID = CAREPROVIDEROID FOR XML PATH('')),1,1,''),2,4000) AS TEAMS
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.