CODEWITHSUNDEEP
c#linqentity-framework
newuser205
newuser205

Reputation: 55

Entity framework Linq very slow queries

i need some help optimizing linq queries in c#. I have a little application, it uses entity framework, and inside is some code like:

using (var db = new MyEntities())
{
  System.Diagnostics.Debug.WriteLine("Start: " + DateTime.Now.ToString());
  var myData = from table1 in db.Table1
  join table2 in db.Table2 on table1.Key equals table2.Key
  // more joins from various tables
  where table1.Date > StartDate && table1.Date < EndDate
  select table1;
  System.Diagnostics.Debug.WriteLine("After myData: " + DateTime.Now.ToString());

  var count = myData.Count();
  System.Diagnostics.Debug.WriteLine("After Count(): " + DateTime.Now.ToString());
  var list = myData.ToList();
  foreach(var variable in list)
  {
    //do something;
  }
}
return

In these tables, there is about 200.000 rows. The thing is, even if Count() returns 0, Count() takes about 10 seconds, and if there are a few thousand rows, then it takes very long time(at least 10 minutes) and then i get error, that variable is null. Is there some kind of techniques or something that would help me to optimize linq queries ?

Upvotes: 1

Views: 936

Answers (1)

ManOVision
ManOVision

Reputation: 1893

I would bet that it's not an EF or LINQ issue here. Your database still needs to be constructed with proper indexing. If you set a breakpoint and check your intellitrace window you will be able to see your actual query being executed. Copy that query into SQL Server Management Studio and see how the query performs. This can give you hints on how to setup indexes to optimize this query. Any time you join, you open the possibility for slower executing queries. Any WHERE clause that doesn't match your indexes will open the possibility for slower executing queries.

Note: There are thousands of books about the proper way to index your database so please use care when doing this.

Note 2: Rather than DateTime.Now for your timing, use the Stopwatch class. You'll have more consistent results.

var sw = Stopwatch.StartNew();
//do code
sw.Stop();
Debug.WriteLine(sw.Elapsed);

Upvotes: 1

Related Questions