How to Improve Performance when Loading Data into Microsoft Project with VSTO

Question

Background

We have an existing application which can load data into Microsoft Project so it can be manipulated by MS Project.

The original application is an old-style COM Project add-in written by VB6 targeting on MS Project 2003 / 2007, and now we are planning to migrate them to VSTO add-in targeting MS Project 2013 / 2016.

Problem

For them COM Add-in solution, we have encountered performance issue:

Testing project has 4414 activities (av_activity table) and 8330 relationships (av_reln table).

Performance issues were reported during Load operation:

Load time on MSP 2003 template for above project is apprx: 30-35 mins.

Load time on MSP 2007 & 2010 template is > 3hrs

For VSTO solution it gets better but still we hope can improve the loading performance by some means.

What We Have Tried So Far

We have tried several tricks but didn't get much effect...

• Diable auto calculation during adding data

  _application.Calculation = PjCalculation.pjManual;

• Disable screen updating during adding data

  _application.ScreenUpdating = false;

• Disable change highlighting

  _application.EnableChangeHighlighting = false;

• Disable status bar

  _application.DisplayStatusBar = false;

• Set the Undo value to 1

• Set default View to "Task Sheet View" (instead of "Gantt View" which incurs more overhead during load).

Seeking Help For

I am seeking help for any solution on improving performance while loading data into MS Project with VSTO Add-in and C#.

Thanks in advance!

Answer 1

Yes, using the TaskDependencies.Add method to create relationships is extremely slow.

This is unfortunate since it is the logical method to use. The only workaround is to build the Predecessor list ahead of time. Once you have that list you can create the relationships in two ways:

• add to an existing schedule
• create a new schedule by importing task data that includes the predecessor list

Since your data is already tabular, importing it from a csv or Excel file works very well using the Project Import Wizard. To use the Project Import Wizard, select a csv or Excel file to open from within MS Project (File->Open). The wizard will walk you through the steps of creating an import map which you can save for automation use later. Include the Predecessors column in your import data and the wizard will create the relationships for you in a matter of seconds.

The task Predecessors field contains a comma-separated list of predecessors. Each predecessor is in the following format:

• Task ID
• Relationship type (FS, FF, SS, SF)
• Lag (+/- #d)

Predecessors with FS type and no lag are shown with just the Task ID. Here are some examples of predecessor field values:

• 14,126,127
• 73,92SS
• 144FS+3d,145

How to create predecessor field values from a table of relationships

Presuming a table of relationships that contains the Task ID of the predecessor and successor:

1. Create a dictionary with an integer key (successor task IDs) and string value (predecessors).
2. Loop through the table of relationships and add the successor's task ID and its predecessor to the dictionary; if it already exists, update the value to append a comma and the predecessor.
3. Loop through the dictionary and A) update the predecessor column in the csv file or B) set the predecessors field on the successor task in the schedule.

Answer 2

If I understand the problem, you said it takes about 30 minutes to put 4000-ish tasks into MS Project. I'm not sure what you meant by 8000 relationships; do you mean predecessors/successors? Perhaps you could clarify? So, what you have is a system made up of 3 components: a data source, a transfer mechanism, and MS Project itself; correct?

This performance surprises me. I whipped up some VBA code (see bottom of post) to test the performance of adding 4000 tasks with various outline levels + predecessors and on my system (Proj 2016, Intel i7, Win 10) it took at most 100 seconds to add the tasks. This tells me that there isn't a bottleneck in Project. I would suspect the bottleneck is in either your data source or the transfer mechanism.

To confirm this, perhaps you could try just adding all of your task (you called them an activity) data to one task's notes property and see what your performance is. A If it is quick, then perhaps try adding the 4000 tasks but with no properties (other than name) and then incrementally add more properties until you find which properties(s) add the slow-down. From my test below, using the task.predecessors property added a 6x performance reduction, the rest had a negligible impact; you may encounter another performance degrading attribute. B But if adding your task data to just one task's notes is still slow, then you have a problem with the data source or transfer mechanism. Maybe you are sending one task (i.e. activity) at a time and instead could build a batch? Whatever the case, isolate the problem and eliminate it.

Good Luck!

Sub add4000tasks()

On Error Resume Next

Dim myTask As task
Dim myProject As Project
resPool = Split("Allice,Bob,Claire,Dave", ",")

For testRun = 0 To &HF '00001111

    testPreds = testRun And &H1 '00000001
    testOutlines = testRun And &H2 '00000010
    testDurations = testRun And &H4 '00000100
    testAssignments = testRun And &H8 '00001000

    If testPreds Then Debug.Print "Testing Predcessors"
    If testOutlines Then Debug.Print "Testing Outlines"
    If testDurations Then Debug.Print "Testing Durations"
    If testAssignments Then Debug.Print "Testing Resource Assignments"


    Application.Projects.Add
    Set myProject = ActiveProject
    Application.Calculation = pjManual

    starttime = Now

    Set myTask = myProject.Tasks.Add("Task 0")
    For a = 1 To 4000

        Set myTask = myProject.Tasks.Add("Task " & a)

        If testPreds Then
            myTask.Predecessors = Rnd * 10000000 Mod a + 1 'may fail if predecessor is also a parent
        End If

        If testOutlines Then
            If Rnd * 10000000 Mod 10 = 0 And myTask.OutlineLevel < 10 Then
                myTask.OutlineIndent
            ElseIf Rnd * 10000000 Mod 10 = 1 And myTask.OutlineLevel > 1 Then
                myTask.OutlineOutdent
            End If
        End If

        If testDurations Then
            myTask.Duration = Rnd * 10000000 Mod 50 & "d"
        End If

        If testAssignments Then
            myTask.ResourceNames = resPool(Rnd * 10000000 Mod UBound(resPool) + 1)
        End If


    Next

    Application.Calculation = pjAutomatic

    Debug.Print (Now - starttime) * 86400 & vbCrLf

    Application.FileCloseEx (pjDoNotSave)

Next

End Sub

Answer 3

If you are creating a new project with your data, you could use MPXJ to generate a Microsoft project XML file (MSPDI file) which you can then open directly in Project.