PDA

View Full Version : Can this MSP script get any faster?



Hedghog07
11-30-2010, 07:13 AM
Hi all

We're trying to import some records from an excel sheet into project on a regular basis, in the neighbourhood of 10,000 tasks worth.

To have it running as quickly as possible (because we can't use Unique ID's, we are using a custom Record ID field from the source data), I load the whole spreadsheet into memory in an array, load all tasks and subtasks into 3 separate dictionaries, with their task ID's as keys, then the main loop goes through each row in the array, and if the record ID exists in the task dictionary, then it updates 10 or so fields for that task, if it doesn't exist, it matches other pieces of info in the array with the summary tasks dictionary, and adds a task at that ID '+1'.

It works, but we are trying to do this for 10,000 tasks, and it's taking about 10-15 minutes for users on the slower machines (1-1.5GB ram, Single core CPU).

on the fastest machine (quad core, 8GB ram) it still takes about 5 minutes.

I've gotten to the point of trying silly little things, like turning the 'With tasks(x)' part into a function, and it's still not any faster.

If anyone has any experience with this stuff, or thinks they know a way to make this faster, please let us know! Dumping all 10,000 tasks (after 3 conditons are met, which atm every task meets) into an array and then dumping that array to excel only takes about 16 seconds.

Why does this take 10 minutes?

C

Oh - and before anyone asks, Excel is hidden, and screenupdating and calculation in both apps is off.

SamT
03-02-2013, 11:51 AM
At 10 minutes for 10,000 importations each unit is taking 6ms. 10 - 15 minutes ain't bad.

Let me tell you what I understand of what you're doing from your description.

Many Users, each User has unique 10K item task list. Load task list into array, distribute array into 3 ea 10K item dictionarys. Compare Array Items to first dictionary and process or use second dictionary to processs. Discard third dictionary. Send something to MSProject.

Can you explain your complex and lengthy process with a bit more detail?

A good explanation might take two screens of text, compared to the one brief paragraph you now have.