3

I've inherited a C# / ASP.NET MVC / Entity Framework project with some slowness. There's not a lot of data in the DB but calls to .Include() were causing slowdowns.

However, I found something very strange. I have a 2k row table with just numbers (5 columns). I have indexes on the columns I'm searching.

When doing:

_entities.MyTable.Where(x=> x.Id1 == 4 && x.Id2 == 5).First()

it takes 1800ms on my development machine.

However, when I do :

_entities.MyTable.Where("it.Id1 = 4 and it.Id2 = 5").First()

it takes like 10ms.

What's the deal? I don't understand why the LINQ expression would be so slow.

marapet
  • 54,856
  • 12
  • 170
  • 184
Alexandru Petrescu
  • 3,449
  • 2
  • 23
  • 23
  • 1
    Try to un it inside LinqPad and compare its generated SQL query with your own – Tigran Apr 06 '12 at 07:04
  • Is MyTable inherited in any entity? – daryal Apr 06 '12 at 07:23
  • @daryal sorry I'm still a bit of a noob when it comes to EF, what would that look like? This Object/Model is related to other objects. The numbers in the table are Foreign Keys to other tables. – Alexandru Petrescu Apr 06 '12 at 07:25
  • @AlexandruPetrescu, do you have any relations in the model, such that an arrow pointing to MyTable? (which means mytable is a base class for other end of the relationship) – daryal Apr 06 '12 at 07:33
  • @daryal This class has 3 navigation properties (Foreign Keys), but I don't see "arrows" in the edmx model. I see the relationships but I'm not calling .Include() when doing the .Where(x=>) calls, so it shouldn't be querying those tables right? – Alexandru Petrescu Apr 06 '12 at 07:39

2 Answers2

0

Open Sql Profiler, look through the queries from EF. Try to analyze it, build plans. Seems that EF implenets queries in a strange way, without getting indexes.

Johnny_D
  • 4,592
  • 3
  • 33
  • 63
  • I don't have profiler on my dev box since I'm running SQL Express. Any other ways to do it? I got SQL Server Management Studio to run/debug queries. – Alexandru Petrescu Apr 06 '12 at 07:04
  • Not sure if it's allowed here posting link on other resource, but google "sql server get all queries". There is lots of ways to get runned queries without profiler – Johnny_D Apr 06 '12 at 07:14
  • I am using LinqPad based on a suggestion above and its just as fast as the String method, but the order of the parameters are weird... It's doing "where (4 = Id1) and (5 = Id2)" instead of Id1=4 and Id2=5. Doesn't seem like it's making much of a performance hit though in LinqPad. – Alexandru Petrescu Apr 06 '12 at 07:16
0

Could it be that EF has to generate the SQL to do the where clause in the first example and in the second the SQL is much easier to generate as it can just plug in your already provided SQL?

Ive found that EF is very slow at generating queries, it seems unlikely in this case as its a rather simple query in both cases.

Have you tried compiling the first query and running it multiple times to check that the time to execute only includes actually running the SQL and not just generating it?

martin308
  • 706
  • 6
  • 20