c# - Using LINQ for quering in a big database take so much time -

c# - Using LINQ for quering in a big database take so much time -

i'm using linq preparing info in controller , sending them view. connection provided ef6 code first migration sql server in controller , linq look , model of database map proper view model follow:

var temp = db.points.tolist().select(pnt => new mappointsmodel() { pointid = pnt.pointid, name = pnt.name, positionx = pnt.positionx, positiony = pnt.positiony, road = pnt.road.id, order = pnt.order, signalstate = pnt.signalstate, powerstate = pnt.powerstate, videostate = pnt.videostate, camerastate = pnt.camerastate, hourtraffic = new int[]{ pnt.crossings.where(c => datetime.compare(c.datetime, lasthour) >= 0 ).where(c => c.line == 1).count(), pnt.crossings.where(c => datetime.compare(c.datetime, lasthour) >= 0 ).where(c => c.line == 2).count(), pnt.crossings.where(c => datetime.compare(c.datetime, lasthour) >= 0 ).where(c => c.line == 3).count() }, daytraffic = new int[]{ pnt.crossings.where(c => datetime.compare(c.datetime, lastday) >= 0 ).where(c => c.line == 1).count(), pnt.crossings.where(c => datetime.compare(c.datetime, lastday) >= 0 ).where(c => c.line == 2).count(), pnt.crossings.where(c => datetime.compare(c.datetime, lastday) >= 0 ).where(c => c.line == 3).count() }, hourviolation = new int[] { pnt.crossings.where(c => c.violation != null && datetime.compare(c.datetime, lasthour) >= 0).where(c => c.line == 1).count(), pnt.crossings.where(c => c.violation != null && datetime.compare(c.datetime, lasthour) >= 0).where(c => c.line == 2).count(), pnt.crossings.where(c => c.violation != null && datetime.compare(c.datetime, lasthour) >= 0).where(c => c.line == 3).count() }, dayviolation = new int[] { pnt.crossings.where(c => c.violation != null && datetime.compare(c.datetime, lastday) >= 0).where(c => c.line == 1).count(), pnt.crossings.where(c => c.violation != null && datetime.compare(c.datetime, lastday) >= 0).where(c => c.line == 2).count(), pnt.crossings.where(c => c.violation != null && datetime.compare(c.datetime, lastday) >= 0).where(c => c.line == 3).count() }, checkedviolations = pnt.crossings.where(c => c.violation != null).where(c => c.violation.deliberated == true).count(), uncheckedviolations = pnt.crossings.where(c => c.violation != null).where(c => c.violation.deliberated == false).count(), bandavgspeed = new int[] { pnt.crossings.where(c => c.line == 1).count() == 0 ? 0 : pnt.crossings.where(c => c.line == 1).sum(c => c.speed)/pnt.crossings.where(c => c.line == 1).count(), pnt.crossings.where(c => c.line == 2).count() == 0 ? 0 : pnt.crossings.where(c => c.line == 2).sum(c => c.speed)/pnt.crossings.where(c => c.line == 2).count(), pnt.crossings.where(c => c.line == 3).count() == 0 ? 0 : pnt.crossings.where(c => c.line == 3).sum(c => c.speed)/pnt.crossings.where(c => c.line == 3).count(), }, }); homecoming temp.tolist();

this code works 10000 records or lower, in 500000 record or more there no result , in tests timeout accurse.

i'm looking reason of problem

update:

there 4 records in "points" table, bigger 1 more 500000 records "crossings" tried solve problem removing .tolist() db.points, rises exception:

system.argumentexception: argument types not match

your problem this:

db.points.tolist()....

this retrieves all records in entirety table memory. so, more records have, longer take. need create query returns records need.

i'm not sure you're planning 500,000 records @ once.. need subset? if so, this:

db.points.select(....).take(25) // or many need.

you're doing numerous sub-selects , sub-counts, each of separate statements executed, 500,000 might have many millions of sub-queries.

c# sql-server asp.net-mvc linq entity-framework