# Compare-Object Optimations

**URL:** <https://forums.powershell.org/t/compare-object-optimations/17413>\
**Category:** PowerShell Help\
**Created:** [August 25, 2021, 12:27pm UTC](https://forums.powershell.org/t/compare-object-optimations/17413 "2021-08-25T12:27:24Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 25, 2021, 12:27pm UTC](https://forums.powershell.org/t/compare-object-optimations/17413/1 "2021-08-25T12:27:24Z")

</div>

Hi everyone, if possible could someone please point me in the right direction with optimising this script.  
Essentially it is importing a directory search, a csv, performing a compare then outputting matching results.

If I only have a few items in the test directory it works without issue and is very quick  
However when on the real directory with a few thousand items, with real data (a few thousand items)  
I have just worked out that this single line is going to take approximately 33 days to complete.

I wouldn’t say I’m impatient but… I might need it to run slightly quicker than that! 🙂

Any help greatly appreciated

$DataFilter is the problem causer 🙂

```auto
# $SearchPath is searched for PDF invoices including subdirectories #
$SearchPath = "C:\Somepath\Live"
# $ScanDirectories is the RESULT of a directory search including only .PDF files - This variable is passed into the array called $ScanArray #
$ScanDirectories =@(get-childitem -Path $SearchPath -Include *.pdf -Recurse | Select-Object Basename, Fullname)
# $SQLExport is the file exported from (SQL) with the Order Details. The column we need is "InternalRef" #
$SQLExport = "C:\Somepath\Live\SQLExport.csv"
# This file is used for outputting the results for the Missing invoices, i.e files that cannot be find based on the Dimensions reference #
$PDFInvoiceReport = "C:\InvoiceScript\PDFInvoiceReport.csv"
# $ExportInvoices Empty Array to hold final script results
$ExportInvoices =@()

#################################################
#################### ARRAYS #####################
#################################################

# $SQLArray array is a list of all order numbers found in SQL Export "InternalRef" Column)
$SQLArray = @((import-csv $SQLExport).InternalRef) | sort-object
# $ScanArray array is populated with a trimmed file basename from the $ScanDirectories search, basically a list of all PDF files with 0's removed from the basename
$ScanArray = @(($ScanDirectories).Basename.Trim('0')) | Sort-Object
# $DataFile is the filtered comparison between the arrays

#### THE LINE THAT IS GOING TO TAKE 33 DAYS TO FINISH #################################################################################################################################################
## FOREACH was added just to check it was actually doing something - each item takes approximately 6 seconds to be compared
$DataFilter = $ScanDirectories | where {Compare-Object -ReferenceObject $ScanArray -DifferenceObject $SQLArray -IncludeEqual -ExcludeDifferent | Where-Object sideindicator -EQ '==' | Select-Object inputobject } | foreach($obj) { write-host "Found a matching file - This takes a long time to complete "}

# $HyperLink is just a string to add to $DataFilter.$line so that it can create a Hyperlink in excel for the export
$Hyperlink ='=HYPERLINK("'
# $Quote is just a quote mark to append to $Hyperlink+$Line.FullName+$Quote i.e. =HYPERLINK("$Line.FullName")
$Quote = '")'

ForEach($Line in $DataFilter) {
write-host "Found invoice for OrderRef" $Line.BaseName.Trim('0') "getting ready to export"
$LineExport = New-Object PSObject
$LineExport | Add-Member -MemberType NoteProperty -Name "InternalRef" -Value $Line.BaseName
$LineExport | Add-Member -MemberType NoteProperty -Name "Location - Click the path to open Invoice" -Value ("{0}{1}{2}" -f $HyperLink, $Line.FullName, $Quote)
$ExportInvoices += $LineExport }

$export = $ExportInvoices | export-csv -path $PDFInvoiceReport -NoTypeInformation

```

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 25, 2021, 12:59pm UTC](https://forums.powershell.org/t/compare-object-optimations/17413/2 "2021-08-25T12:59:47Z")

</div>

Hmmm …

> [@MrSnowman2010](#):
>
> ```auto
> # $SearchPath is searched for PDF invoices including subdirectories #
> $SearchPath = "C:\Somepath\Live"
> 
> ```

You are over commenting your code. Anyone knowing just a little bit of PowerShell knows that this is a variable definition. Please do not add a comment for every single line of code. That makes the code even harder to read.

> [@MrSnowman2010](#):
>
> ```auto
> $ScanDirectories =@(get-childitem -Path $SearchPath -Include *.pdf -Recurse | Select-Object Basename, Fullname)
> 
> ```

You are limitting yourself with this `Select-Object`. Why not keeping the rest of the properties? You might use them later on. 😉

> [@MrSnowman2010](#):
>
> `$ExportInvoices =@()`

That’s unnecessary in the vast majority of the cases I know of. PowerShell implicitly creates an array for you if a query returns more than one result. If you want to cast a query to an array use this `@()` around your actual query.

> [@MrSnowman2010](#):
>
> ```auto
> $SQLArray = @((import-csv $SQLExport).InternalRef) | sort-object
> $ScanArray = @(($ScanDirectories).Basename.Trim('0')) | Sort-Object
> 
> ```

That’s what I meant in the other thread. Instead of limitting the objects to a single property you should enrich them with a property with the same name to compare agains each other.  
In this case you should either add the property `BaseName` to your array `$SQLArray` if there is none. Or add the property `InternalRef` containing actually the `BaseName`.

> [@MrSnowman2010](#):
>
> ```auto
> $DataFilter = $ScanDirectories | where {Compare-Object -ReferenceObject $ScanArray -DifferenceObject $SQLArray -IncludeEqual -ExcludeDifferent | Where-Object sideindicator -EQ '==' | Select-Object inputobject } | foreach($obj) { write-host "Found a matching file - This takes a long time to complete "}
> 
> ```

Now … your enemy … 😉 With this line of code you’re basically running the comparison for each single element in your array $ScanDirectories again and again and again.

On top of that

> [@MrSnowman2010](#):
>
> `-IncludeEqual -ExcludeDifferent | Where-Object sideindicator -EQ '=='`

When you use -IncludeEqual and -ExcludeDifferent you don’t need this particular Select-Object because there are only results with the SideIndicator ==.  
And

> [@MrSnowman2010](#):
>
> ```auto
> Select-Object inputobject 
> 
> ```

That does not make sense because you actually don’t have an input object without using the parameter `-PassThru` for `Compare-Object` and the objects you used for comparison are stripped off of all useful properties by your earlier commands. 😉

So without actually really understanding what you want to do you should start with something like this:

```auto
$SearchPath = "C:\Somepath\Live"
$SQLExport = "C:\Somepath\Live\SQLExport.csv"
$PDFInvoiceReport = "C:\InvoiceScript\PDFInvoiceReport.csv"

$SQLArray = Import-Csv $SQLExport | Select-Object -Property *,@{Name = 'BaseName'; Expression = {$_.InternalRef}}
$ScanDirectories = Get-ChildItem -Path $SearchPath -Include *.pdf -Recurse 

$DataFilter = 
    Compare-Object -ReferenceObject $ScanDirectories -DifferenceObject $SQLArray -Property 'BaseName' -IncludeEqual -ExcludeDifferent 

```

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 25, 2021, 1:50pm UTC](https://forums.powershell.org/t/compare-object-optimations/17413/3 "2021-08-25T13:50:24Z")

</div>

Hi again Olaf thanks for the speedy response,  
Reading your comments I had to chuckle at seeing how bad I am at this hahaha  
Will deffo make a note of the points 🙂

Yes sorry the commenting is more for me than anyone else.  
I have just tested your script and it completed in about 20 seconds 🙂  
One thing I do need to do however is pass the “fullname” of the file into the csv export.

So it will read like;  
12345 c:\somepath\somefile.pdf

This is the reason why the $datafilter variable was set to reference the $Scandirectory  
That was the only way I could get it to work with my limited knowledge, worked fine with 10 test items. Nearly 8000 items however caused a “slight” performance issue 🤣

Thanks

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 25, 2021, 2:24pm UTC](https://forums.powershell.org/t/compare-object-optimations/17413/4 "2021-08-25T14:24:51Z")

</div>

> [@MrSnowman2010](#):
>
> Reading your comments I had to chuckle at seeing how bad I am at this hahaha

Don’t worry. We all started small once. 😉

> [@MrSnowman2010](#):
>
> One thing I do need to do however is pass the “fullname” of the file into the csv export.

I’m unsure if I got this right. But if that’s the property you want to compare you have to have it in both arrays.

> [@MrSnowman2010](#):
>
> This is the reason why the $datafilter variable was set to reference the $Scandirectory  
> That was the only way I could get it to work with my limited knowledge, worked fine with 10 test items. Nearly 8000 items however caused a “slight” performance issue

If you want to compare two objects or two arrays of objects you don’t have to limit the objects to one single property each. Instead you have to make sure that both objects or arrays of objects have one property in common you can provide for `Compare-Object` for comparison.

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 26, 2021, 7:46am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/5 "2021-08-26T07:46:00Z")

</div>

Hi Olaf sorry for the late reply.

I would like to compare the SQLexport-internal ref to file basenames (with the 00’s stripped from the basename so they match up correctly)

If there is a match between the basenames(0’s removed and the Internal ref), from there I need to export the basename and the filepath which is found under the “fullname” property of the get-childitem command.

Does that make sense?

Thanks

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 26, 2021, 7:52am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/6 "2021-08-26T07:52:40Z")

</div>

Hi Olaf here is a very high level diagram which “may” help

 ![Capture](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/8/817d2c511e6eaf6a87844abcd5ae1c8e23747727.png)

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 26, 2021, 8:12am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/7 "2021-08-26T08:12:51Z")

</div>

> [@MrSnowman2010](#):
>
> Does that make sense?

It does actually. 😉

Try this:

```auto
$SearchPath = 'C:\Somepath\Live'
$SQLExport = 'C:\Somepath\Live\SQLExport.csv'
$PDFInvoiceReport = 'C:\InvoiceScript\PDFInvoiceReport.csv'

$SQLArray = 
    Import-Csv -Path $SQLExport | 
        Select-Object -Property *,@{Name = 'StrippedBaseName'; Expression = {$_.InternalRef}}
$ScanDirectories = 
    Get-ChildItem -Path $SearchPath -Include *.pdf -Recurse | 
        Select-Object -Property *, @{Name = 'StrippedBaseName'; Expression = { $_.BaseName -as [int] } } 

$Comparison = 
    Compare-Object -ReferenceObject $ScanDirectories -DifferenceObject $SQLArray -Property 'StrippedBaseName' -IncludeEqual -ExcludeDifferent -PassThru
$Comparison |
    Export-Csv -Path $PDFInvoiceReport -NoTypeInformation
$Comparison |
    Select-Object -Property BaseName,FullName

```

If it does not show the expected results you may try to switch the values of ` -ReferenceObject` with the `-DifferenceObject`.

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 26, 2021, 8:52am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/8 "2021-08-26T08:52:59Z")

</div>

Hi Olaf, just tried this, does appear to take a very long time to run  
I will try and add a “foreach” into the line just to check how long it takes to find each item. It however appears to stop on the $Scandirectories variable now, presumably the expressions to declare as integer

![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/1/162d634379ab52be3ef0967856ea4452efb16086.png)

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 26, 2021, 9:10am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/9 "2021-08-26T09:10:38Z")

</div>

Hi Olaf, apologies, it would appear that this is actually working on the test directories with a few items.

I think potentially it may be the sheer volume of items that is causing the problem.  
I don’t know why it would say it can’t find an item however when running on the real directory 🙂

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 26, 2021, 9:10am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/10 "2021-08-26T09:10:45Z")

</div>

> [@MrSnowman2010](#):
>
> does appear to take a very long time to run

If performance matters you should measure it. Read the help for

> **[Measure-Command (Microsoft.PowerShell.Utility) - PowerShell](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/measure-command?view=powershell-7.4&viewFallbackFrom=powershell-7.1)**
>
> The Measure-Command cmdlet runs a script block or cmdlet internally, times the execution of the operation, and returns the execution time. Note Script blocks run by Measure-Command run in the current scope, not a child scope.

> [@MrSnowman2010](#):
>
> It however appears to stop on the $Scandirectories variable now, presumably the expressions to declare as integer

I assume “L:” is a network drive, isn’t it? I’d recommend to query the needed list locally on the server where the folder physically is. That would speed up.

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 26, 2021, 9:13am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/11 "2021-08-26T09:13:24Z")

</div>

That is a very good idea indeed,  
I will move everything over to the file server and give it a try as we are also co-located, so even though I am in the office, the data is technically elsewhere, I have a few meeting shortly but will come back as soon as I can.

Thanks

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 26, 2021, 9:52am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/12 "2021-08-26T09:52:18Z")

</div>

Hi Olaf, I have ran this on the actually server and it now takes about 5 minute to complete.

I’m going to do a few ammendments for formatting purposes on the export but the core comparison part of the script works wonderfully now.

Once again, you are a gentleman, thanks for your help 🙂

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 26, 2021, 10:02am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/13 "2021-08-26T10:02:04Z")

</div>

> [@MrSnowman2010](#):
>
> the core comparison part of the script works wonderfully now.

Great. I’m glad it helped. 👍🏽 😃

---

<div class="post-metadata">

**Author:** ![MrSnowman2010](https://avatars.discourse-cdn.com/v4/letter/m/ecae2f/32.png) [@MrSnowman2010](https://forums.powershell.org/u/MrSnowman2010)\
**Post date:** [August 26, 2021, 11:04am UTC](https://forums.powershell.org/t/compare-object-optimations/17413/14 "2021-08-26T11:04:24Z")

</div>

Just one last question on this if you don’t mind.  
Don’t worry it still works 🙂

If I remove the select-object from the end of comparison array, I get all the additional info from the $Scandirectory array.  
However if I reverse this, i.e. try to pull it from the $SQLArray, it still pulls the basename info but nothing else?

Any ideas, this is additional to original however it would actually be really useful to have.

![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/5/5274ac5e4f773796a837666ef499e1d7307a5e5c.png)

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 26, 2021, 12:35pm UTC](https://forums.powershell.org/t/compare-object-optimations/17413/15 "2021-08-26T12:35:29Z")

</div>

> [@MrSnowman2010](#):
>
> if you don’t mind

Why should I start to mind now? 😉

> [@MrSnowman2010](#):
>
> However if I reverse this, i.e. try to pull it from the $SQLArray, it still pulls the basename info but nothing else?

I don’t know how to explain that correctly. Usually only the reference object will be part of the output when you use `-PassThru`.  
You might play a little bit for yourself:

```auto
$Data1 = 
@'
First,Last,Compare
John,Lennon,1
George,Harrison,2
Paul,McCartney,3
Ringo,Starr,4
'@ |
    ConvertFrom-Csv

$Data2 = 
@'
Name,Compare
Mick,1
Ron,5
Keith,3
Charlie,8
'@ |
    ConvertFrom-Csv

Compare-Object -ReferenceObject $Data1 -DifferenceObject $Data2 -Property Compare -PassThru -IncludeEqual

Compare-Object -ReferenceObject $Data2 -DifferenceObject $Data1 -Property Compare -PassThru -IncludeEqual

```

Please run the two Compare-Object command lines separately!!

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:21pm UTC](https://forums.powershell.org/t/compare-object-optimations/17413/16 "2024-05-16T20:21:45Z")

</div>

Closed on May 16, 2024, 8:21pm UTC


