When we are doing so, we had experienced some performance degradation. We have checked the DB logs from postgres to troubleshoot the issue and found that the queries generated are of unnecessary nested queries. And also its fetching all the fields from all the tables involved in join queries.
This is my LINQ Query:
Code: Select all
var pmsClims = (
from en in dbACI.Encounters
join pms in dbACI.PMSClaims on en.Id equals pms.EncounterId
join qrda in dbACI.QRDAValueSets on
new { code = pms.BillingCodeValue, codysystem = pms.BillingCodeSystem } equals
new { code = qrda.Code, codysystem = qrda.CodeSystemOId }
where pms.PatientId == patientId
&& en.EncounterDate >= reportingPeriod.StartDate && en.EncounterDate <= reportingPeriod.EndDate
&& qrda.QDMCategory == SysCode.Encounter && pms.IsActive == true && qrda.CMSNumber == CMSID
select new { pms.BillingCodeSystem, pms.BillingCodeValue, en.EncounterDate, pms.PatientId, qrda.ValueSetOId, qrda.ValueSetName }
).Distinct().ToList();
Code: Select all
SELECT
"Distinct1"."C1",
"Distinct1"."BillingCodeSystem",
"Distinct1"."BillingCodeValue",
"Distinct1"."EncounterDate",
"Distinct1"."PatientId",
"Distinct1"."ValueSetOId",
"Distinct1"."ValueSetName"
FROM ( SELECT DISTINCT
"Filter1"."EncounterDate1" AS "EncounterDate",
"Filter1"."PatientId1" AS "PatientId",
"Filter1"."BillingCodeValue",
"Filter1"."BillingCodeSystem",
"Extent3"."ValueSetName",
"Extent3"."ValueSetOId",
1 AS "C1"
FROM (SELECT
"Extent1"."Id" AS "Id1",
"Extent1"."PatientId" AS "PatientId2",
"Extent1"."FacilityId",
"Extent1"."ReasonForVisit",
"Extent1"."ChiefComplaint",
"Extent1"."IsActive" AS "IsActive1",
"Extent1"."ExternalId" AS "ExternalId1",
"Extent1"."ReferenceNumber" AS "ReferenceNumber1",
"Extent1"."ReasonForReferral",
"Extent1"."SignedDate",
"Extent1"."EncounterDate" AS "EncounterDate1",
"Extent1"."TIN",
"Extent1"."FirstParticipantId",
"Extent1"."SecondParticipantId",
"Extent1"."NPI",
"Extent1"."SavedDate",
"Extent1"."ExamType",
"Extent1"."EncounterId" AS "EncounterId1",
"Extent1"."Source" AS "Source1",
"Extent1"."LastModifiedDate",
"Extent1"."EncounterDuration",
"Extent1"."Assessment",
"Extent2"."Id" AS "Id2",
"Extent2"."Source" AS "Source2",
"Extent2"."ReferenceNumber" AS "ReferenceNumber2",
"Extent2"."PatientId" AS "PatientId1",
"Extent2"."EncounterId" AS "EncounterId2",
"Extent2"."EncounterDate" AS "EncounterDate2",
"Extent2"."PhysicianNPI",
"Extent2"."PhysicianTIN",
"Extent2"."BillingCodeValue",
"Extent2"."BillingCodeDescription",
"Extent2"."BillingCodeSystem",
"Extent2"."BillingCodeName",
"Extent2"."ExternalId" AS "ExternalId2",
"Extent2"."IsActive" AS "IsActive2"
FROM "Eli"."Encounter" AS "Extent1"
INNER JOIN "Eli"."PMSClaim" AS "Extent2" ON "Extent1"."Id" = "Extent2"."EncounterId"
WHERE "Extent2"."IsActive" = true ) AS "Filter1"
INNER JOIN "Eli"."QRDAValueSet" AS "Extent3" ON ("Filter1"."BillingCodeValue" = "Extent3"."Code") AND ("Filter1"."BillingCodeSystem" = "Extent3"."CodeSystemOId")
WHERE (((("Filter1"."PatientId1" = (CAST('19' AS int))) AND ("Filter1"."EncounterDate1" >= '2016-01-01 00:00:00')) AND ("Filter1"."EncounterDate1" <= '2018-01-01 00:00:00')) AND ("Extent3"."QDMCategory" = 'Encounter')) AND ("Extent3"."CMSNumber" ='CMS68v6')
) AS "Distinct1"