0

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.

Sami
  • 3,956
  • 9
  • 37
  • 52
  • 2
    Check generated SQL and see what's being sent to database. – MarcinJuraszek May 12 '14 at 08:08
  • I checked in profiler but there no query shown related to this table, may be its because i get error on this line. – Sami May 12 '14 at 08:10
  • If its not executing then how are you managed to see the data. It appears that you are not profiling it correctly. If the query is executing then you should be able to profile using SQL Profiler (assuming you are using SQL server) or EF logging http://msdn.microsoft.com/en-us/library/dn469464.aspx – activebiz May 12 '14 at 08:16
  • Is it while debugging or also without debugging? Is your connection string correct? – L-Four May 12 '14 at 08:19
  • connection string is correct. when i debug or without bebug then it gives error on this line. – Sami May 12 '14 at 08:37

2 Answers2

2

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.

Anshul Nigam
  • 1,608
  • 1
  • 12
  • 26
0

  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.

Bronek
  • 10,722
  • 2
  • 45
  • 46