I'm writing an app in MVC3 using EF 4.1, code first and ninject. What I'm trying to do is very simple yet runs painfully slow.
There is a main table, Adverts, which contains cars. Simplified example of the table:
public class Advert
{
public int AdvertId { get; set; }
public int UserId { get; set; }
public int VehicleModelId { get; set; }
public Nullable<int> VehicleBodyTypeId { get; set; }
public Nullable<int> VehicleColourId { get; set; }
public Nullable<int> NumberOfDoors { get; set; }
public Nullable<int> VehicleEngineSizeId { get; set; }
public Nullable<int> VehicleTransmissionId { get; set; }
public Nullable<int> VehicleFuelTypeId { get; set; }
//more properties (mileage, registration etc)...
public virtual VehicleColour VehicleColour { get; set; }
public virtual VehicleEngineSize VehicleEngineSize { get; set; }
public virtual VehicleFuelType VehicleFuelType { get; set; }
public virtual VehicleModel VehicleModel { get; set; }
//more navigation properties...
}
All automatic mapping works correctly etc. There are of course the relevant tables for colour, engine size etc containing the actual values.
To scale test the app - I loaded approx 50,000 adverts into the Adverts table. When selecting non-navigation properties in a query e.g. just selecting AdvertId, UserId - EF performs relatively quickly, loading the page in about 1 second give or take a tenth or two depending on whether I'm loading the first page or the 5000th page, using Skip() and Take(), showing 10 results per page, example:
from a in repository.Adverts
select new ViewModel
{
AdvertId = a.AdvertId,
UserId = a.UserId
}
This is the behaviour I expect - it shouldn't matter how large the Skip() value is - because I'm only ever pulling 10 records it should always take rougly the same time (all of the rows have more or less similar values).
However, the moment I start adding properties which navigate to other tables e.g.:
from a in repository.Adverts
select new ViewModel
{
AdvertId = a.AdvertId,
UserId = a.UserId,
VehicleModel = a.VehicleModel.Model
VehicleTransmission = a.VehicleTransmission.Transmission
VehicleFuelType = a.VehicleFuelType.Fuel
//and more
}
all of a sudden the request time goes up massively. Each navigation property can add as much as 4 or 5 tenths of a second to the request time - why is this? This means that when selecting all required data - about 10 navigation properties - the request time
goes up to almost 4 seconds if selecting the last page - which is absolutely rediculous! Yet when selecting the first page - the time is roughly 1 second. And this is despite the fact I'm using paging! So only ever pulling 10 records at one particular time.
Why is it that the higher the page requested, so in essence, the larger the Skip() value - the longer it takes? More importantly - how do I resolve this?
I understand it loads from other tables - I just didn't expect it to be as slow, though some of it may well be down to my understanding.
The same query when copied and pasted from SQL Profiler, and run in SQL Manager takes about 2.8 seconds if selecting the "last page". If selecting the "first page" i.e. starting from the first row then it takes <200ms. The SQL generated is pretty ugly and
large too.
So I actually already tried this before posting, didn't seem to make much of a dfiference? Once I've run "Generate Views" are there any other steps required?
Once I've run "Generate Views" are there any other steps required?
yes，I suggest you that once you've used the EntityFramework （Code-first）to generate the database as well as the tables，you can just create Stored procdure or View and use it。Even if the speed is slow，you can try to use the traditional ADO.NET+EntityFramework
together to combine them together and use their advantages to avoid their disadvantages……
suj69
Member
22 Points
20 Posts
Slow Entity Framework performance when navigating other tables and pagination when loading high p...
Jun 30, 2012 11:47 PM|LINK
I'm writing an app in MVC3 using EF 4.1, code first and ninject. What I'm trying to do is very simple yet runs painfully slow.
There is a main table, Adverts, which contains cars. Simplified example of the table:
public class Advert { public int AdvertId { get; set; } public int UserId { get; set; } public int VehicleModelId { get; set; } public Nullable<int> VehicleBodyTypeId { get; set; } public Nullable<int> VehicleColourId { get; set; } public Nullable<int> NumberOfDoors { get; set; } public Nullable<int> VehicleEngineSizeId { get; set; } public Nullable<int> VehicleTransmissionId { get; set; } public Nullable<int> VehicleFuelTypeId { get; set; } //more properties (mileage, registration etc)... public virtual VehicleColour VehicleColour { get; set; } public virtual VehicleEngineSize VehicleEngineSize { get; set; } public virtual VehicleFuelType VehicleFuelType { get; set; } public virtual VehicleModel VehicleModel { get; set; } //more navigation properties... }All automatic mapping works correctly etc. There are of course the relevant tables for colour, engine size etc containing the actual values.
To scale test the app - I loaded approx 50,000 adverts into the Adverts table. When selecting non-navigation properties in a query e.g. just selecting AdvertId, UserId - EF performs relatively quickly, loading the page in about 1 second give or take a tenth or two depending on whether I'm loading the first page or the 5000th page, using Skip() and Take(), showing 10 results per page, example:
from a in repository.Adverts select new ViewModel { AdvertId = a.AdvertId, UserId = a.UserId }This is the behaviour I expect - it shouldn't matter how large the Skip() value is - because I'm only ever pulling 10 records it should always take rougly the same time (all of the rows have more or less similar values).
However, the moment I start adding properties which navigate to other tables e.g.:
from a in repository.Adverts select new ViewModel { AdvertId = a.AdvertId, UserId = a.UserId, VehicleModel = a.VehicleModel.Model VehicleTransmission = a.VehicleTransmission.Transmission VehicleFuelType = a.VehicleFuelType.Fuel //and more }all of a sudden the request time goes up massively. Each navigation property can add as much as 4 or 5 tenths of a second to the request time - why is this? This means that when selecting all required data - about 10 navigation properties - the request time goes up to almost 4 seconds if selecting the last page - which is absolutely rediculous! Yet when selecting the first page - the time is roughly 1 second. And this is despite the fact I'm using paging! So only ever pulling 10 records at one particular time.
Why is it that the higher the page requested, so in essence, the larger the Skip() value - the longer it takes? More importantly - how do I resolve this?
TIA
ignatandrei
All-Star
135210 Points
21690 Posts
Moderator
MVP
Re: Slow Entity Framework performance when navigating other tables and pagination when loading hi...
Jul 01, 2012 02:51 AM|LINK
Because it loads from other tables. Please make the same query in SqlServer and see how much it takes.
suj69
Member
22 Points
20 Posts
Re: Slow Entity Framework performance when navigating other tables and pagination when loading hi...
Jul 01, 2012 03:46 AM|LINK
I understand it loads from other tables - I just didn't expect it to be as slow, though some of it may well be down to my understanding.
The same query when copied and pasted from SQL Profiler, and run in SQL Manager takes about 2.8 seconds if selecting the "last page". If selecting the "first page" i.e. starting from the first row then it takes <200ms. The SQL generated is pretty ugly and large too.
rickevry
Member
386 Points
88 Posts
Re: Slow Entity Framework performance when navigating other tables and pagination when loading hi...
Jul 01, 2012 10:26 AM|LINK
You need to use the include method so that EF joins everything and only runs one query.
http://msdn.microsoft.com/en-us/library/bb896272.aspx
I am sure EF is executing multiple queries right now.
Use some like http://www.efprof.com/ to verify your sql.
christiandev
Star
8607 Points
1841 Posts
Re: Slow Entity Framework performance when navigating other tables and pagination when loading hi...
Jul 02, 2012 09:50 AM|LINK
Take a look here too...
http://msdn.microsoft.com/en-us/library/bb896240.aspx
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
suj69
Member
22 Points
20 Posts
Re: Slow Entity Framework performance when navigating other tables and pagination when loading hi...
Jul 02, 2012 10:56 AM|LINK
Do pre-generated views work with code first?
Edit: Sorry - just read the title in the URL lol.
So I actually already tried this before posting, didn't seem to make much of a dfiference? Once I've run "Generate Views" are there any other steps required?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Slow Entity Framework performance when navigating other tables and pagination when loading hi...
Jul 03, 2012 02:07 AM|LINK
yes，I suggest you that once you've used the EntityFramework （Code-first）to generate the database as well as the tables，you can just create Stored procdure or View and use it。Even if the speed is slow，you can try to use the traditional ADO.NET+EntityFramework together to combine them together and use their advantages to avoid their disadvantages……
Reguards！·