Optimising looping through large data set and updating database

I have a database table storing information about documents (MigrationMaster). There is another table with security information on these (DocumentSecurity).

There are 4 steps I can see that I need to perform. I've listed them here with the average time (or range of times) I've recorded they take (you can also find them in the code):

1. Retrieve the document rows in a batches of 1000 using skip and take from the database (0.05 - 0.2 seconds)

2. Retrieve their related security information from the database (1.5 - 3.4 seconds)

3. Loop through the documents, looking up it's security policies. Do some light string manipulation then concatenate policies into one string (0.35 - 1 second)

4. Save it back to the database against that document. (0.4 - 0.8 seconds)

It's relatively easy to do, but I have 2.1 million documents and about 6 million security policies. So this is going to take ages to finish and I'd like to make it faster. In this sample set I am only taking the first 30,000 rows.

Heres the code:

public static Task ApplyDocumentSecurity(TextBlock log)
{
{
DateTime start = DateTime.Now;
TimeSpan runningTime = DateTime.Now - start;
int totalDocCount = 0;
int take = 1000;
double totalSeconds = 0;

// get total doc count (not applicable for testing, only going up to 30,000)
totalDocCount = 30000;

for (int i = 0; i < totalDocCount; i = i + take)
{
LogToTextBox(log, "Iteration " + ((i + take) / take).ToString() + Environment.NewLine);

using (MyEntities context = new MyEntities())
{
//1. Get the next batch of documents (0.15 seconds)
DateTime startGetDocuments = DateTime.Now;
var documents = (from d in context.MigrationMasters
where d.Security == null
orderby d.ID
select d).Skip(i).Take(take).ToList();

LogToTextBox(log, "Selecting documents took " + (DateTime.Now - startGetDocuments).TotalSeconds + Environment.NewLine);
totalSeconds += (DateTime.Now - startGetDocuments).TotalSeconds;

// get a filtered list of the unique docnums
var uniqueDocNums = documents.Select(x => x.DocNum).Distinct();

//2. Retrieve all the security policies for those documents (3 seconds)
DateTime startGetSecurityLines = DateTime.Now;
Lookup<SecurityItemKey, DocumentSecurity> securityLines = (Lookup<SecurityItemKey, DocumentSecurity>)context.DocumentSecurities
.Where(s => uniqueDocNums.Contains(s.DocNum)).ToList()
.ToLookup(o => new SecurityItemKey { DocNum = o.DocNum, Version = o.Version }, o => o);

LogToTextBox(log, "Retrieved security policies for documents took " + (DateTime.Now - startGetSecurityLines).TotalSeconds + Environment.NewLine);
totalSeconds += (DateTime.Now - startGetSecurityLines).TotalSeconds;

//3. Loop through each document and get the security policies
// now in memory (0.3 - 1 second)
DateTime startMatchDocuments = DateTime.Now;
foreach (MigrationMaster document in documents)
{
var lookupResult = securityLines[new SecurityItemKey { DocNum = document.DocNum, Version = document.Version }];

// manipulate security lines and join them
string acl = JoinSecurityLines(lookupResult.ToList());
document.Security = String.IsNullOrWhiteSpace(acl) ? "<CabinetDefault>" : acl;
}

LogToTextBox(log, "Matching security to documents took " + (DateTime.Now - startMatchDocuments).TotalSeconds + Environment.NewLine);
totalSeconds += (DateTime.Now - startMatchDocuments).TotalSeconds;

//4. Save documents back to DB (0.7 seconds)
DateTime startSaveDocuments = DateTime.Now;
context.SaveChanges();
LogToTextBox(log, "Saving documents took " + (DateTime.Now - startSaveDocuments).TotalSeconds + Environment.NewLine);

}
}

runningTime = DateTime.Now - start;
LogToTextBox(log, "All document security set. " + runningTime.TotalSeconds + Environment.NewLine);

});
}


Total runtime for 30,000 was 132 seconds. To do 2.1 million will theoretically take 152 minutes (2.5 hours).

If I reduce it to take 100 documents, step 2 was still between 2 and 3 seconds.

If I increase to take 5000 documents, step 2 blows out to 21 seconds or more.

If I increase to take 2000 documents, I get 1 second faster total run time for all 30,000. So I think there are minimal gains to be made by finding the sweet spot for how many I 'take'.

I'm wondering if there is any approach that will make a greater impact?

I added a non-clustered index to the DocNum column of the DocumentSecurities table accessed in step 3.

I have replaced the foreach loop in step 3 with the following:

List<Task> taskList = new List<Task>();
for (int j = 0; j < documents.Count; j++)
{
int localIndex = j;
var lookupResult = securityLines[new SecurityItemKey {
DocNum = documents[localIndex].DocNum,
Version = documents[localIndex].Version }];

// manipulate security lines and join them
string acl = JoinSecurityLines(lookupResult.ToList());
documents[localIndex].Security = String.IsNullOrWhiteSpace(acl) ? "<CabinetDefault>" : acl;
});