Bulk Loading Tasks - Slow

E

Evan S. Dictor

I have written a Project add-in responsible for bulk loading data from
another application into Project. I have taken steps to make this load as
fast as possible, by temporarily turning off any option that would appear to
make this process slower. However, I'm noticing that when loading any large
number of tasks, each task is loaded slower than the one before. For example,
when loading 5000 tasks, the first tasks are loaded at a rate of 12
tasks/second, but the last tasks take close to a second each. The 5000 tasks
eventually loaded in just over 30 minutes, which is actually acceptable.
However, 10,000 tasks took over 7 hours, which is completely unacceptable (to
our customers that is). My gut feeling is that with every task loaded,
Project is "considering" all other tasks already loaded.

Currently I am setting the following at the beginning of the load:

Application.ScreenUpdating = False
Application.Calculation = pjManual
Application.AutoLevel = False

Is there anything else I can do to speed up this process?

Evan
 
E

Evan S. Dictor

The add-in I have written requires MS Project 2007.

At the start of the load I am also setting Application.UndoLevels = 1

Evan
 
R

Rod Gill

I think that sort of performance might be unavoidable. However, you could
try saving the 5000+ task as xml and see how long it takes to be imported.
If that time is acceptable your code can create a .xml file then use
automation to open the xml file.

I vaguely remember that the latest Project 2007 update (came out this week)
had an xml fix, so you may want to check it out and make sure that is a
pre-requisite for running your add-in.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com
 
E

Evan S. Dictor

My application is deployed as a DLL add-in, currently written in VB6. I
wouldn't expect to get much more performance by switching to VSTO alone,
although that is planned for the near future.

Evan
 
E

Evan S. Dictor

I was considering creating an MDB file instead of XML, but I guess it's
really the same solution. It would be nice if I could have Project stop all
processing while I add the rows, and then start again after I was done -
similar to what I guess it does during a File -> Open.

Evan
 
D

Dave G

I'm hitting a similar issue to Evan, but have found a somewhat bizarre
work-around that may be useful (and might just give someone that conceptual
spark to think of an elegant solution)....

Like Evan, I'm bulk-loading data from another app (MS Access) using VBA. My
script runs a custom query, loads the data over a network drive (table in in
a Jet-based database) and parses the data (~2800 records / tasks) into a VBA
object in 3 seconds flat.

When prototyping the code, I left the Project application visible. Update
rates were slow (1 task per second), as expected. I then used

prjApp.Visible = False
prjApp.ScreenUpdating = False
prjApp.Calculation = pjManual

to run Project invisibly in the background. I expected the script to run
MUCH faster, but little change in speed was observed (a status bar message in
Access informs me of progress - BTW, the status bar update isn't causing the
issue - I've tried running the code without it!). I note that doing a similar
"visible=false" action with a previous project using PowerPoint automation
caused a circa 50-fold increase in speed, as the application wasn't having to
re-draw the window all the time.

Now the bizarre bit....
By chance, when using
prjApp.Visible = True
prjApp.Calculation = pjManual
I used the scroll bar scroll-down button (n.b. scroll lock was OFF) while
the script was drawing out the items. Project scrolled down to the bottom of
the data set at the "normal" rate, drawing all of the tasks as if they'd
always been there. The result - 2 minutes for a full export to MSP instead of
45 minutes (though I did have to hold down the mouse button for 2 minutes!).

Clearly, then, MS Project is capable of fast import via automation. Have we
reached the limits of VBA, is there a bug in the implementation of MS
Project's ScreenUpdating property / similar or are we missing something we
should have set before adding the data?

Any suggestions gratefully received,
Dave.
 
D

Dave G

Is the slow bit just adding tasks or adding and outlining and linking?

Just adding tasks (populating Task.Title, Task.Flag1 plus a few others) is
slow (about 4 tasks / sec). The data entry area in project flickers
noticeably, as if it's being re-drawn every time any field is changed. Doing
this with the Project Application hidden and screen updating off does
increase the speed, but only slightly.
Populating all of the data fields I use (currently 17, including custom flag
and text fields) makes it about 50% slower (no outlining, application visible
and screen updating on).
Once I bring in outlining (which I have implemented via
Application.SelectCellDown and Application.OutlineIndent / Outdent as
appropriate after creating a task and populating its data fields), overall
speed decreases by about another 30% to just over 1 task per second.
I haven't yet got 'round to linking tasks (though it on my list of
developments-to-do).

The "hold down the scroll down button" work-around works for the latter 2 of
the above (I haven't tested it with the simplest example), giving a rate of
approx 50 tasks / second.

CPU loading is minimal unless using the work-around.

Platform (sorry I didn't declare before):
MS Project 2003 SP1
MS Access 2003 SP2
Win XP Pro SP2 (running in an enterprise environment)
P4 HT 3GHz / 512MB RAM
Integrated graphics card
(Also tested on laptops of similar capability but with faster graphics
drawing - slight improvement observed in all cases).
 
R

Rod Gill

I did a quick test on Project 2007 SP1 on Vista Business and Excel 2007 Sp1.

Open Excel file 5000 unique task names, nothing else.
VBA Macro in Project reading Excel data using automation
5000 tasks created in 159 seconds

I then closed the Excel file and opened it in Project
I created the map (Tasks only and Task name only field)

The data came across in 2 seconds!

I would not expect the read from Access to take much more time. However,
adding durations and links etc would take more time.

Calculation was Auto, as was screen updating.

Not sure if this helps, but it does suggest that either there is a
bottleneck reading the data in your add-in or the additional data you are
reading is causing the delay.

Let me know what data you are reading and I'll add it to my test.
--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com



"Stephen Sanderlin" <stephen DOTNOSPAM sanderlin NOSPAMAT msprojectexperts
DOTNOSPAM com> wrote in message
 
D

Dave G

Rod Gill said:
Let me know what data you are reading and I'll add it to my test.
Click to expand...

I'm reading various data from an Access database with an ancient field
format (it used to be a mainframe app) that we are obliged to maintain. In
one of my earlier posts, I note that this database's table is opened, parsed
(a number of string parsing operations are required to translate the data
into something easily used by Project e.g. multiple binary flags instead of
string enumerators for certain fields, translation of a strange date format
string into MS date format) with the parsed data loaded into the members of a
custom VBA class module, all within about 2 seconds.
The issue is then exporting it to MSP. I don't see how reading from a loaded
class would be a bottleneck (though I'm willing to be told!), so I presume
the problem is writing to MSP.

The MSP fields I am currently writing to are:
..Name
..Start
..Finish
..Baseline1Start
..Baseline2Start
..Baseline3Start
..Baseline4Start
..Text1
..Text2
..Text3
..Flag1
..Flag2
..Flag3
..Flag4
..Flag5
..Flag6
..Flag7
..PercentComplete


I've taken the values of VBA.DateTime.Timer at the beginning of export of
each task, after the field values have been set and after the outline level
has been set.
The results are as follows:
MSP Hidden, Showdraw False - writing of data items 0.31-1.07s, outlining
0.07-0.25s.
MSP visible, showdraw true, no user interaction - writing of data items
0.24-0.63s, outlining 0.06-0.20s.
MSP visible, showdraw true, user scrolling down - writing of data items
<0.01-0.02s, outlining consistently <0.01s.
Note that the above show my earlier ("gut feeling") timings for hidden MSP
were wrong.

I'd be interested to hear if the data movement is any slower for you when
exporting data from Excel to Project (i.e. code resides in Excel). I'd also
be interested to know if the workaround (continuous scrolling) works in
either case. It's only the fact that the workaround works that keeps me
searching for an elegant solution... I know the MSP MUST be capable of
working more rapidly - I just don't know how to make it so!

Regards,
Dave.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing subprojects without Save prompts 1
XML import is very slow 1
Can I "Freeze" the display while loading data? 7
Problem with VBA Code for Massive Publish 21
Resume date for task with multiple split 2
Functions in Outlook 2007 not working - Tasks, reminders 1
Alternative links between tasks in MS Project 2003 2
Merging XML data into active project 7

Top