dotConnect to Postgresql : Generating Unnecessary nested queries and feilds

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Sreedhar28
Posts: 1
Joined: Fri 03 Nov 2017 14:21

dotConnect to Postgresql : Generating Unnecessary nested queries and feilds

Post by Sreedhar28 » 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:

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();
Output Query for Postgresql:

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"
 
Looking for help on how to avoid the nested queries.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect to Postgresql : Generating Unnecessary nested queries and feilds

Post by Shalex » Tue 07 Nov 2017 11:04

Thank you for your report. The investigation is in progress. We will contact you as soon as possible.

Post Reply