Poor performance query
Posted: Fri 27 Sep 2013 14:06
Hi,
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:
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?
Thanks,
Nick
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
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 ("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"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)});
Debug.Write(requiredInfosQuery.ToString());
var requiredInfos = requiredInfosQuery.ToList();Nick