PowerShell Performance Win: A One-Line Change That Saved 50+ Hours

This is a quick post, but I felt it was worth sharing—especially for fellow ProjectWise users and PowerShell scripters who work with large datasets.

Recently, I was working on a script to generate a comprehensive report of all documents within a complex ProjectWise folder hierarchy. The scope? A whopping 29,240 folders and over 420,000 documents.

The Setup

To build the report, my script did the following:

1. Queried the database to retrieve the full folder hierarchy.
2. Retrieved the immediate child folders of a given parent.
3. Iterated through each of those child folders, querying the dms_doc table to get all documents contained within that folder and its subfolders.
4. For each document, I needed to add specific metadata to a DataTable for reporting.

This brings me to the key performance bottleneck I hit—and how a simple one-line change made a massive difference.

The Slow Way

Initially, to get the full path of the folder a document belonged to, I queried a cached DataTable ($dtHierachy_All) using Where-Object. Like this:

$tempFolder = $dtHierachy_All | Where-Object ProjectID -eq $row2.ProjectID

This method worked, but performance varied widely. On average, it took 20 to 100 milliseconds per document. Multiply that by 420,000 documents, and… well, that’s a long time. In fact, it was estimated to take 50+ hours to complete.

The Fast Way

Then I tried using the .Select() method from the System.Data.DataTable class:

$tempFolder = $dtHierachy_All.Select("ProjectID = $($row2.ProjectID)")

This small change dropped processing time per lookup to just 0.2 to 0.39 milliseconds—about 100× faster.

The Impact

That one-line change took the overall script runtime down from days to minutes. Truly a night-and-day difference.

Takeaway

When working with large datasets in PowerShell, especially when querying DataTables, avoid Where-Object when possible. Instead, leverage .Select() or even consider using hashtables for constant-time lookups.

I hope this tip saves someone else hours—or days—of processing time. If you’re scripting against ProjectWise or working with large PowerShell datasets, this kind of optimization can make a huge difference.

Happy scripting!