CODEWITHSUNDEEP
c#asp.net.netsql-serverentity-framework
Steven Lemmens
Steven Lemmens

Reputation: 1491

Entity Framework takes about 30 seconds on first query

I'm using Entity Framework 6 on a SQL Server database to query an existing database (database first, so there's an EDMX in my project).

I've noticed that the first time I request an entity, it can take up to thirty seconds for the query to be executed. Subsequent queries to the same object then get completed in a matter of milliseconds. The actual SQL being executed is very fast so it's not a slow query.

I've found that Entity Framework generates views on the background and that this is the most likely culprit. What I haven't found, however, is a good solution for this. There's a NuGet package that can handle the View Generation (EFInteractiveViews), but it hasn't been updated since 2014 and I hardly seem to find any information on how to use it.

What options do I have nowadays? I've tried initializing Entity Framework on Application_Start by doing a few queries, but this doesn't seem to help much at all, and also it's quite difficult to perform the real queries on Application_Start, because most queries use data from the current user (who is not yet logged on at Application_Start) so it's difficult to run these in advance.

I've thought about creating an ashx file that constantly polls the application by calling the API and keep it alive. I've also set the Application Pool to "AlwaysRunning" so that EF doesn't restart when the app pool is recycled.

Does anyone have any tips or ideas on how I can resolve this or things I can try?

Thanks a lot in advance. I've spent the better part of two days already searching for a viable solution.

Upvotes: 5

Views: 4798

Answers (2)

Monah
Monah

Reputation: 6784

There are many practices to speed up Entity Framework, I will mention some of them

  1. Turn off the LazyLoading (EDMX => open the file right click anywhere => properties => Lazy Loading Enabled set it to false )

  2. Use AsNoTracking().ToList() and when you want to update, use Attach and update object state to EntityState.Modified

  3. Use Indexes on your table

  4. Use Paging, do not load all the data at once

  5. Split your Edmx into many smaller, only include the ones you need in your page, ( this will effect the performance in good way)

  6. If you want to load related objects "be eager and not lazy", use Include, you might include using System.Data.Entity to use the lambda include features

Example for splitting your Edmx

If you have the following objects for a rent a car app : Country, City , Person, Car, Rent, Gender, Engine, Manufacturers,..etc.

Now

  • If you are working on a screen to Manage (CRUD) person, this means you don't need Car,Rent,Manufacturer, so create ManagePerson.edmx contains ( Country, City, Person, Gender)

  • If you are working on managing (CRUD) Car then you don't need (Person,City, Gender,Rent), so you can create ManageCar.edmx containing ( Car, Manufacturer,Country, Engine)

Upvotes: 5

Wurd
Wurd

Reputation: 475

Entity Framework must first compile and translate your LINQ queries into SQL, but after this it then caches them. The first hit to a query is always going to take a long time, but as you mention after that the query will run very quickly.

When I first used EF it was constantly an issue brought up by testers, but when the system went live and was used frequently (and queries were cached) it wasn't an issue.

See Hadi Hassans answer for general speed up tips.

Upvotes: 2

Related Questions