performance issue with dotConnect query creation

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
nicolas.bourgoin
Posts: 5
Joined: Mon 08 Jul 2013 16:27

performance issue with dotConnect query creation

Post by 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?

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

Re: performance issue with dotConnect query creation

Post by Shalex » Mon 12 Aug 2013 14:31

Please set UseDatabaseNullSemantics / UseCSharpNullComparisonBehavior to False explicitly: http://stackoverflow.com/questions/1630 ... -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.

Post Reply