Linq to Entities much slower than Linq to SQL

StevenF

I'm finding that queries against an SQL server database that run through an EntityFramework connection run much slower than the equivalent query in Linq to SQL (or using the autogenerated SQL code from the Linq to Entities query).

This is my Linq to Entities query:

var result = StructureCategoryEntries.Where (sce => sce.OrganisationStructureCategory.OrganisationID == 2)
.Select (sce => new {EntryID = sce.EntryID, AssessmentInstances = sce.AssessmentInstances})
.ToList();

It takes about 30 seconds to run, although there are only a few hundred cases.

Here is the Linq to SQL query. It runs in less than a second (which is what I expect):

var result = StructureCategoryEntries.Where (sce => sce.OrganisationStructureCategories.OrganisationID ==2)
.Select (sce => new {EntryID = sce.EntryID, AssessmentInstances = sce.OrgStructureAssmessmentInstanceStructureEntries})
.ToList();

When I re-run the Linq to Entities query it takes just as long (not faster second time around). I've looked at this question, but the suggested fixes don't make any difference:

http://forum.linqpad.net/discussion/736/entityframework-connection-is-much-slower-than-linq-to-sql-connection

Any suggestions for what's going on? Very strange....

JoeAlbahari

First, have you tried comparing the generated SQL in each case? Click the 'SQL' to see the translation. From there, you can also click the 'Analyze SQL' tab to open in SSMS where you can compare execution plans.

Second, there's a possibility that LINQPad is walking the object graph in greater detail in the case of Entity Framework. You can test this by clicking 'Results to Data Grids' and re-running the queries; this ensures that only the top-level properties are walked.

StevenF

I had a look at the generated SQL for each approach, and they yield slightly different queries. The Linq to Entities version is a bit more complex, and so the execution plans are a bit different too. But the two queries run equally fast in SSMS.

When I run the Linq to Entities query in Linqpad with "Results to Data Grids" it runs as quickly as I expect. So it may be that as you say, Linq to Entities runs more slowly because of how it is walking the object graph.

However, I also tried the Linq to Entities query from within a Visual Studio project, and there it runs quickly. So the slow query execution seems to only happen in Linqpad.

ulfius

This sounds related to my issue forum.linqpad.net/discussion/966/rich-text-much-slower-than-data-grid#latest

JoeAlbahari

Steven/ulfius: Can you paste an example of the slow output? I'm interested as to whether it includes sub-entities. This will obviously slow things down.

ulfius

Mine has 7 sub-entities, that's why in the other thread my example got much faster when I excluded them and the relationship type of the sub-entity affected the speed, but it was also slow with just 1 sub-entity.

My table has 22,000 records and I also have Rich Text set to 1,000 rows, so I'm surprised that the difference would be 2 seconds compared to 1 hour 20 minutes. Using the limit to 1,000 rows was much slower than .Take(1000).Dump() (3 min 41 sec)

ulfius

Well, the light bulb just went on.

I made a small tweak to my query listed in the other discussion. Instead of

PIs.Dump();

I used

PIs.AsNoTracking().Dump();

and the results were remarkable. In Rich Text mode, it only took 2.9 seconds instead of over an hour.

Joe, do you know why this would be an issue only when in Rich Text mode?