Poor performance query

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Posts: 5
Joined: Mon 08 Jul 2013 16:27

Poor performance query

Post by nicolas.bourgoin » Fri 27 Sep 2013 14:06


I'm having really really poor performance on a couple of queries I run against an oracle database. The query SQL generated by devart is:

Code: Select all

"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
	WHERE ("Extent1".JOB_ID = :p__linq__0) AND ("Extent1".G3E_FNO IN (1300,1400,2200,2500,2600,2700,2900,3000,7000,7100,10200,10300,10400,10500,10600,10800,10900,11300,11400,14700,14900,15500,17200,17300,17400,17500,17700,18000,19300,19400,1600,14800,14600,14400,14100,1200,2000,2400,15100,15600,17600,1100))
	GROUP BY "Extent1".G3E_FID, "Extent1".LTT_ID
)  "GroupBy1"
When I run this query directly using Oracle SQL Developper using the same connection string, I get the answer in 0.018 seconds. It can take up to 1 minute to get using devart dotconnect. The table has ~38 millions rows and maybe the query does a full table scan?
Do you have any suggestion as to how to make this faster?

Code: Select all

var requiredInfosQuery = _elementRepository.Query()
                .Where(x => x.JOB_ID == job.G3E_IDENTIFIER)
                .Where(x => fnoToQuery.Contains(x.G3E_FNO))
                .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)});


            var requiredInfos = requiredInfosQuery.ToList();

Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Poor performance query

Post by Shalex » Fri 27 Sep 2013 15:30

Post Reply