How to improve performance when loading data into Microsoft Project using VSTO
Background
We have an existing application that can load data into Microsoft Project, so it can be manipulated with MS Project.
The original application is an old style COM Project add-in written with VB6 targeting MS Project 2003/2007 and we are now planning to migrate them to the VSTO MS Project 2013/2016 add-in targeting.
Problem
For them COM Add-in solution, we ran into a performance issue:
The test project has 4414 activities (av_activity table) and 8330 relationships (av_reln table).
Performance issues were reported during the load operation:
The download time for the MSP 2003 template for the above project is approximately 30-35 minutes.
Time to download MSP 2007 and 2010> 3 hours
This is improving for the VSTO solution, but we hope some improvements can improve loading performance.
What have we tried so far
We tried a few tricks but didn't get much effect ...
-
Complex automatic calculation when adding data
_application.Calculation = PjCalculation.pjManual;
-
Disable screen refresh on adding data
_application.ScreenUpdating = false;
-
Disable highlighting changes
_application.EnableChangeHighlighting = false;
-
Disable status bar
_application.DisplayStatusBar = false;
-
Set Undo to 1
- Set to default. Display Task Table View (instead of Gantt View, which carries additional overhead at boot time).
Find help for
I am looking for help for any solution to improve performance when loading data into MS Project using VSTO and C # add-in.
Thanks in advance!
source to share
Yes, using the TaskDependencies.Add method to create relationships is extremely slow.
This is unfortunate as it is a logical method. The only solution is to create a list of predecessors in advance . Once you have this list, you can create relationships in two ways:
- add to existing schedule
- create a new schedule by importing task data that includes a list of predecessors
Since your data is already tabular, import from a csv or Excel file works very well with the Project Import Wizard. To use the Project Import Wizard, select the csv or Excel file to open from MS Project (File-> Open). A wizard walks you through the steps of creating an import map, which you can save for automation later. Include the Predecessors column in your import data and the wizard will create a relationship for you in seconds.
Task The predecessors field contains a comma-separated list of predecessors. Each predecessor has the following format:
- Task ID
- Relationship type (FS, FF, SS, SF)
- Defer (+/- #d)
Forerunners with FS type and no delay are only displayed with the task ID. Here are some examples of predecessor field values:
- 14126127
- 73.92SS
- 144FS + 3d, 145
How to create predecessor field values from relationship table
Assuming a relationship table that contains the task ID of the predecessor and successor:
- Create a dictionary with integer key (job IDs for successor) and string value (predecessors).
- Scroll through the relationship table and add the successor job ID and its predecessor to the dictionary; if it already exists, update the value to add the comma and predecessor.
- Scroll through the dictionary and A) update the predecessor column in the csv file or B) set the predecessor field in the successor task in the schedule.
source to share
If I understand the problem, you said that it takes about 30 minutes to run 4000 hash tasks in MS Project. I'm not sure what you mean by 8000 relationships; do you mean predecessors / successors? Maybe you could clarify? So, you have a system consisting of three components: a data source, a transmission mechanism, and MS Project itself; right?
This work surprises me. I hacked into VBA code (see bottom of post) to test the performance of adding 4000 tasks with different outline levels + predecessors and on my system (Proj 2016, Intel i7, Win 10) it took no more than 100 seconds to add tasks.This says that there is no bottleneck in the project. I suspect the bottleneck is either in your data source or in the transmission mechanism.
To confirm this, perhaps you can try to just add your entire task (you called it an activity) to one property of the task notes and see what your performance is. A . If it's fast, then maybe try adding 4000 tasks, but no properties (other than the name), and then gradually adding additional properties until you find which properties add slowdown. From my test below, using the task.predecessors property added a 6x performance degradation, the rest had little impact; you may encounter another attribute of performance degradation. BHowever, if adding task data to only one task note is still slow, you have a problem with the data source or the transfer mechanism. Maybe you are submitting one job (i.e. activity) at a time and can create a batch instead? Either way, isolate the problem and fix 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
source to share