CODEWITHSUNDEEP
c#sql-serverasp.net-mvclinqentity-framework
Hamed Hamedi
Hamed Hamedi

Reputation: 1546

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

I'm using LINQ for preparing some data in my controller and sending them to view. My connection provided by EF6 code first migration with sql server So in the controller and with a LINQ expression , the model of database map to proper view model as 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(),
            },
        });
        return temp.ToList();

this code works for 10000 records or lower, but in 500000 record or more there is no result and in all tests timeout accurse.

I'm looking for the reason of this problem

UPDATE:

There is just 4 records in "points" table, the bigger one with more than 500000 records is "crossings" however I tried to solve the problem with removing .toList() from db.points, but it rises exception:

System.ArgumentException: Argument types do not match

Upvotes: 0

Views: 1332

Answers (5)

Hamed Hamedi
Hamed Hamedi

Reputation: 1546

The main problem was Average !!!

Because average function runs 4 times over the all records, it takes about 7 seconds.

I changed the linq query and instead of requesting all data at once , the average section calculated in other request from server with OrderBy GroupBy tools. and then combined two lists that retrieve from server.

Beside, I removed .ToList() from points table (as folks suggested) and changed arrays constructions to simple variables (this caused error!).

This solution caused runtime take just about 1 second instead of 7 second with the same output :)

Upvotes: 0

neo x
neo x

Reputation: 51

You need to change the ToList() from the code and change var temp = db.points.AsNoTracking() This will save your time .Also creating pre-generated views for your code first model gives better performance.

Upvotes: 0

Fredou
Fredou

Reputation: 20140

doing this

    var temp = db.points.ToList()....

bring the whole table to the client, so if you have a few meg / gig of data this can take a while depending on cpu / connection / memory

Upvotes: 0

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93474

Your problem is this:

db.points.ToList()....

This retrieves ALL records in their entirety from the table into memory. So, the more records you have, the longer this will take. You need to create a query that returns only the records you need.

I'm not sure what you're planning to do with 500,000 records all at once.. do you only need a subset? If so, then do something like this:

db.points.Select(....).Take(25) // or however many you need.

You're also doing numerous sub-selects and sub-counts, each of those are separate statements that get executed, so for those 500,000 you might actually have many millions of sub-queries.

Upvotes: 2

Praveen Paulose
Praveen Paulose

Reputation: 5771

You are transforming it to a List at the beginning. This is taking up all the time. Try removing the ToList() from db.points.ToList().Select. This should solve your problem.

Until you convert it using a ToList, it still is a IQueryable and will not hit the database. The moment you do a ToList, it will query the database, fetch the results in memory and then process the records in memory.

Upvotes: 0

Related Questions