dotConnect to Postgresql : Generating Unnecessary nested queries and feilds
Posted: Fri 03 Nov 2017 14:36
I am using entity frame work with dotConnect to convert LINQ queries for postgresql.
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:
Output Query for Postgresql:
Looking for help on how to avoid the nested queries.
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"