I have a powershell script to get a list of user and properties from our DC to write into a database. If I run this locally on DC1, against DC1, it takes about XX seconds. When I run it on DC1 against DC2 it takes about 6-7 min to complete.

What I'm not sure about is that it seems like the loop is slow, but I would have thought the only speed variance would be populating $users as it's coming from a different DC.

Any suggestions what I've missed?

Running on DC1, against DC1

20250314 14:15:58 + Defining Variables
20250314 14:15:58 + Retrieve all users from Active Directory on DC: DC01
20250314 14:16:03 + Defining Output Datatable
20250314 14:16:03 + Process each AD user and add them to the DataTable
20250314 14:16:21 + Starting ConverToDatatable
20250314 14:16:21 + + In Function ConvertTo-DataTable
20250314 14:16:22 + Connecting to SQL database

Running on DC1, against DC2

20250314 14:08:27 + Defining Variables
20250314 14:08:27 + Retrieve all users from Active Directory on DC: DC02
20250314 14:08:33 + Defining Output Datatable
20250314 14:08:33 + Process each AD user and add them to the DataTable
20250314 14:15:40 + Starting ConverToDatatable
20250314 14:15:40 + + In Function ConvertTo-DataTable
20250314 14:15:40 + Connecting to SQL database

My Script is below (Write-Host added for tracking):

# Import Active Directory module
Import-Module ActiveDirectory



Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Defining Variables"

$DomainController = "ADSERVER"  # Set Domain Controller


# Define AD properties to retrieve
$properties = @(
    'GivenName', 'Surname', 'DisplayName', 'Office', 'TelephoneNumber', 'Mail',
    'StreetAddress', 'PostOfficeBox', 'City', 'State', 'PostalCode', 'Country',
    'UserPrincipalName', 'SamAccountName', 'AccountExpirationDate', 'UserAccountControl',
    'HomePhone', 'Pager', 'MobilePhone', 'FacsimileTelephoneNumber', 'IPPhone',
    'Title', 'Department', 'Company', 'Manager', 'LogonWorkstations', 'OU',
    'DistinguishedName', 'CanonicalName', 'EmployeeNumber', 'EmployeeID',
    'LastLogonDate', 'PasswordLastSet', 'ProxyAddresses', 'whenCreated'
)


Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Retrieve all users from Active Directory on DC: $DomainController"
# Retrieve all users from Active Directory using a specific DC
$users = Get-ADUser -Server $DomainController -Filter * -Properties $properties



Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Defining Output Datatable"
# Create a DataTable for Bulk Copy
$DataTable = New-Object System.Data.DataTable

# Define table schema (all NVARCHAR except UAC)
$columnDefinitions = @{
    "FirstName" = [System.String]
    "LastName" = [System.String]
    "DisplayName" = [System.String]
    "Office" = [System.String]
    "TelephoneNumber" = [System.String]
    "Email" = [System.String]
    "StreetAddress" = [System.String]
    "POBox" = [System.String]
    "City" = [System.String]
    "State" = [System.String]
    "PostalCode" = [System.String]
    "Country" = [System.String]
    "UserLoginName" = [System.String]
    "SamAccountName" = [System.String]
    "DistinguishedName" = [System.String]  # Store full Distinguished Name
    "AccountExpires" = [System.String] # Store as string, convert in SQL
    "HomePhone" = [System.String]
    "Pager" = [System.String]
    "Mobile" = [System.String]
    "Fax" = [System.String]
    "IPPhone" = [System.String]
    "JobTitle" = [System.String]
    "Department" = [System.String]
    "Company" = [System.String]
    "Manager" = [System.String]  # Store Manager as DistinguishedName
    "LogonWorkstations" = [System.String]
    "OU" = [System.String]
    "CanonicalName" = [System.String]
    "EmployeeNumber" = [System.String]
    "EmployeeID" = [System.String]
    "LastLogin" = [System.String] # Store as string, convert in SQL
    "PasswordLastSet" = [System.String]
    "ProxyAddresses" = [System.String]
    "DateCreated" = [System.String]
    "UserAccountControl" = [System.Int32]
    "DCName" = [System.String]  # Added Domain Controller Name
}

# Add columns to DataTable
foreach ($col in $columnDefinitions.Keys) {
    $DataTable.Columns.Add($col, $columnDefinitions[$col]) | Out-Null
}

# Function to handle NULL values
function ConvertTo-DBValue($value) {
    if ($value -eq $null -or $value -eq "") { return [System.DBNull]::Value }
    return $value
}


# Function to convert object array to DataTable in bulk
function ConvertTo-DataTable($objects) {

    Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "+ In Function ConvertTo-DataTable"
    $DataTable = New-Object System.Data.DataTable
    if ($objects.Count -eq 0) { return $DataTable }  # Return empty DataTable if no data

    # Create columns from object properties dynamically
    $properties = $objects[0].PSObject.Properties | ForEach-Object { $_.Name }
    foreach ($prop in $properties) { $null = $DataTable.Columns.Add($prop) }

    # Convert all objects to DataRows in bulk
    $objects | ForEach-Object {
        $row = $DataTable.NewRow()
        foreach ($prop in $properties) { $row[$prop] = $_.$prop }
        $DataTable.Rows.Add($row)
    }

    return $DataTable
}


Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Process each AD user and add them to the DataTable"
# Convert AD Users to PSCustomObject array for fast processing
$ProcessedUsers = New-Object System.Collections.ArrayList
foreach ($user in $users) {
    $ProcessedUsers.Add([PSCustomObject]@{
        FirstName          = $user.GivenName
        LastName           = $user.Surname
        DisplayName        = $user.DisplayName
        Office             = $user.Office
        TelephoneNumber    = $user.TelephoneNumber
        Email              = $user.Mail
        StreetAddress      = $user.StreetAddress
        POBox              = ($user.PostOfficeBox -join ', ')
        City               = $user.City
        State              = $user.State
        PostalCode         = $user.PostalCode
        Country            = $user.Country
        UserLoginName      = $user.UserPrincipalName
        SamAccountName     = $user.SamAccountName
        DistinguishedName  = $user.DistinguishedName
        AccountExpires     = $user.AccountExpirationDate
        HomePhone          = $user.HomePhone
        Pager              = $user.Pager
        Mobile             = $user.MobilePhone
        Fax                = $user.FacsimileTelephoneNumber
        IPPhone            = $user.IPPhone
        JobTitle           = $user.Title
        Department         = $user.Department
        Company            = $user.Company
        Manager            = $user.Manager
        LogonWorkstations  = $user.LogonWorkstations
        OU                 = $user.OU
        CanonicalName      = $user.CanonicalName
        EmployeeNumber     = $user.EmployeeNumber
        EmployeeID         = $user.EmployeeID
        LastLogin          = $user.LastLogonDate
        PasswordLastSet    = $user.PasswordLastSet
        ProxyAddresses     = ($user.ProxyAddresses -join ', ')
        DateCreated        = $user.whenCreated
        UserAccountControl = $user.UserAccountControl
        DCName             = $DomainController
    }) | Out-Null
}


Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Starting ConverToDatatable"
# Bulk convert processed objects to DataTable
$DataTable = ConvertTo-DataTable $ProcessedUsers



Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Connecting to SQL database"

I have a powershell script to get a list of user and properties from our DC to write into a database. If I run this locally on DC1, against DC1, it takes about XX seconds. When I run it on DC1 against DC2 it takes about 6-7 min to complete.

What I'm not sure about is that it seems like the loop is slow, but I would have thought the only speed variance would be populating $users as it's coming from a different DC.

Any suggestions what I've missed?

Running on DC1, against DC1

20250314 14:15:58 + Defining Variables
20250314 14:15:58 + Retrieve all users from Active Directory on DC: DC01
20250314 14:16:03 + Defining Output Datatable
20250314 14:16:03 + Process each AD user and add them to the DataTable
20250314 14:16:21 + Starting ConverToDatatable
20250314 14:16:21 + + In Function ConvertTo-DataTable
20250314 14:16:22 + Connecting to SQL database

Running on DC1, against DC2

20250314 14:08:27 + Defining Variables
20250314 14:08:27 + Retrieve all users from Active Directory on DC: DC02
20250314 14:08:33 + Defining Output Datatable
20250314 14:08:33 + Process each AD user and add them to the DataTable
20250314 14:15:40 + Starting ConverToDatatable
20250314 14:15:40 + + In Function ConvertTo-DataTable
20250314 14:15:40 + Connecting to SQL database

My Script is below (Write-Host added for tracking):

# Import Active Directory module
Import-Module ActiveDirectory



Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Defining Variables"

$DomainController = "ADSERVER"  # Set Domain Controller


# Define AD properties to retrieve
$properties = @(
    'GivenName', 'Surname', 'DisplayName', 'Office', 'TelephoneNumber', 'Mail',
    'StreetAddress', 'PostOfficeBox', 'City', 'State', 'PostalCode', 'Country',
    'UserPrincipalName', 'SamAccountName', 'AccountExpirationDate', 'UserAccountControl',
    'HomePhone', 'Pager', 'MobilePhone', 'FacsimileTelephoneNumber', 'IPPhone',
    'Title', 'Department', 'Company', 'Manager', 'LogonWorkstations', 'OU',
    'DistinguishedName', 'CanonicalName', 'EmployeeNumber', 'EmployeeID',
    'LastLogonDate', 'PasswordLastSet', 'ProxyAddresses', 'whenCreated'
)


Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Retrieve all users from Active Directory on DC: $DomainController"
# Retrieve all users from Active Directory using a specific DC
$users = Get-ADUser -Server $DomainController -Filter * -Properties $properties



Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Defining Output Datatable"
# Create a DataTable for Bulk Copy
$DataTable = New-Object System.Data.DataTable

# Define table schema (all NVARCHAR except UAC)
$columnDefinitions = @{
    "FirstName" = [System.String]
    "LastName" = [System.String]
    "DisplayName" = [System.String]
    "Office" = [System.String]
    "TelephoneNumber" = [System.String]
    "Email" = [System.String]
    "StreetAddress" = [System.String]
    "POBox" = [System.String]
    "City" = [System.String]
    "State" = [System.String]
    "PostalCode" = [System.String]
    "Country" = [System.String]
    "UserLoginName" = [System.String]
    "SamAccountName" = [System.String]
    "DistinguishedName" = [System.String]  # Store full Distinguished Name
    "AccountExpires" = [System.String] # Store as string, convert in SQL
    "HomePhone" = [System.String]
    "Pager" = [System.String]
    "Mobile" = [System.String]
    "Fax" = [System.String]
    "IPPhone" = [System.String]
    "JobTitle" = [System.String]
    "Department" = [System.String]
    "Company" = [System.String]
    "Manager" = [System.String]  # Store Manager as DistinguishedName
    "LogonWorkstations" = [System.String]
    "OU" = [System.String]
    "CanonicalName" = [System.String]
    "EmployeeNumber" = [System.String]
    "EmployeeID" = [System.String]
    "LastLogin" = [System.String] # Store as string, convert in SQL
    "PasswordLastSet" = [System.String]
    "ProxyAddresses" = [System.String]
    "DateCreated" = [System.String]
    "UserAccountControl" = [System.Int32]
    "DCName" = [System.String]  # Added Domain Controller Name
}

# Add columns to DataTable
foreach ($col in $columnDefinitions.Keys) {
    $DataTable.Columns.Add($col, $columnDefinitions[$col]) | Out-Null
}

# Function to handle NULL values
function ConvertTo-DBValue($value) {
    if ($value -eq $null -or $value -eq "") { return [System.DBNull]::Value }
    return $value
}


# Function to convert object array to DataTable in bulk
function ConvertTo-DataTable($objects) {

    Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "+ In Function ConvertTo-DataTable"
    $DataTable = New-Object System.Data.DataTable
    if ($objects.Count -eq 0) { return $DataTable }  # Return empty DataTable if no data

    # Create columns from object properties dynamically
    $properties = $objects[0].PSObject.Properties | ForEach-Object { $_.Name }
    foreach ($prop in $properties) { $null = $DataTable.Columns.Add($prop) }

    # Convert all objects to DataRows in bulk
    $objects | ForEach-Object {
        $row = $DataTable.NewRow()
        foreach ($prop in $properties) { $row[$prop] = $_.$prop }
        $DataTable.Rows.Add($row)
    }

    return $DataTable
}


Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Process each AD user and add them to the DataTable"
# Convert AD Users to PSCustomObject array for fast processing
$ProcessedUsers = New-Object System.Collections.ArrayList
foreach ($user in $users) {
    $ProcessedUsers.Add([PSCustomObject]@{
        FirstName          = $user.GivenName
        LastName           = $user.Surname
        DisplayName        = $user.DisplayName
        Office             = $user.Office
        TelephoneNumber    = $user.TelephoneNumber
        Email              = $user.Mail
        StreetAddress      = $user.StreetAddress
        POBox              = ($user.PostOfficeBox -join ', ')
        City               = $user.City
        State              = $user.State
        PostalCode         = $user.PostalCode
        Country            = $user.Country
        UserLoginName      = $user.UserPrincipalName
        SamAccountName     = $user.SamAccountName
        DistinguishedName  = $user.DistinguishedName
        AccountExpires     = $user.AccountExpirationDate
        HomePhone          = $user.HomePhone
        Pager              = $user.Pager
        Mobile             = $user.MobilePhone
        Fax                = $user.FacsimileTelephoneNumber
        IPPhone            = $user.IPPhone
        JobTitle           = $user.Title
        Department         = $user.Department
        Company            = $user.Company
        Manager            = $user.Manager
        LogonWorkstations  = $user.LogonWorkstations
        OU                 = $user.OU
        CanonicalName      = $user.CanonicalName
        EmployeeNumber     = $user.EmployeeNumber
        EmployeeID         = $user.EmployeeID
        LastLogin          = $user.LastLogonDate
        PasswordLastSet    = $user.PasswordLastSet
        ProxyAddresses     = ($user.ProxyAddresses -join ', ')
        DateCreated        = $user.whenCreated
        UserAccountControl = $user.UserAccountControl
        DCName             = $DomainController
    }) | Out-Null
}


Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Starting ConverToDatatable"
# Bulk convert processed objects to DataTable
$DataTable = ConvertTo-DataTable $ProcessedUsers



Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Connecting to SQL database"

• powershell
• active-directory

• What you're seeing is expected, querying a remote DC will always be slower than querying locally. The reason why the loop is slower is due to lazily evaluated properties. – Santiago Squarzon Commented Mar 14 at 3:29
• Can you exapand what you mean by lazily evaluated properties? I would have through that $users = Get-ADUser would bring all the data into an object for local processing? – Stephen Pefanis Commented Mar 14 at 4:59
• Would surprise me if that were the issue – lazily retrieving each property from AD would be quite the pessimization for PowerShell to do, when it already can receive all of them upfront from the search query... (though I only do this through LDAP, for certain reasons haven't had the possibility to compare PowerShell myself) – grawity_u1686 Commented Mar 14 at 6:33
• As aside, Get-ADUser by default already returns objects with these properties: DistinguishedName, Enabled, GivenName, Name, ObjectClass, ObjectGUID, SamAccountName, SID, Surname, UserPrincipalName, so you only have to ask for the rest in $properties – Theo Commented Mar 14 at 10:28
• I guess the lazy evalution causes AD open and close the connection for each user, try using the PowerShell pipeline and one at the time processing: Get-ADUser ... | ForEach-Object { <your datatable conversion> } | <update database> – iRon Commented Mar 14 at 16:05

1 Answer 1

There could be several reasons why DC2 is performing slower than DC1.

But as you suggest, since the issue appears to be with the "Process each AD user and add them to the DataTable" step (# Convert AD Users to PSCustomObject array for faster processing), I would instead focus on ensuring that the AD user data is returned in the desired format right away, rather than iterating through each user in a foreach loop just to rebuild the properties.

Example enhancements

This is an example on how I would do it.
(I cannot fully test the changes as I have no access to an actual DC at the moment)

# Function to handle NULL values
function ConvertTo-DBValue($value) {
    if ($value -eq $null -or $value -eq "") { return [System.DBNull]::Value }
    return $value
}


# Function to convert object array to DataTable in bulk
function ConvertTo-DataTable($objects) {

    Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "+ In Function ConvertTo-DataTable"
    $DataTable = New-Object System.Data.DataTable
    if ($objects.Count -eq 0) { return $DataTable }  # Return empty DataTable if no data

    # Create columns from object properties dynamically
    $properties = $objects[0].PSObject.Properties | ForEach-Object { $_.Name }
    foreach ($prop in $properties) { $null = $DataTable.Columns.Add($prop) }

    # Convert all objects to DataRows in bulk
    $objects | ForEach-Object {
        $row = $DataTable.NewRow()
        foreach ($prop in $properties) { $row[$prop] = $_.$prop }
        $DataTable.Rows.Add($row)
    }

    return $DataTable
}

# Import Active Directory module
Import-Module ActiveDirectory

Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Defining Variables"

$DomainController = "ADSERVER"  # Set Domain Controller


# Define AD properties to retrieve
$properties = @(
    'GivenName', 'Surname', 'DisplayName', 'Office', 'TelephoneNumber', 'Mail',
    'StreetAddress', 'PostOfficeBox', 'City', 'State', 'PostalCode', 'Country',
    'UserPrincipalName', 'SamAccountName', 'AccountExpirationDate', 'UserAccountControl',
    'HomePhone', 'Pager', 'MobilePhone', 'FacsimileTelephoneNumber', 'IPPhone',
    'Title', 'Department', 'Company', 'Manager', 'LogonWorkstations', 'OU',
    'DistinguishedName', 'CanonicalName', 'EmployeeNumber', 'EmployeeID',
    'LastLogonDate', 'PasswordLastSet', 'ProxyAddresses', 'whenCreated'
)

# Format the received AD user properties
$FormattedProperties = @(
    @{label = "FirstName"          ; expression={$_.GivenName}}
    @{label = "LastName"           ; expression={$_.Surname}}
    @{label = "DisplayName"        ; expression={$_.DisplayName}}
    @{label = "Office"             ; expression={$_.Office}}
    @{label = "TelephoneNumber"    ; expression={$_.TelephoneNumber}}
    @{label = "Email"              ; expression={$_.Mail}}
    @{label = "StreetAddress"      ; expression={$_.StreetAddress}}
    @{label = "POBox"              ; expression={($_.PostOfficeBox -join ', ')}}
    @{label = "City"               ; expression={$_.City}}
    @{label = "State"              ; expression={$_.State}}
    @{label = "PostalCode"         ; expression={$_.PostalCode}}
    @{label = "Country"            ; expression={$_.Country}}
    @{label = "UserLoginName"      ; expression={$_.UserPrincipalName}}
    @{label = "SamAccountName"     ; expression={$_.SamAccountName}}
    @{label = "DistinguishedName"  ; expression={$_.DistinguishedName}}
    @{label = "AccountExpires"     ; expression={$_.AccountExpirationDate}}
    @{label = "HomePhone"          ; expression={$_.HomePhone}}
    @{label = "Pager"              ; expression={$_.Pager}}
    @{label = "Mobile"             ; expression={$_.MobilePhone}}
    @{label = "Fax"                ; expression={$_.FacsimileTelephoneNumber}}
    @{label = "IPPhone"            ; expression={$_.IPPhone}}
    @{label = "JobTitle"           ; expression={$_.Title}}
    @{label = "Department"         ; expression={$_.Department}}
    @{label = "Company"            ; expression={$_.Company}}
    @{label = "Manager"            ; expression={$_.Manager}}
    @{label = "LogonWorkstations"  ; expression={$_.LogonWorkstations}}
    @{label = "OU"                 ; expression={$_.OU}}
    @{label = "CanonicalName"      ; expression={$_.CanonicalName}}
    @{label = "EmployeeNumber"     ; expression={$_.EmployeeNumber}}
    @{label = "EmployeeID"         ; expression={$_.EmployeeID}}
    @{label = "LastLogin"          ; expression={$_.LastLogonDate}}
    @{label = "PasswordLastSet"    ; expression={$_.PasswordLastSet}}
    @{label = "ProxyAddresses"     ; expression={($_.ProxyAddresses -join ', ')}}
    @{label = "DateCreated"        ; expression={$_.whenCreated}}
    @{label = "UserAccountControl" ; expression={$_.UserAccountControl}}
)

Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Retrieve all users from Active Directory on DC: $DomainController"
# Retrieve all users from Active Directory using a specific DC
$users = Get-ADUser -Server $DomainController -Filter * -Properties $properties | Select-Object -Property $FormattedProperties

# Added Domain Controller Name
$users | Add-Member -MemberType NoteProperty -Name "DCName" -Value $DomainController



Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Defining Output Datatable"
# Create a DataTable for Bulk Copy
$DataTable = New-Object System.Data.DataTable

# Define table schema (all NVARCHAR except UAC)
$columnDefinitions = @{
    "FirstName" = [System.String]
    "LastName" = [System.String]
    "DisplayName" = [System.String]
    "Office" = [System.String]
    "TelephoneNumber" = [System.String]
    "Email" = [System.String]
    "StreetAddress" = [System.String]
    "POBox" = [System.String]
    "City" = [System.String]
    "State" = [System.String]
    "PostalCode" = [System.String]
    "Country" = [System.String]
    "UserLoginName" = [System.String]
    "SamAccountName" = [System.String]
    "DistinguishedName" = [System.String]  # Store full Distinguished Name
    "AccountExpires" = [System.String] # Store as string, convert in SQL
    "HomePhone" = [System.String]
    "Pager" = [System.String]
    "Mobile" = [System.String]
    "Fax" = [System.String]
    "IPPhone" = [System.String]
    "JobTitle" = [System.String]
    "Department" = [System.String]
    "Company" = [System.String]
    "Manager" = [System.String]  # Store Manager as DistinguishedName
    "LogonWorkstations" = [System.String]
    "OU" = [System.String]
    "CanonicalName" = [System.String]
    "EmployeeNumber" = [System.String]
    "EmployeeID" = [System.String]
    "LastLogin" = [System.String] # Store as string, convert in SQL
    "PasswordLastSet" = [System.String]
    "ProxyAddresses" = [System.String]
    "DateCreated" = [System.String]
    "UserAccountControl" = [System.Int32]
    "DCName" = [System.String]  # Added Domain Controller Name
}

# Add columns to DataTable
foreach ($col in $columnDefinitions.Keys) {
    $DataTable.Columns.Add($col, $columnDefinitions[$col]) | Out-Null
}

Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Starting ConverToDatatable"
# Bulk convert processed objects to DataTable
$DataTable = ConvertTo-DataTable $users


Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Connecting to SQL database"

Explanation / Summary

1. Not part of the problem, but I've moved your functions to the top for best practices reasons.

2. I've taken your foreach loop and instead made it into an array ($FormattedProperties), which contains the formatting you wanted for your finished object.

3. Then for the $users = Get-ADUser row, I've added | Select-Object -Property $FormattedProperties to have it returned as you wanted it in the end.

4. After the $users variable is populated, I also added the DCName property with a value of $DomainController as this property/value is not from the Get-ADUser cmdlet.

5. Then finally at the end of the script I've just switched to directly target $users here $DataTable = ConvertTo-DataTable $users

Hope this helps and good luck!