CODEWITHSUNDEEP
asp.net-mvcsql-server-2008entity-framework-5
lee lee
lee lee

Reputation: 21

Entity Framework and foreign key relationships producing slow sql performance

I have normalized a Country/region/city database into multiple tables. City has a foreign key to region which has a foreign key to country.

The CITY table includes 2 additional columns for finding the associated numerical IPAddress. As you can imagine the city table has over 4 million records (representing the cities in the world which maps back to a region and then a country).

CITY, REGION, COUNTRY are entities that I have mapped with Entity Framework power tools, that all have a name column (that represents a cityname, regionname, countryname, respectively), and a primary key IDENTITY column that is indexed.

Let's say I have a table / entity called VisitorHit that has the following columns:

id as int (primary key, identity)
dateVisited as datetime 
FK_City as int (which has a many to one relationship to the CITY entity)

In code I use the VisitorHit entity like:

var specialVisitors = VisitorRepository.GetAllSpecialVisitors();
var distinctCountries = specialVisitors.Select(i => i.City.CityName).Distinct().ToArray();

now the GetAllSpecialVisitors returns a subset of the actual visitors (and it works pretty fast). The typical subset contains approximately 10,000 rows. The Select Distinct statement takes minutes to return. Ultimately I need to further delimit the distinctCountries by a date range (using the visitorhit.datevisited field) and return the count for each distinctCountry.

Any ideas on how I could speed up this operation?

Upvotes: 2

Views: 1128

Answers (1)

Colin Mackay
Colin Mackay

Reputation: 19175

Have you looked at SQL Profiler to see what SQL is being generated for this. My first guess (since you don't post the code for GetAllSpecialVisitors) would be that you are lazy loading the City rows in which case you are going to be producing multiple calls to the database (one for each instance in specialVisitors) to get the city. You can eager load the city in the call to GetAllSpecialVisistors().

Use .Include("City") or .Include(v=>v.City)

e.g. Something like this:

var result = from hit in context.VisitorHits
             where /* predicates */
             .Include(h =>h.City)

Like I said, you need to look at what the SQL Profiler is showing you to see what SQL Is actually being sent to the SQL Server. But when I have issues like this it turns out to be the most common cause.

If you try writing the query yourself in the SSMS and it works well then another solution may be to write a view and query on the view. That is something else I've done on occasion when Entity Framework produces unwieldy queries that don't work efficiently.

Upvotes: 3

Related Questions