Performance issue with Sitecore Powershell Script

Question

I am working on SPE (Sitecore.PowerShell.Extensions-5.1) with Sitecore 9.0.1 where I have one script which creates a report of items in approved workflow state. But the execution of the script is taking more time, sometime it shows the timeout exception and need to trigger again.

In the below script there are nested foreach loops which I think is a root cause for this slowness of execution.

$workflowStateID = Get-Item -Path master: -ID "{C674D9DF-B5EA-4F3B-8871-F80B2F214F28}"
$referringItems = Get-ItemReferrer -Item $workflowStateID | Where-Object { $_.__Updated -gt [datetime]::Now.AddDays(-30) -and $_.__Created -gt [datetime]::Now.AddDays(-30)}

$listofWorkflowItems = @()
Foreach($item in $referringItems) 
{ 
    $itemPaths = $item.Paths.FullPath
    if($item.Paths.FullPath.contains('/sitecore/content/abc-website'))
    {
        $rendering = $item | Get-Rendering        
        $addInList = New-Object System.Object
        $addInList | Add-Member -type NoteProperty -name ID -Value $item.ID
        $addInList | Add-Member -type NoteProperty -name Path -Value $item.FullPath
        $addInList | Add-Member -type NoteProperty -name ItemCreateDate -Value $item.Created        
        if($item.'__Never publish' -eq 1)
        {
            $addInList | Add-Member -type NoteProperty -name Publishable -Value 'True'
        }
        else{
            $addInList | Add-Member -type NoteProperty -name Publishable -Value 'False'
        }                
        $workflowdetailList = Get-ItemWorkflowEvent -Id $item.ID | Where-Object { $_.NewState -eq '{C674D9DF-B5EA-4F3B-8871-F80B2F214F28}' } | Sort-Object -Property Date -Descending
        Foreach($workflow in $workflowdetailList){
                $addInList | Add-Member -type NoteProperty -name WorkflowApprover -Value $workflow.User
                $addInList | Add-Member -type NoteProperty -name ApprovedDate -Value $workflow.Date
                break
        }       
        if($rendering){
            $addInList | Add-Member -type NoteProperty -name URL -Value $item.FullPath.Replace("/sitecore/content/abc-website/home","https://abc.com") 
        }        
        $listofWorkflowItems += $addInList
    }
}
$listofWorkflowItems | Show-ListView

Is there any other approach by which execution time can be reduced or the performance can be improved?

Answer 1

You are always getting ALL items in final state through LinkDB first and then filtering them down by date. This query will continue to become slower and slower.

What you can do is use the sitecore_master_index to query for your referring items:

$criteria = @(
    @{Filter = "Equals"; Field = "__workflow_state"; Value = "c674d9dfb5ea4f3b8871f80b2f214f28" },
    @{Filter = "Equals"; Field = "_path"; Value = "0de95ae441ab4d019eb067441b7c2450" },  # Replace with Short ID of /sitecore/content/mysite
    @{Filter = "InclusiveRange"; Field = "__smallcreateddate"; Value = [string[]]@([datetime]::Today.AddDays(-30).ToString("yyyy-MM-ddTHH:mm:ssZ"), [datetime]::Now.ToString("yyyy-MM-ddTHH:mm:ssZ"))},
    @{Filter = "InclusiveRange"; Field = "__smallupdateddate"; Value = [string[]]@([datetime]::Today.AddDays(-30).ToString("yyyy-MM-ddTHH:mm:ssZ"), [datetime]::Now.ToString("yyyy-MM-ddTHH:mm:ssZ"))}
)
$props = @{
    Index = "sitecore_master_index"
    Criteria = $criteria
}

$referringItems = Find-Item @props | Initialize-Item

$listofWorkflowItems = @()
Foreach($item in $referringItems) 
{
    $rendering = $item | Get-Rendering        
        $addInList = New-Object System.Object
        $addInList | Add-Member -type NoteProperty -name ID -Value $item.ID
        $addInList | Add-Member -type NoteProperty -name Path -Value $item.FullPath
        $addInList | Add-Member -type NoteProperty -name ItemCreateDate -Value $item.Created        
        if($item.'__Never publish' -eq 1)
        {
            $addInList | Add-Member -type NoteProperty -name Publishable -Value 'True'
        }
        else{
            $addInList | Add-Member -type NoteProperty -name Publishable -Value 'False'
        }                
        $workflowdetailList = Get-ItemWorkflowEvent -Id $item.ID | Where-Object { $_.NewState -eq '{C674D9DF-B5EA-4F3B-8871-F80B2F214F28}' } | Sort-Object -Property Date -Descending
        Foreach($workflow in $workflowdetailList){
                $addInList | Add-Member -type NoteProperty -name WorkflowApprover -Value $workflow.User
                $addInList | Add-Member -type NoteProperty -name ApprovedDate -Value $workflow.Date
                break
        }       
        if($rendering){
            $addInList | Add-Member -type NoteProperty -name URL -Value $item.FullPath.Replace("/sitecore/content/abc-website/home","https://abc.com") 
        }        
        $listofWorkflowItems += $addInList
}
$listofWorkflowItems | Show-ListView