Fast insert relational(normalized) data tables int

问题:

I'm trying to find a better and faster way to insert pretty massive amount of data(~50K rows) than the Linq that I'm using now. The data I'm trying to write to a local db is in a list of ORM mapped data serialized and received from WCF. I'm keen on using SqlBulkCopy, but the problem is that the tables are normalized and are actually a sequence or interconnected tables with one-to-many relationships.

Here's some code that illustrates my point:

foreach (var meeting in meetingsList)
    {
         int meetingId = dbAccess.InsertM(value1, value2...);
         foreach (var competition in meeting.COMPETITIONs)
         {
                int competitionId = dbAccess.InsertC(meetingid, value1, value2...);
                foreach(var competitor in competition.COMPETITORs)
                {
                       int competitorId = dbAccess.InsertCO(comeetitionId, value1,....)
                       // and so on
                }
         }
    }

where dbAccess.InsertMeeting looks something like this:

// check if meeting exists
int  meetingId = GetMeeting(meeting, date);

if (meetingId == 0)
{
   // if meeting doesn't exist insert new
   var m = new MEETING
   {
       NAME = name,
       DATE = date
   }
   _db.InsertOnSubmit(m);
   _db.SubmitChanges();
}

Thanks in advance for any answers. Bojan

回答1:

I would still use SqlBulkCopy to quickly copy your data from the external file into a staging table that has the same (flat) structure as the file (you'll need to create that table ahead of time)

Once it's loaded, you can split up the data across multiple tables using e.g. a stored procedure or something - should be pretty fast since everything's on the server already.