SOLVED

PS array performance >40K entries

%3CLINGO-SUB%20id%3D%22lingo-sub-1870911%22%20slang%3D%22en-US%22%3EPS%20array%20performance%20%26gt%3B40K%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1870911%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI'm%20trying%20to%20find%20the%20fastest%20way%20to%20search%20a%20large%20array%20(40K%20entries)%20for%20a%20value.%20However%2C%20I'm%20struggling%20with%20the%20way%20in%20which%20the%20array%20is%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20read%20in%20all%20AD%20users%20as%20shown%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000080%22%3E%24AllADUsers%20%3D%20Get-ADUser%20-Filter%20%22*%22%20-properties%20SAMAccountName%2C%20DisplayName%2C%20UserPrincipalName%2C%20Company%2C%20Office%2CDepartment%2C%26nbsp%3BManager%2C%26nbsp%3BDescription%2C%26nbsp%3BCreated%2C%26nbsp%3BLastLogonDate%2C%26nbsp%3BEmployeeType%2CInfo%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000080%22%3E-Server%26nbsp%3B%24ADServer%26nbsp%3B-Credential%26nbsp%3B%24c%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000080%22%3E%7Cselect%26nbsp%3BSAMAccountName%2C%26nbsp%3BDisplayName%2C%26nbsp%3BUserPrincipalName%2C%26nbsp%3BCompany%2C%26nbsp%3BOffice%2C%26nbsp%3BDepartment%2C%26nbsp%3BManager%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000080%22%3EDescription%2C%26nbsp%3BCreated%2C%26nbsp%3BLastLogonDate%2C%26nbsp%3BEmployeeType%2CInfo%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20this%20is%20why%20I%20have%20a%20query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20search%20the%20array%20using%20the%20.Contains%20method%20it%20finds%20the%20entry%20in%20around%202ms%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000080%22%3E%24AllADUsers.Contains(%24SearchID)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20then%20try%20to%20pull%20the%20data%20for%20the%20record%20using%20the%20%22where%22%20method%20it%20takes%201000ms%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000080%22%3E%24AllADUsers.Where(%7B%24_.UserPrincipalName%20-eq%20%22%24SearchID%22%7D)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20why%20can%20it%20find%20the%20value%20in%202ms%20using%20%22contains%22%20method%20but%20takes%20more%20than%201000ms%20to%20read%20the%20actual%20record%20using%20the%20%22where%22%20method.%20Its%20as%20if%20it's%20using%20a%20different%20search%20algorithm%20in%20the%20%22where%22%20method.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%20it%20was%20faster%20to%20use%20Get-AdUser%20for%20each%20search%20as%20this%20only%20takes%20900ms%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20tried%20other%20variations%20to%20no%20avail%2C%20such%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%20color%3D%22%23000080%22%3E%24AllADUsers%20%7C%20where-object%20%7B%24_.UserPrincipalName%20-eq%20%24SearchID%7D%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20gratefully%20received.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3Eblairkei%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1870911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPoweShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1871305%22%20slang%3D%22en-US%22%3ERe%3A%20PS%20array%20performance%20%26gt%3B40K%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863702%22%20target%3D%22_blank%22%3E%40blairkei%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20create%20a%20hash%20table%20where%26nbsp%3BUserPrincipalName%20is%20the%20key%20and%20the%20user%20properties%20are%20the%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%24AllADUsers%20%3D%20Get-ADUser%20-Filter%20%22*%22%20-properties%20SAMAccountName%2C%20DisplayName%2C%20UserPrincipalName%2C%20Company%2C%20Office%2CDepartment%2C%20Manager%2C%20Description%2C%20Created%2C%20LastLogonDate%2C%20EmployeeType%2CInfo%20-Server%20%24ADServer%20-Credential%20%24c%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24AllADUsersHash%20%3D%20%5Bordered%5D%40%7B%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24AllADUsers%20%7C%20ForEach-Object%20%7B%20%24AllADUsersHash.add(%24_.UserPrincipalName%2C%24_)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24AllADUsersHash%5B%22Joe%40microsoft.com%22%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%20or%3CBR%20%2F%3E%24AllADUsersHash.Item(%22Joe%40microsoft.com%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1871519%22%20slang%3D%22en-US%22%3ERe%3A%20PS%20array%20performance%20%26gt%3B40K%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863879%22%20target%3D%22_blank%22%3E%40Joe_Cauffiel%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20alternative%20solution.%20My%20next%20step%20was%20to%20switch%20over%20to%20using%20a%20HASH%20array%20but%20it%20still%20does%20not%20explain%20why%20.contains%20is%20taking%202ms%20and%20.where%20is%20taking%201000ms%20when%20they%20are%20both%20using%20the%20same%20search%20criteria%20and%20scanning%20through%20the%20array.%20I'm%20wondering%20if%20I%20am%20missing%20an%20option%20on%20the%20%22.where%22%20somehow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1872102%22%20slang%3D%22en-US%22%3ERe%3A%20PS%20array%20performance%20%26gt%3B40K%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1872102%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863702%22%20target%3D%22_blank%22%3E%40blairkei%3C%2FA%3E%26nbsp%3BAfter%20testing%20using%20the%20HASH%20array%20the%20performance%20of%20a%20search%20takes%202000ms.%20So%20this%20method%20is%20even%20slower%20than%20either%20reading%20from%20the%20ARRAY%20or%20directly%20reading%20the%20record%20from%20AD.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1875158%22%20slang%3D%22en-US%22%3ERe%3A%20PS%20array%20performance%20%26gt%3B40K%20entries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1875158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863702%22%20target%3D%22_blank%22%3E%40blairkei%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Contain%20method%20will%20return%20true%20or%20false%2C%20but%20to%20use%20it%20in%20your%20case%2C%20I%20guess%20you%20will%20need%20to%20add%3C%2FP%3E%3CP%3E%3CSTRONG%3E%24AllADUsers.UserPrincipalName.Contains(%24SearchID)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20guess%20this%20is%20related%20to%20how%20many%20items%20in%20the%20object%2C%20so%20in%20your%20case%2C%20each%20object%20of%20the%20array%20contain%20multiple%20items.%20not%20a%26nbsp%3B%3C%2FP%3E%3CP%3EKey%3D%20Value%3C%2FP%3E%3CP%3Eif%20you%20check%20the%20Where%7B%7D%20statement%2C%20you%20are%20going%20to%20each%20item%20properties%2C%20and%20this%20is%20why%20you%20get%20the%20result.%3C%2FP%3E%3CP%3EThe%20%3CSTRONG%3E%24AllADUsers.Contains(%22*user1%40necad.ae*%22)%26nbsp%3B%3C%2FSTRONG%3Eis%20not%20equal%20to%26nbsp%3B%3CSTRONG%3E%24AllADUsers.Where(%7B%24_.UserPrincipalName%20-eq%20%22%24SearchID%22%7D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi

I'm trying to find the fastest way to search a large array (40K entries) for a value. However, I'm struggling with the way in which the array is working.

 

I read in all AD users as shown below

 

$AllADUsers = Get-ADUser -Filter "*" -properties SAMAccountName, DisplayName, UserPrincipalName, Company, Office,Department, Manager, Description, Created, LastLogonDate, EmployeeType,Info 

-Server $ADServer -Credential $c

|select SAMAccountName, DisplayName, UserPrincipalName, Company, Office, Department, Manager,

Description, Created, LastLogonDate, EmployeeType,Info

 

Now this is why I have a query

 

If I search the array using the .Contains method it finds the entry in around 2ms

 

$AllADUsers.Contains($SearchID)

 

If I then try to pull the data for the record using the "where" method it takes 1000ms

 

$AllADUsers.Where({$_.UserPrincipalName -eq "$SearchID"})

 

So why can it find the value in 2ms using "contains" method but takes more than 1000ms to read the actual record using the "where" method. Its as if it's using a different search algorithm in the "where" method.

 

In fact it was faster to use Get-AdUser for each search as this only takes 900ms

 

I've also tried other variations to no avail, such as:

 

$AllADUsers | where-object {$_.UserPrincipalName -eq $SearchID}

 

Any help gratefully received.

 

Thanks

blairkei

9 Replies

@blairkei 

I would create a hash table where UserPrincipalName is the key and the user properties are the value. 

$AllADUsers = Get-ADUser -Filter "*" -properties SAMAccountName, DisplayName, UserPrincipalName, Company, Office,Department, Manager, Description, Created, LastLogonDate, EmployeeType,Info -Server $ADServer -Credential $c

 

$AllADUsersHash = [ordered]@{}

 

$AllADUsers | ForEach-Object { $AllADUsersHash.add($_.UserPrincipalName,$_)}

 

$AllADUsersHash["Joe@microsoft.com"]

 

 

# or
$AllADUsersHash.Item("Joe@microsoft.com")

best response confirmed by blairkei (Occasional Contributor)
Solution

@Joe_Cauffiel Thanks for the alternative solution. My next step was to switch over to using a HASH array but it still does not explain why .contains is taking 2ms and .where is taking 1000ms when they are both using the same search criteria and scanning through the array. I'm wondering if I am missing an option on the ".where" somehow.

 

@blairkei After testing using the HASH array the performance of a search takes 2000ms. So this method is even slower than either reading from the ARRAY or directly reading the record from AD.

@blairkei 

The Contain method will return true or false, but to use it in your case, I guess you will need to add

$AllADUsers.UserPrincipalName.Contains($SearchID)

I guess this is related to how many items in the object, so in your case, each object of the array contain multiple items. not a 

Key= Value

if you check the Where{} statement, you are going to each item properties, and this is why you get the result.

The $AllADUsers.Contains("*user1@necad.ae*") is not equal to $AllADUsers.Where({$_.UserPrincipalName -eq "$SearchID"})

 

 

@farismalaeb Hi

 

The contain statement only takes 2ms

The where statement takes 1000ms

 

The search string is the same in both cases. So changing the contains statement won't address my issue with the where method.

@Joe_Cauffiel Hi

 

I had a small mistake in my code and the HASH solution works great. Thanks for your help

@blairkei

 

Joe's hash idea looks very useful. I've not yet wrapped my head around Hash tables to be honest but as to why the contains method is so much quicker than the Where method is that the latter has to read each object in the array and read the Userprincipal attribute until it finds an object that where the UPN matches your search string whilst the first method is doing a straight read until it finds that string and then retrieving that object. Probably not the most technically accurate explanation but that's my understanding of it..     

@PeterJ_Inobits 

I suspect the ‘contains’ method returns a faster response because it only needs to iterate through the collection until the first match, while the “where” method needs to iterate through the whole collection before it completes. Have you measure the completion times of ‘Contains’ Method where the search item is at the beginning, middle and end of the collection?  Is the completion time using the 'contains' method on the last item of collection closer to the time of the 'where' method?


This link has a good primer on Hash Tables.

https://docs.microsoft.com/en-us/powershell/scripting/learn/deep-dives/everything-about-hashtable?vi...

@Joe_Cauffiel That is exactly what I was trying to say you just articulated way better than I did..

 

Thanks for the hash table primer. I will take a look.