table valued function xml reader high cost

link textAll,

this table valued function xml reader cost is very high.can u please give your valuable suggestions to reduce this xml reader cost.

Please find the actual execution plan.

SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT siteCache.cachenumber AS sitecachenumber, latestCache.cachelastupdatedttm AS latestcachelastupdatedttm, siteLocation.locationId AS sitelocationid, latestfacility.facilityid AS latestfacilityid, sitecache.cachenumber AS cachenumber, sitelocation.name AS sitelocationname, latestcacheprevlocation.name AS latestcacheprevlocationname, latestsite.name AS latestsitename, latestfacility.name AS latestfacilityname, latestlocation.code AS latestlocationcode, sitelocation.code AS sitelocationcode, latestcache.sitedeparteddttm AS latestsitedeparteddttm, latestfacility.code AS latestfacilitycode, latestsite.code AS latestsitecode, sitecache.sitedeparteddttm AS sitedeparteddttm, latestcacheprevlocation.code AS latestcacheprevlocationcode, latestsite.siteid AS latestsiteid, cdr.roomname AS cdrbed, cdr.wardname AS cdrward, latestlocation.name AS latestlocationname, patient.surname AS patientsurname, patient.gender AS patientgender, externalpatientid.externalpatientid AS externalpatientid, patient.dateofbirth AS patientdateofbirth, patient.firstgivenname AS patientfirstgivenname, diet.othercomment AS othercomment, diet.diet AS diet, diet.taskstatus AS taskstatus, sitecache.dietstatus AS dietstatus, diet.pref AS pref, problem.foodallergy AS foodallergy, problem.description AS description, cdr.edd AS edd, cdr.admitstatus AS admitstatus
FROM Location siteLocation LEFT OUTER JOIN Cache siteCache ON siteCache.locationId = siteLocation.locationId LEFT OUTER JOIN Cache latestCache ON latestCache.patientId = sitecache.patientId LEFT OUTER JOIN Patient patient ON latestCache.patientid = patient.patientId LEFT OUTER JOIN Area siteArea ON siteLocation.areaId = siteArea.areaId LEFT OUTER JOIN ExternalPatientId externalPatientId ON latestCache.patientId = externalPatientId.patientId LEFT OUTER JOIN Nurse assignedNurse ON siteCache.assignedNurseId = assignedNurse.nurseId LEFT OUTER JOIN Nurse seenByNurse ON siteCache.seenByNurseId = seenByNurse.nurseId LEFT OUTER JOIN Doctor assignedDoctor ON siteCache.assignedDoctorId = assignedDoctor.doctorId LEFT OUTER JOIN Doctor seenByDoctor on siteCache.seenByDoctorId = seenByDoctor.doctorId LEFT OUTER JOIN Consultant consultant on siteCache.assignedConsultantId = consultant.consultantId LEFT OUTER JOIN Specialty specialty on siteCache.specialtyId = specialty.specialtyId LEFT OUTER JOIN TreatmentProtocol treatmentProtocol ON siteCache.treatmentProtocolId = treatmentProtocol.treatmentProtocolId LEFT OUTER JOIN Location latestLocation on latestCache.locationId = latestLocation.locationId LEFT OUTER JOIN Area latestArea ON latestLocation.areaId = latestArea.areaId LEFT OUTER JOIN Site latestSite ON siteLocation.siteId = latestSite.siteId LEFT OUTER JOIN Facility latestFacility ON latestFacility.facilityId = latestSite.facilityId LEFT OUTER JOIN Result ON siteCache.encounterNumber = result.encounterNumber AND siteCache.patientId = result.patientId LEFT OUTER JOIN CacheTableHistory latestCacheHistory ON latestCache.cacheNumber = latestCacheHistory.cacheNumber LEFT OUTER JOIN Location latestCachePrevLocation ON latestCachePrevLocation.locationId = latestCacheHistory.locationId LEFT OUTER JOIN CacheTableHistory siteCacheHistory ON siteCache.cacheNumber = siteCacheHistory.cacheNumber LEFT OUTER JOIN Location siteCachePrevLocation ON siteCachePrevLocation.locationId = siteCacheHistory.locationId

LEFT OUTER JOIN (SELECT DISTINCT pp2.patient_id, CAST(STUFF((SELECT ',' + cd.description FROM [OTEST_OHPRIST].[problemlist].[problems_v] pp INNER JOIN [OTEST_OHCAL].[CodeSet].[CodeSet] CS ON cs.identifier = pp.problem_name_code_set INNER JOIN OTEST_OHCAL.codeset.CodeDefinition cd ON ( cd.code = pp.problem_name_code AND cd.codesetid = cs.id ) WHERE patient_id = pp2.patient_id AND pp.last_update_action NOT IN ('DELETE','CLOSE') AND pp.adr_class_code IN (3,4) AND cd.description != 'Other' ORDER BY [description] FOR XML PATH ('')),1,1,'')as varchar(255)) As foodAllergy, CAST((STUFF((SELECT ',' + ' Other:' + pp.problem_name_freetext_desc FROM [OTEST_OHPRIST].[problemlist].[problems_v] pp INNER JOIN [OTEST_OHCAL].[CodeSet].[CodeSet] CS on cs.identifier = pp.problem_name_code_set INNER JOIN OTEST_OHCAL.codeset.CodeDefinition cd on ( cd.code = pp.problem_name_code AND cd.codesetid = cs.id ) WHERE patient_id = pp2.patient_id AND pp.last_update_action not in ('DELETE','CLOSE') AND pp.adr_class_code in (3,4)
FOR XML PATH('')), 1, 1, '') ) as varchar(255)) as [description] FROM [OTEST_OHPRIST].[problemlist].[problems_v] pp2) AS problem ON problem.patient_id = externalpatientid.externalpatientid

LEFT OUTER JOIN (SELECT DISTINCT id.identifier, encs.code as admitStatus, dateadd(minute,enc.admitDTUTC0OffsetMin,enc.admitDTUTC0) as expAdmit, dateadd(minute,enc.expDischargeDTUTC0OffsetMin,enc.expDischargeDTUTC0) as EDD, enc.externalIdentifier, CASE WHEN LEN(LTRIM(RTRIM(isnull(room.description,'')))) = 3 THEN room.description / OF-62 Map some bed codes to respective display values / WHEN (room.description like 'HN3%') THEN CASE WHEN RIGHT(room.description, LEN(room.description) -4) = 'IC1' THEN '01' WHEN RIGHT(room.description, LEN(room.description) -4) = 'IC2' THEN '02' WHEN RIGHT(room.description, LEN(room.description) -4) = 'IC3' THEN '03' WHEN RIGHT(room.description, LEN(room.description) -4) = 'IC4' THEN '04' WHEN RIGHT(room.description, LEN(room.description) -4) = 'IC5' THEN '05' WHEN RIGHT(room.description, LEN(room.description) -4) = 'IC6' THEN '06' END WHEN (room.description like 'NP1-D%') THEN CASE WHEN RIGHT(room.description, LEN(room.description) -4) = 'D01' THEN '01' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D02' THEN '02' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D03' THEN '03' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D04' THEN '04' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D05' THEN '05' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D06' THEN '06' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D07' THEN '07' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D08' THEN '08' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D09' THEN '09' WHEN RIGHT(room.description, LEN(room.description) -4) = 'D10' THEN '10' END WHEN (room.description like 'WST-HD%') THEN CASE WHEN RIGHT(room.description, LEN(room.description) -4) = 'HD1' THEN '01' WHEN RIGHT(room.description, LEN(room.description) -4) = 'HD2' THEN '02' END ELSE RIGHT(room.description, LEN(room.description) -4) END AS roomName, ward.description as wardName FROM OTEST_OHCAL.Encounter.PatientEncounter enc INNER JOIN OTEST_OHCAL.Identifier.Identifier id on id.id = enc.patientIdentId INNER JOIN OTEST_OHCAL.Encounter.EncounterState encs on encs.id = enc.encounterStateId LEFT OUTER JOIN OTEST_OHCAL.Encounter.PATIENTENCOUNTERLOCCODE room on room.id = enc.bedspaceId LEFT OUTER JOIN OTEST_OHCAL.Encounter.PATIENTENCOUNTERLOCCODE ward on ward.id = enc.wardId) CDR on CDR.externalIdentifier = siteCache.encounternumber

LEFT OUTER JOIN (SELECT DISTINCT fm.v2_FIR#Diet#Form01_ODAZA, dm.patientid, dm.documentid, dm.documentModelID, CAST(fm.DietPreferences_UDDFJ as varchar(255)) as Pref, CAST((SELECT 'Other:' + fm2.CommentDiet_Y3M4L FROM [OHCP_APP_OHcRepository].[dbo].[v2_FIRDF0_ODAZA_Diet_JKQ7P] d LEFT OUTER JOIN OHCP_APP_OHcRepository.dbo.v2_FIR#Diet#Form01_ODAZA fm2 on fm2.v2_FIR#Diet#Form01_ODAZA = d.v2_FIR#Diet#Form01_ODAZA AND d.[desc] = 'Other' WHERE d.v2_FIR#Diet#Form01_ODAZA = fm.v2_FIR#Diet#Form01_ODAZA ORDER BY [desc] FOR XML PATH ('')) as varchar(255)) as OtherComment, CAST(STUFF((SELECT ',' + [desc] FROM [OHCP_APP_OHcRepository].[dbo].[v2_FIRDF0_ODAZA_Diet_JKQ7P] d WHERE d.v2_FIR#Diet#Form01_ODAZA = fm.v2_FIR#Diet#Form01_ODAZA and d.[desc] != 'Other' ORDER BY [desc] FOR XML PATH ('')),1,1,'') as varchar(255)) as diet, CASE WHEN dm.taskStatus in (2,5) THEN 'Cancelled' ELSE 'Current' END AS taskStatus FROM OHCP_Custom.dbo.vw_docMetaData dm INNER JOIN OHCP_APP_OHcRepository.dbo.v2_FIR#Diet#Form01_ODAZA fm on fm.DOCUMENTID = dm.documentId WHERE fm.v2_FIR#Diet#Form01_ODAZA = (SELECT MAX(d2.v2_FIR#Diet#Form01_ODAZA) FROM OHCP_APP_OHcRepository.dbo.v2_FIR#Diet#Form01_ODAZA d2 INNER JOIN OHCP_Custom.dbo.vw_docMetaData dm2 ON d2.DOCUMENTID = dm2.documentId AND dm2.isDeleted = 0 AND dm2.documentStatus = 'FINAL' WHERE dm2.patientid = dm.patientid)) as Diet ON Diet.patientid = externalpatientid.externalpatientid

WHERE latestfacility.facilityid = (select facilityid from Site where siteId = 7 ) AND ((siteCache.cacheNumber IS NULL AND siteLocation.unallocatedLocation = 'N') OR (externalPatientId.primaryId = 'Y' AND latestCache.registrationDttm = (SELECT MAX(registrationDttm) FROM Cache c WHERE c.patientId = siteCache.patientId AND siteCache.siteDepartedDttm IS NULL AND siteCache.arrivedDttm IS NOT NULL AND cdr.admitStatus = 'admitted'))) ORDER BY latestSite.name asc,
siteLocation.name asc,
CDR.wardName asc,
CDR.roomName asc

avatar image By Charlesp 1 asked Feb 01 at 12:44 AM
more ▼
(comments are locked)
avatar image Matak Feb 01 at 11:12 PM

I think you forgot to attach the plan

10|10000 characters needed characters left

1 answer: sort voted first

alt text

exeution-plan.jpg (256.1 kB)
avatar image By Charlesp 1 answered Feb 02 at 12:01 AM
more ▼
(comments are locked)
avatar image Matak Feb 02 at 12:03 AM

Please attach it as a .sqlplan. Adding a jpeg doesnt allow us to see any details.

avatar image Charlesp Feb 02 at 12:20 AM

attached now

avatar image Matak Feb 02 at 12:40 AM

Your statistics are way out. Can you update them ? Your plan timed out so its most likely not going to be a great one. First thing i would do is remove the xml components and make sure you get a plan that doesnt time out and tune that first.

avatar image Charlesp Feb 02 at 12:46 AM

Thanks Matak, statistics are uptodate. if i comment the two below joins which has XML components and the script is running fast. kindly help me to remove the xml components or any other alertnative.

-------1--------

--LEFT OUTER JOIN --(SELECT DISTINCT pp2.patient_id, -- CAST(STUFF((SELECT ',' + cd.description -- FROM [OHCP_OHProblemList].[problemlist].[problems_v] pp -- INNER JOIN [OHCP_OHCLINICAL].[CodeSet].[CodeSet] CS ON cs.identifier = pp.problem_name_code_set -- INNER JOIN ohcp_ohclinical.codeset.CodeDefinition cd ON ( cd.code = pp.problem_name_code AND cd.codesetid = cs.id ) -- WHERE patient_id = pp2.patient_id -- AND pp.last_update_action NOT IN ('DELETE','CLOSE') -- AND pp.adr_class_code IN (3,4) -- AND cd.description != 'Other' -- ORDER BY [description] FOR XML PATH ('')),1,1,'')as varchar(255)) As foodAllergy, -- CAST((STUFF((SELECT ',' + ' Other:' + pp.problem_name_freetext_desc -- FROM [OHCP_OHProblemList].[problemlist].[problems_v] pp -- INNER JOIN [OHCP_OHCLINICAL].[CodeSet].[CodeSet] CS on cs.identifier = pp.problem_name_code_set -- INNER JOIN ohcp_ohclinical.codeset.CodeDefinition cd on ( cd.code = pp.problem_name_code AND cd.codesetid = cs.id ) -- WHERE patient_id = pp2.patient_id -- AND pp.last_update_action not in ('DELETE','CLOSE') -- AND pp.adr_class_code in (3,4)
-- FOR XML PATH('')), 1, 1, '') ) as varchar(255)) as [description] -- FROM [OHCP_OHProblemList].[problemlist].[problems_v] pp2) AS problem ON problem.patient_id = externalpatientid.externalpatientid

-------2--------

  • LEFT OUTER JOIN
    • (SELECT DISTINCT
      •  fm.v2_FIR#Diet#Form01_ODAZA, 
         --    dm.patientid, dm.documentid, 
         --    dm.documentModelID,
         --    CAST(fm.DietPreferences_UDDFJ as varchar(255)) as Pref,
         --    CAST((SELECT 'Other:' + fm2.CommentDiet_Y3M4L 
         --          FROM [OHCP_APP_OHcRepository].[dbo].[v2_FIRDF0_ODAZA_Diet_JKQ7P] d 
         --          LEFT OUTER JOIN OHCP_APP_OHcRepository.dbo.v2_FIR#Diet#Form01_ODAZA fm2 on fm2.v2_FIR#Diet#Form01_ODAZA = d.v2_FIR#Diet#Form01_ODAZA 
         --          AND d.[desc] = 'Other'
         --          WHERE d.v2_FIR#Diet#Form01_ODAZA = fm.v2_FIR#Diet#Form01_ODAZA 
         --          ORDER BY [desc] FOR XML PATH ('')) as varchar(255)) as OtherComment,
         --    CAST(STUFF((SELECT ',' + [desc] 
         --                FROM [OHCP_APP_OHcRepository].[dbo].[v2_FIRDF0_ODAZA_Diet_JKQ7P] d 
         --                WHERE d.v2_FIR#Diet#Form01_ODAZA = fm.v2_FIR#Diet#Form01_ODAZA 
         --                and d.[desc] != 'Other' 
         --                ORDER BY [desc] FOR XML PATH ('')),1,1,'') as varchar(255)) as diet,
         --    CASE WHEN dm.taskStatus in (2,5) THEN 'Cancelled' ELSE 'Current' END AS taskStatus
         --FROM OHCP_Custom.dbo.vw_docMetaData dm INNER JOIN OHCP_APP_OHcRepository.dbo.v2_FIR#Diet#Form01_ODAZA fm on fm.DOCUMENTID = dm.documentId
         --WHERE fm.v2_FIR#Diet#Form01_ODAZA = (SELECT MAX(d2.v2_FIR#Diet#Form01_ODAZA) 
         --                                     FROM OHCP_APP_OHcRepository.dbo.v2_FIR#Diet#Form01_ODAZA d2 INNER JOIN OHCP_Custom.dbo.vw_docMetaData dm2 
         --                                     ON d2.DOCUMENTID = dm2.documentId AND dm2.isDeleted = 0 AND dm2.documentStatus = 'FINAL'
         --                                     WHERE dm2.patientid = dm.patientid)) as Diet ON Diet.patientid = externalpatientid.externalpatientid 
        
        
        

Thank you in advance.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x2

asked: Feb 01 at 12:44 AM

Seen: 140 times

Last Updated: Feb 02 at 12:46 AM

Related Questions