table valued function xml reader high cost

Charlesp 2017-02-01 00:44:18

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

Matak 2017-02-01 23:12:56
I think you forgot to attach the plan
Charlesp 2017-02-02 00:01:31
alt text
Matak 2017-02-02 00:03:20
Please attach it as a .sqlplan.
Adding a jpeg doesnt allow us to see any details.
Charlesp 2017-02-02 00:20:30
attached now
Matak 2017-02-02 00:40:07
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.
Charlesp 2017-02-02 00:46:11
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.