Using Linq to Entity for searching large data cause high memory usage

Question

I developed a ASP.NET MVC Web Application and using Linq to Entity
And I got a trouble with memory usage

Here is my code:

//Search for about 0.6 million transaction data
var presQuery=(from A in db.TransDetail
               where A.TRAN_DATE.CompareTo("2015/02/01")>=0 && A.TRAN_DATE.CompareTo("2015/02/28")<=0
               group A by A.MBR_ID into grp
               select new {
                MBR_ID=grp.Key,
                Price=grp.Sum(s=>s.Price)}).ToList();

//Search for about 0.6 million transaction data
var preVQuery=(from A in db.TransDetail
               where A.TRAN_DATE.CompareTo("2015/01/01")>=0 && A.TRAN_DATE.CompareTo("2015/01/31")<=0
               group A by A.MBR_ID into grp
               select new {
                MBR_ID=grp.Key,
                Price=grp.Sum(s=>s.Price)}).ToList();

int[] rankValue=new int(){2000,1000,500,250,100};
TransDetailViewModel model;
List<TransDetailViewModel> ls=new List<TransDetailViewModel>();
double CountRatio;
int TotalPrice;
int TotalCnt;




//start calculate 
for(int i=0;i<5;i++){
    for(int j=0;j<5;j++){
    if (i < 5  && j < 5)
    {
        var query1 = presQuery.Where(w => w.Price >= rankValue[i]);
        var query2 = prevQuery.Where(w => w.Price >= rankValue[j]);
        var query3 = from q1 in query1
                     join q2 in query2 on q1.MBR_ID equals q2.MBR_ID
                     select q1;
        TotalCnt = query3.Count();
        TotalPrice = Convert.ToInt32(query3.Sum(s => s.Price));
        CountRatio = (Convert.ToDouble(query1.Count()) / Convert.ToDouble(query2.Count())) * 100;
        model = new TransDetailViewModel()
        {
            RankLevel = "R" + i.ToString() + j.ToString(),
            CountRatio = CountRatio,
            TotalCount = TotalCnt,
            TotalPrice = TotalPrice
        };
        ls.Add(model);
        }      

    }   
}

the above code will cause the issue about application memory usage increase to 1G when there are four people starting search at the same time.
Is there a more efficient way to solve?

Try to do using `stored proceudre`. – Kaushik Maheta Nov 23 '15 at 09:50 — Kaushik Maheta, Nov 23 '15 at 09:50
Paged your data. use LINQ Take() and Skip(). – MichaelMao Nov 23 '15 at 09:57 — MichaelMao, Nov 23 '15 at 09:57
is there any diff between preVQuery & preQuery – Arijit Mukherjee Nov 23 '15 at 10:16 — Arijit Mukherjee, Nov 23 '15 at 10:16
you can use a ExecutedQuery as well – Arijit Mukherjee Nov 23 '15 at 10:16 — Arijit Mukherjee, Nov 23 '15 at 10:16
I corrected the presQuery and prevQuery – 吳郁賢 Nov 23 '15 at 11:54 — 吳郁賢, Nov 23 '15 at 11:54

score 2 · Answer 1 · edited May 23 '17 at 11:59

When using linq there's a concept called materialization, some commands like "ToList" will cause your query to "materialize", which means, data will be fetched from the database into the memory, now take a look at this code

//Search for about 0.6 million transaction data
var presQuery=(from A in db.TransDetail
               where A.TRAN_DATE.CompareTo("2015/02/01")>=0 && A.TRAN_DATE.CompareTo("2015/02/28")<=0
               group A by A.MBR_ID into grp
               select new {
                MBR_ID=grp.Key,
                Price=grp.Sum(s=>s.Price)}).ToList();

"ToList" materializes the query, so if this query actually loads 0.6 million rows... You just blew up your memory here (I'm not even mentioning that the second query is the same, so you load the same data twice), so just removing the "ToList" from the end of the query will solve the memory issue up until this part of the code.

Now, for the second part, you start with a double for loop. When you see a for loop mixed with query code, it's a strong smell that you are missing some clever use of a command like Select, SelectMany, Join or GroupBy. You should never need loops for querying.

Those loops are going to be a problem because now that we removed "ToList" and our queries are not materialized, calling "Count()" inside the for loop will cause the code to hit the database several times and that's a funny problem because the time it takes to run this code is gonna be approximately the ping from the server to the database multiplied by the number of iterations in your for loop so if your database is local, it runs fast, but when you deploy it to production, it's slow! We need to get rid of the for loop. (I'm out of time so I'm not gonna do this right now)

Lastly, if your final result has a lot of rows, no changes in the process of getting the data will make your app use less memory, the only remedy for this is to make your final result smaller by paging your data with skip and take

One last comment about using a stored procedure, procs are good because they guarantee that the database will only be hit a single time, it runs on the database which is designed to perform this type of computation, and above all benefits (imho) you get more control of your queries, linq to entities/sql can generate very weird and sometimes ineffective sql when doing complicated queries

Using Linq to Entity for searching large data cause high memory usage

1 Answers1