如何提升SharePoint Online同步AD数据脚本的性能？

我编写了一个将AD数据导出至SharePoint Online的脚本，核心逻辑为：当AD中有变更时同步更新SharePoint对应项；若SharePoint中无对应项，则创建新项。现希望获取该脚本的性能优化方法，原脚本如下：

Import-Module PnP.PowerShell

$siteURL = "https://mytenant.sharepoint.com/sites/sitename"
$primaryDomain = "mydomain.com"
$clientId = "5502516a-48c3-4b7d-b7a7-c42809bdf9a0"
$pfxPath = "C:\Users\f034067\OneDrive - Aethra Sistemas Automotivos S.A\Área de Trabalho\Pastas\Scripts\Scripts PowerShell\SharePoint Script\certificate.pfx"
$senhaPath = "C:\Users\f034067\OneDrive - Aethra Sistemas Automotivos S.A\Área de Trabalho\Pastas\Scripts\Scripts PowerShell\SharePoint Script\senha.txt"

try {
    $secureCertPassword = Get-Content $senhaPath | ConvertTo-SecureString
    $certificate = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($pfxPath, $secureCertPassword)
    
    $connectSplat = @{
        Url                 = $siteURL
        ClientId            = $clientId
        Tenant              = $primaryDomain
        CertificatePath     = $pfxPath
        CertificatePassword = $secureCertPassword
    }
    
    Connect-PnPOnline @connectSplat
    Write-Host "Autenticado no site SharePoint: $siteURL"
} catch {
    Write-Host "Não foi possível autenticar no site ou ler o certificado: $_"
    exit
}

$existingItems = Get-PnPListItem -List "Membros do grupo" -Fields "Colaborador", "Grupo"
$existingItemsHash = @{}
foreach ($item in $existingItems) {
    $key = "$($item["Colaborador"])|$($item["Grupo"])"
    $existingItemsHash[$key] = $item
}

$getADGroupSplat = @{
    LDAPFilter = '(&(|(name=F_*)(name=G_*)(name=CAX_*))(groupType:1.2.840.113556.1.4.803:=2147483648))'
    Properties = 'Name', 'Info', 'Description', 'Member'
}

$groups = Get-ADGroup @getADGroupSplat
Start-PnPBatch

foreach ($gp in $groups) {
    Write-Host "Processando o grupo --> $($gp.Name)" -ForegroundColor DarkGreen

    if ($gp.Info -ne 'sem aprovador' -or $gp.Info -ne 'Sem aprovador') {
        $aprList = @()
        $aprovadores = if ($gp.Info) {
            $gp.Info -split "`r`n" | ForEach-Object { $_.Trim() } 
        } else { 
            @() 
        }

        foreach ($apr in $aprovadores) {
            if (-not [string]::IsNullOrEmpty($apr)) {
                $userSplat = @{
                    LDAPFilter = "(mail=$apr)"
                    Properties = 'mail'
                }
                
                $aprUser = Get-ADUser @userSplat
                if ($aprUser) {
                    $aprList += $aprUser.mail
                }
            }
        }

        $groupMemberSplat = @{
            Identity = $gp.Name
        }
        
        $members = Get-ADGroupMember @groupMemberSplat

        foreach ($mb in $members) {
            if ($mb.objectClass -eq "user") {
                $userDetailSplat = @{
                    LDAPFilter = "(samAccountName=$($mb.SamAccountName))"
                    Properties = 'DisplayName', 'Enabled'
                }
                
                $user = Get-ADUser @userDetailSplat
                if ($null -ne $user -and $user.Enabled) {
                    $key = "$($user.DisplayName)|$($gp.Name)"

                    $listItemSplat = @{
                        List   = "listname"
                        Values = @{
                            "Colaborador"     = $user.DisplayName
                            "Aprovadores"     = $aprList
                            "Grupo"           = $gp.Name
                            "Caminho"         = $gp.Description
                        }
                    }
                    
                    try {
                        if ($existingItemsHash.ContainsKey($key)) {
                            
                            Set-PnPListItem -List "listname" -Identity $existingItemsHash[$key].Id -Values $listItemSplat.Values -Batch
                            Write-Host "Item atualizado para o usuário $($user.DisplayName) no grupo $($gp.Name)"
                        } else {
                            
                            Add-PnPListItem -List "listname" -Values $listItemSplat.Values -Batch
                            Write-Host "Item adicionado para o usuário $($user.DisplayName) no grupo $($gp.Name)"
                        }
                    } catch {
                        Write-Host "Erro ao processar o usuário $($user.Name): $_"
                    }
                }
            }
        }
    }
    else {
        Write-Host "Grupo definido para não ter aprovadores. Logo, não faz sentido tê-lo na gestão de acesso."
    }    
}

Invoke-PnPBatch
Disconnect-PnPOnline
Write-Host "Desconectado do SharePoint."

1. 修复逻辑判断错误（提升执行效率） #

原脚本中组过滤逻辑存在逻辑错误：if ($gp.Info -ne 'sem aprovador' -or $gp.Info -ne 'Sem aprovador')，该条件永远为true（一个值不可能同时等于两个不同字符串），会导致无需处理的组也进入循环。修改为：

if ($gp.Info -ne 'sem aprovador' -and $gp.Info -ne 'Sem aprovador') {
    # 原有处理逻辑
}

2. 减少AD重复查询，批量获取属性 #

原脚本存在多次重复AD查询，优化方式为批量查询用户属性：

• 收集所有审批人邮箱后，一次性构建LDAP过滤器批量查询
• 收集组成员的SamAccountName后，批量查询用户详情，避免循环调用Get-ADUser

示例修改：

# 批量处理审批人查询
$aprovadores = if ($gp.Info) {
    $gp.Info -split "`r`n" | ForEach-Object { $_.Trim() } | Where-Object { -not [string]::IsNullOrEmpty($_) }
} else { 
    @() 
}

$aprList = @()
if ($aprovadores.Count -gt 0) {
    $mailFilter = $aprovadores | ForEach-Object { "(mail=$_)" } | Join-String -Separator "|"
    $aprList = Get-ADUser -LDAPFilter "($mailFilter)" -Properties mail | Select-Object -ExpandProperty mail
}

# 批量查询组成员详情
$members = Get-ADGroupMember -Identity $gp.Name | Where-Object { $_.objectClass -eq "user" }
$samAccountNames = $members.SamAccountName
$userHash = @{}
if ($samAccountNames.Count -gt 0) {
    $samFilter = $samAccountNames | ForEach-Object { "(samAccountName=$_)" } | Join-String -Separator "|"
    $users = Get-ADUser -LDAPFilter "($samFilter)" -Properties DisplayName, Enabled
    foreach ($u in $users) {
        $userHash[$u.SamAccountName] = $u
    }
}

foreach ($mb in $members) {
    $user = $userHash[$mb.SamAccountName]
    if ($null -ne $user -and $user.Enabled) {
        # 后续同步逻辑
    }
}

3. 优化SharePoint列表查询与哈希表键唯一性 #

• 原脚本用DisplayName|Grupo作为哈希表键，DisplayName可能重复，建议改用AD用户的UserPrincipalName（需在SharePoint列表中新增该字段）结合组名作为唯一键
• 给Get-PnPListItem添加-PageSize参数，提升大列表的查询效率

示例修改：

$existingItems = Get-PnPListItem -List "Membros do grupo" -Fields "Colaborador", "Grupo", "UserPrincipalName" -PageSize 500
$existingItemsHash = @{}
foreach ($item in $existingItems) {
    $key = "$($item["UserPrincipalName"])|$($item["Grupo"])"
    $existingItemsHash[$key] = $item
}

4. 优化数组操作，避免频繁创建新数组 #

原脚本中$aprList += $aprUser.mail会每次创建新数组，效率低下，改用System.Collections.ArrayList：

$aprList = New-Object System.Collections.ArrayList
# 添加元素时使用
[void]$aprList.Add($aprUser.mail)

5. 拆分Batch操作，避免超量 #

PnP Batch有默认大小限制（通常为1000条），数据量大时建议拆分Batch，避免超时或失败：

$maxBatchSize = 500
$batch = Start-PnPBatch
$batchCount = 0

foreach ($mb in $members) {
    # 处理逻辑...
    if ($existingItemsHash.ContainsKey($key)) {
        Set-PnPListItem -List "listname" -Identity $existingItemsHash[$key].Id -Values $listItemSplat.Values -Batch $batch
    } else {
        Add-PnPListItem -List "listname" -Values $listItemSplat.Values -Batch $batch
    }
    $batchCount++

    if ($batchCount -ge $maxBatchSize) {
        Invoke-PnPBatch -Batch $batch
        $batch = Start-PnPBatch
        $batchCount = 0
    }
}

if ($batchCount -gt 0) {
    Invoke-PnPBatch -Batch $batch
}

6. 移除冗余代码 #

原脚本加载了$certificate变量但未使用，直接删除该变量的初始化代码即可。

7. 增量同步，减少全量查询 #

添加时间戳记录上次同步时间，仅查询AD中该时间点之后变更的对象，避免每次全量同步：

$lastSyncPath = "last_sync_timestamp.txt"
$lastSyncTime = Get-Content $lastSyncPath -ErrorAction SilentlyContinue

if ($lastSyncTime) {
    $getADGroupSplat["LDAPFilter"] = "(&$($getADGroupSplat["LDAPFilter"])(whenChanged>=$lastSyncTime))"
}

$groups = Get-ADGroup @getADGroupSplat

# 同步完成后更新时间戳
Get-Date -Format "yyyyMMddHHmmss" | Set-Content $lastSyncPath

内容的提问来源于stack exchange，提问作者Thiago Luan