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)
        return Task.Run(() =>
            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;
                    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?

UPDATES -- In order of the impact they have made.

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 task = Task.Run(() => {
         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;                                

I removed a redundant call to ToList() in the lambda expression in step 3.

  • \$\begingroup\$ I've made some minor improvements that has reduced the total run time to about 60 seconds. I removed ToList() after the Where() statement in step 2. I wrapped the contents of the foreach loop in step 3 into Tasks so the documents can all find their security string simultaneously. And finally I've added a non-clustered index to the DocNum column on the DocumentSecurity table. \$\endgroup\$ – SeanOB May 18 '16 at 12:32
  • \$\begingroup\$ Doing everything in SQL/stored procedure would certainly speed things up. If you need intermediate results consider a new table just for that purpose. If you do that, "truncate" vs "delete" to clear it. Define appropriate indexes. Do explicit "update statistics" after a mass insert. \$\endgroup\$ – radarbob May 18 '16 at 16:36
  • \$\begingroup\$ Okay that's basically all I needed to hear. I will do it in a stored procedure then. It makes the most sense. I just had no idea how to do it. I'll give it a go. \$\endgroup\$ – SeanOB May 18 '16 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.