CODEWITHSUNDEEP
c#entity-framework
Sami
Sami

Reputation: 3976

entity framework query take much time, timeout exception

If i test it Select * from TableName where Id = 257 then i get one record and query execute in some seconds, but when Select * from TableName then it shows thousand of records and it takes more than 3 minutes to execute.

I am using this statement in entity framework and i get timeout exception always:

TableName tbl= db.TableName.Where(o => o.ID == 257).FirstOrDefault();

Does it first selects all records from db and then apply where on it, thats why it is taking much time or there is any other reason?

Please guide me.

Thanks.

Upvotes: 0

Views: 1967

Answers (2)

Bronek
Bronek

Reputation: 11235

  1. Generally as long as you operate against IQueryable collections in your Linq queries, the operation builds SQL statement and does not query a database. It queries a database and finally materialises the collection if you use such actions as: ToList(), FirstOrDefault() or you simply start to iterate over the collection.

    So it is always better to build Linq statement using the IQueryable collections (instead of List). So, in this case when you use Linq extensions methods (fluent pattern mode) the methods like Where always returns IQueryable collection.

  2. If you get time consuming rows' collection and you are not going to submit changes to it (only reading) you have possibility to turn off ChangeTracking function as well as Validation during saving data to a database:

    db.Configuration.AutoDetectChangesEnabled = false; db.Configuration.ValidateOnSaveEnabled = false;

In some situations it brings great improvement of performance.

Upvotes: 1

Anshul Nigam
Anshul Nigam

Reputation: 1628

You can use something like

var query = db.TableName.Where(x => x.ID == 257);
Console.WriteLine(query.ToString());

to see what sql is generated by EF.

When you say 

Select * from TableName then it shows thousand of records and it takes more than 3 minutes     to execute.

This is expected behavior since there is not condition.

but you EF query doesn't reflect the same.

and have where clause. Just a wild guess check if your Table is index on ID you can also try to use 

TableName tbl= db.TableName.Find(257);

while will execute much faster because it can give you result back from its cache.

Update You can also used sql profiling tool called "Entity Framework Profiler"

http://www.hibernatingrhinos.com/products/efprof

its very good tool i had used it personally.

Upvotes: 2

Related Questions