CODEWITHSUNDEEP
c#mysqlentity-framework
Zholen
Zholen

Reputation: 1790

Consistently slow EF query

I have the following lookup:

using (var ctx = new dbContext())
{
   if (page == 0) page = 1;
   //_query = "SELECT * FROM [Log] WHERE [ApplicationId] = {0} and  [LogTime] >= '{1}' ORDER BY [LogTime] DESC OFFSET (({2} - 1) * {3}) ROWS FETCH NEXT {3} ROWS ONLY"
   var logs = ctx.Logs.SqlQuery(string.Format(_query, id, time, 1, 20000)).ToList();

   //var logs = ctx.Logs.Where(x => x.ApplicationId == id && x.LogTime >= time).ToList();

   return logs;
}

Given the correct time this query returns around 20000 entries. It takes 13-18 seconds to perform the query, yet inside SQLMS its completed instantly.

The table looks like:

Log
  LogId (PK,int, not null)
  ApplicationId (FK,int,not null)
  Type(nvarchar(50), not null)
  Code(nvarchar(50), null)
  Source(nvarchar(50), not null)
  Message(ntext, null)
  LogTime(datetime, not null)

I dont expect this should take that long. Is there something I am missing or should change to improve my performance?

Upvotes: 0

Views: 52

Answers (2)

Anders Abel
Anders Abel

Reputation: 69260

When performing a query through EF, there are several steps involved:

  1. Query the DB
  2. Transfer the response to the querying process.
  3. Materialize the returned data into .NET objects.
  4. Add the objects to the change tracker.

The reason it is slow can be in any of those steps. The change tracker can be disabled by inserting AsNoTracking() before the ToList() call.

Upvotes: 1

Praveen Paulose
Praveen Paulose

Reputation: 5771

20000 entries is a substantial number of records to return in a single query. When you run it in SQL Management Studio, the query returns instantly. There is no additional overhead when running it within SQL Management Studio. It just returns the results on the screen.

When you run it as an EF query, there are network overheads to fetch the results. One of the key reasons, it would slow down is because it needs to convert the results fetched into objects of type Log. This would take up most of the time you mentioned (13-18 seconds) of the operation.

To summarize, your EF query consists of multiple steps. 1. Query against the database 2. Convert the results to objects (This would take time for 20000 records)

Upvotes: 1

Related Questions