CODEWITHSUNDEEP
c#linq
behzad razzaqi
behzad razzaqi

Reputation: 1511

What is best and rapid way for calculate this query?

I'm beginner in c# and linq ,write this query in c#:

var query1 = (from p in behzad.Customer_Care_Database_Analysis_Centers
                         select p).ToArray();
            for (Int64 i = 0; i < query1.Count(); i++)
            {
                var query2 = (from tt in behzad.Customer_Care_Database_Analysis_DETAILs
                              where tt.fileid == FILE_ID && tt.code_markaz ==query1[i].code_markaz //"1215" //query1[i].code_markaz.ToString().Trim() //&& tt.code_markaz.ToString().Trim() == query1[i].code_markaz.ToString().Trim()
                              select new
                              {
                                  tt.id
                              }).ToArray();
                if (query2.Count() > 0)
                {
                    series1.Points.Add(new SeriesPoint(query1[i].name_markaz, new double[] { query2.Count() }));
                    counter += 15;
                }


            }//end for


but up code is very slow,i have about 1000000 Customer_Care_Database_Analysis_Centers and about 20 million record into the Customer_Care_Database_Analysis_DETAILs table,which is best query for up code?thanks.

Upvotes: 1

Views: 80

Answers (1)

Hans Kesting
Hans Kesting

Reputation: 39329

Your current code first gets a lot of records into memory, then executes a new query for each record - where you only use the count of items, even though you again get everything.

I think (untested) that the following will perform better:

var query = from center in behzad.Customer_Care_Database_Analysis_Centers
    join details in behzad.Customer_Care_Database_Analysis_DETAILs
       on center.code_markaz equals details.code_markaz
    where details.fileid == FILE_ID
    where details.Any()
    select new { Name = center.name_markaz, Count = details.Count()};

foreach(var point in query)
{
    series1.Points.Add(new SeriesPoint(point.Name, new double[] { point.Count };
    counter += 15;
}
  • Instead of a lot of queries, execute just one query that will get just the data needed
  • Instead of getting everything into memory first (with ToArray()), loop through it as it arrives - this saves a lot of memory

Upvotes: 1

Related Questions