PowerShell跨域控制器AD查询：Foreach循环性能异常求助

问题现象 #

从本地DC（DC1）获取用户数据并处理仅需数十秒，但在DC1上针对远程DC（DC2）执行时，循环处理用户数据的阶段耗时长达6-7分钟，远超出预期。日志显示用户数据获取阶段耗时无明显差异，瓶颈集中在后续的循环处理环节。

核心原因分析 #

1. AD对象的延迟加载触发频繁远程请求 #

Get-ADUser返回的远程DC用户对象，即便指定了-Properties参数，部分属性仍采用延迟加载机制——首次访问属性时，PowerShell会向远程DC发起LDAP请求获取属性值。循环中逐个访问用户属性时，每个属性的首次访问都会产生一次网络往返，大量用户的累积请求直接导致处理时间暴增。本地DC因网络延迟极低，该问题被掩盖。

2. ArrayList集合操作的低效 #

使用System.Collections.ArrayList的Add方法时，每次添加元素都可能触发内部数组扩容，本身存在性能开销。在远程场景下，该开销与网络延迟叠加，进一步拉长了处理时间。

3. 多值属性的重复远程访问 #

循环中对PostOfficeBox、ProxyAddresses等多值属性执行-join操作时，若属性未被预加载，每次访问都会触发远程调用，加剧了网络往返次数。

针对性优化方案 #

1. 提前转换AD对象为本地PSObject，消除延迟加载 #

在获取用户数据后，立即用Select-Object提取所有需要的属性，将AD原生对象转换为普通PSObject，确保所有属性一次性加载到本地：

$users = Get-ADUser -Server $DomainController -Filter * -Properties $properties |
    Select-Object -Property $properties

2. 替换ArrayList为高效泛型集合 #

用System.Collections.Generic.List[PSObject]替代ArrayList，前者的Add方法性能远高于后者：

$ProcessedUsers = New-Object System.Collections.Generic.List[PSObject]

3. 直接填充DataTable，减少中间转换 #

跳过创建PSCustomObject的中间步骤，直接在循环中向DataTable添加行，消除二次转换的开销：

Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Process each AD user and add them to the DataTable"
foreach ($user in $users) {
    $row = $DataTable.NewRow()
    $row.FirstName = ConvertTo-DBValue $user.GivenName
    $row.LastName = ConvertTo-DBValue $user.Surname
    $row.DisplayName = ConvertTo-DBValue $user.DisplayName
    $row.Office = ConvertTo-DBValue $user.Office
    $row.TelephoneNumber = ConvertTo-DBValue $user.TelephoneNumber
    $row.Email = ConvertTo-DBValue $user.Mail
    $row.StreetAddress = ConvertTo-DBValue $user.StreetAddress
    $row.POBox = ConvertTo-DBValue ($user.PostOfficeBox -join ', ')
    $row.City = ConvertTo-DBValue $user.City
    $row.State = ConvertTo-DBValue $user.State
    $row.PostalCode = ConvertTo-DBValue $user.PostalCode
    $row.Country = ConvertTo-DBValue $user.Country
    $row.UserLoginName = ConvertTo-DBValue $user.UserPrincipalName
    $row.SamAccountName = ConvertTo-DBValue $user.SamAccountName
    $row.DistinguishedName = ConvertTo-DBValue $user.DistinguishedName
    $row.AccountExpires = ConvertTo-DBValue $user.AccountExpirationDate
    $row.HomePhone = ConvertTo-DBValue $user.HomePhone
    $row.Pager = ConvertTo-DBValue $user.Pager
    $row.Mobile = ConvertTo-DBValue $user.MobilePhone
    $row.Fax = ConvertTo-DBValue $user.FacsimileTelephoneNumber
    $row.IPPhone = ConvertTo-DBValue $user.IPPhone
    $row.JobTitle = ConvertTo-DBValue $user.Title
    $row.Department = ConvertTo-DBValue $user.Department
    $row.Company = ConvertTo-DBValue $user.Company
    $row.Manager = ConvertTo-DBValue $user.Manager
    $row.LogonWorkstations = ConvertTo-DBValue $user.LogonWorkstations
    $row.OU = ConvertTo-DBValue $user.OU
    $row.CanonicalName = ConvertTo-DBValue $user.CanonicalName
    $row.EmployeeNumber = ConvertTo-DBValue $user.EmployeeNumber
    $row.EmployeeID = ConvertTo-DBValue $user.EmployeeID
    $row.LastLogin = ConvertTo-DBValue $user.LastLogonDate
    $row.PasswordLastSet = ConvertTo-DBValue $user.PasswordLastSet
    $row.ProxyAddresses = ConvertTo-DBValue ($user.ProxyAddresses -join ', ')
    $row.DateCreated = ConvertTo-DBValue $user.whenCreated
    $row.UserAccountControl = ConvertTo-DBValue $user.UserAccountControl
    $row.DCName = ConvertTo-DBValue $DomainController
    $DataTable.Rows.Add($row)
}

此方式可直接省略ConvertTo-DataTable函数，避免不必要的对象转换。

4. 预处理多值属性 #

若需保留中间对象，可在Select-Object阶段提前处理多值属性，避免循环中重复操作：

$users = Get-ADUser -Server $DomainController -Filter * -Properties $properties |
    Select-Object -Property *,
        @{Name='PostOfficeBoxStr'; Expression={$_.PostOfficeBox -join ', '}},
        @{Name='ProxyAddressesStr'; Expression={$_.ProxyAddresses -join ', '}} |
    Select-Object -Property $properties, PostOfficeBoxStr, ProxyAddressesStr

循环中直接使用预处理后的PostOfficeBoxStr和ProxyAddressesStr即可。

优化后脚本示例 #

# 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 users and convert to local PSObject to eliminate lazy loading
$users = Get-ADUser -Server $DomainController -Filter * -Properties $properties |
    Select-Object -Property $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
}

Write-Host  (Get-Date -Format "yyyyMMdd HH:mm:ss") + "Process each AD user and add them to the DataTable"
# Directly populate DataTable without intermediate PSCustomObject
foreach ($user in $users) {
    $row = $DataTable.NewRow()
    $row.FirstName = ConvertTo-DBValue $user.GivenName
    $row.LastName = ConvertTo-DBValue $user.Surname
    $row.DisplayName = ConvertTo-DBValue $user.DisplayName
    $row.Office = ConvertTo-DBValue $user.Office
    $row.TelephoneNumber = ConvertTo-DBValue $user.TelephoneNumber
    $row.Email = ConvertTo-DBValue $user.Mail
    $row.StreetAddress = ConvertTo-DBValue $user.StreetAddress
    $row.POBox = ConvertTo-DBValue ($user.PostOfficeBox -join ', ')
    $row.City = ConvertTo-DBValue $user.City
    $row.State = ConvertTo-DBValue $user.State
    $row.PostalCode = ConvertTo-DBValue $user.PostalCode
    $row.Country = ConvertTo-DBValue $user.Country
    $row.UserLoginName = ConvertTo-DBValue $user.UserPrincipalName
    $row.SamAccountName = ConvertTo-DBValue $user.SamAccountName
    $row.DistinguishedName = ConvertTo-DBValue $user.DistinguishedName
    $row.AccountExpires = ConvertTo-DBValue $user.AccountExpirationDate
    $row.HomePhone = ConvertTo-DBValue $user.HomePhone
    $row.Pager = ConvertTo-DBValue $user.Pager
    $row.Mobile = ConvertTo-DBValue $user.MobilePhone
    $row.Fax = ConvertTo-DBValue $user.FacsimileTelephoneNumber
    $row.IPPhone = ConvertTo-DBValue $user.IPPhone
    $row.JobTitle = ConvertTo-DBValue $user.Title
    $row.Department = ConvertTo-DBValue $user.Department
    $row.Company = ConvertTo-DBValue $user.Company
    $row.Manager = ConvertTo-DBValue $user.Manager
    $row.LogonWorkstations = ConvertTo-DBValue $user.LogonWorkstations
    $row.OU = ConvertTo-DBValue $user.OU
    $row.CanonicalName = ConvertTo-DBValue $user.CanonicalName
    $row.EmployeeNumber = ConvertTo-DBValue $user.EmployeeNumber
    $row.EmployeeID = ConvertTo-DBValue $user.EmployeeID
    $row.LastLogin = ConvertTo-DBValue $user.LastLogonDate
    $row.PasswordLastSet = ConvertTo-DBValue $user.PasswordLastSet
    $row.ProxyAddresses = ConvertTo-DBValue ($user.ProxyAddresses -join ', ')
    $row.DateCreated = ConvertTo-DBValue $user.whenCreated
    $row.UserAccountControl = ConvertTo-DBValue $user.UserAccountControl
    $row.DCName = ConvertTo-DBValue $DomainController
    $DataTable.Rows.Add($row)
}

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

内容的提问来源于stack exchange，提问作者Stephen Pefanis