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):

1. Create a context.
2. For each line in the file, lookup the artist in the Actor table.
3. For each Movie in the Artist table, lookup the matching title.
4. 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