performance issue with dotConnect query creation

performance issue with dotConnect query creation

Postby nicolas.bourgoin » Thu 08 Aug 2013 15:14

I am having performance issues because of the way dotConnect generates a simple query:

Code: Select all
_elementRepository.Query()
                .Where(x => x.JOB_ID == job.G3E_IDENTIFIER)
                .GroupBy(x => new { x.G3E_FID, x.LTT_ID })
                .Select(x => new { Fid = x.Key.G3E_FID, LttId = x.Key.LTT_ID, MaxLttTid = x.Max(j => j.LTT_TID)});


TRANSLATES TO:

Code: Select all
SELECT
"GroupBy1".K1 AS G3E_FID,
"GroupBy1".K2 AS LTT_ID,
"GroupBy1".A1 AS C1
FROM ( SELECT "Extent1".G3E_FID AS K1, "Extent1".LTT_ID AS K2, Max("Extent1".LTT_TID) AS A1
   FROM GCOMM."B$GC_NETELEM" "Extent1"
   WHERE (("Extent1".JOB_ID = :p__linq__0) AND ( NOT (("Extent1".JOB_ID IS NULL) OR (:p__linq__0 IS NULL)))) OR (("Extent1".JOB_ID IS NULL) AND (:p__linq__0 IS NULL))
   GROUP BY "Extent1".G3E_FID, "Extent1".LTT_ID
)  "GroupBy1"

/*
String p__linq__0 = "391758"
*/


I would like the query to simply be:

Code: Select all
SELECT
"GroupBy1".K1 AS G3E_FID,
"GroupBy1".K2 AS LTT_ID,
"GroupBy1".A1 AS C1
FROM ( SELECT "Extent1".G3E_FID AS K1, "Extent1".LTT_ID AS K2, Max("Extent1".LTT_TID) AS A1
   FROM GCOMM."B$GC_NETELEM" "Extent1"
   WHERE "Extent1".JOB_ID = :p__linq__0
   GROUP BY "Extent1".G3E_FID, "Extent1".LTT_ID
)  "GroupBy1"

/*
String p__linq__0 = "391758"
*/


The portion that makes the query run really slow is:

Code: Select all
OR (("Extent1".JOB_ID IS NULL) AND ('391758' IS NULL))


Is this avoidable?
nicolas.bourgoin
 
Posts: 5
Joined: Mon 08 Jul 2013 16:27

Re: performance issue with dotConnect query creation

Postby Shalex » Mon 12 Aug 2013 14:31

Please set UseDatabaseNullSemantics / UseCSharpNullComparisonBehavior to False explicitly: http://stackoverflow.com/questions/16309866/call-is-failing-with-null-parameter. If this doesn't help, send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle