EF4 Import/Lookup thousands of records - my performance stinks!
- by Dennis Ward
I'm trying to setup something for a movie store website (using ASP.NET, EF4, SQL Server 2008), and in my scenario, I want to allow a "Member" store to import their catalog of movies stored in a text file containing ActorName, MovieTitle, and CatalogNumber as follows:
Actor, Movie, CatalogNumber
John Wayne, True Grit, 4577-12 (repeated for each record)
This data will be used to lookup an actor and movie, and create a "MemberMovie" record, and my import speed is terrible if I import more than 100 or so records using these tables:
Actor Table: Fields = {ID, Name, etc.}
Movie Table: Fields = {ID, Title, ActorID, etc.}
MemberMovie Table: Fields = {ID, CatalogNumber, MovieID, etc.}
My methodology to import data into the MemberMovie table from a text file is as follows (after the file has been uploaded successfully):
Create a context.
For each line in the file, lookup the artist in the Actor table.
For each Movie in the Artist table, lookup the matching title.
If a matching Movie is found, add a new MemberMovie record to the context and call ctx.SaveChanges().
The performance of my implementation is terrible. My expectation is that this can be done with thousands of records in a few seconds (after the file has been uploaded), and I've got something that times out the browser.
My question is this: What is the best approach for performing bulk lookups/inserts like this? Should I call SaveChanges only once rather than for each newly created MemberMovie? Would it be better to implement this using something like a stored procedure?
A snippet of my loop is roughly this (edited for brevity):
while ((fline = file.ReadLine()) != null)
{
string [] token = fline.Split(separator);
string Actor = token[0];
string Movie = token[1];
string CatNumber = token[2];
Actor found_actor = ctx.Actors.Where(a => a.Name.Equals(actor)).FirstOrDefault();
if (found_actor == null)
continue;
Movie found_movie = found_actor.Movies.Where( s => s.Title.Equals(title, StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault();
if (found_movie == null)
continue;
ctx.MemberMovies.AddObject(new MemberMovie()
{
MemberProfileID = profile_id,
CatalogNumber = CatNumber,
Movie = found_movie
});
try
{
ctx.SaveChanges();
}
catch
{
}
}
Any help is appreciated!
Thanks, Dennis
