5

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?

  • Hi @SumitK why are you fetching the renderings here, this looks like a bulky task. – samridhi sachdeva Nov 20 '19 at 9:49
  • If possible can you try omitting this part of the script ? – samridhi sachdeva Nov 20 '19 at 9:49
  • @samridhisachdeva I am fetching the rendering to add one column named URL in report, but I can try omitting that section. – SumitK Nov 20 '19 at 9:58
  • Not an answer for you, but are you sure you want to be listing the item as publishable if '__Never Publish' -eq 1? Looks the opposite to me. – James Walford Nov 28 '19 at 13:47
5

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
  • I tried to execute this script as suggested by you but I am getting a complete new error "Find-Item : Cannot bind parameter 'Criteria'. Cannot create object of type "Cognifide.PowerShell.Commandlets.Data.Search.SearchCriteria". Cannot convert value "GreaterThan" to type "Cognifide.PowerShell.Commandlets.Data.Search.FilterType". Error: "Unable to match the identifier name GreaterThan to a valid enumerator name." Any idea about this error. Could not find anything on google as well. – SumitK Nov 20 '19 at 14:03
  • GreaterThan is only available from SPE 6.0. It can also be done using InclusiveRange. I've updated the example. – Mark Lowe Nov 20 '19 at 15:06
  • Still this is not working. Giving me error as follows - "Find-Item : The Lucene query that was generated contains too many clauses. The maximum number of clauses that are allowed in a query is specified in the ‘Sitecore.ContentSearch.Lucene.DefaultIndexConfiguration.config’ configuration file, in the ‘ContentSearch.LuceneQueryClauseCount’ setting. The value is currently set to ‘4096’. You should either make the search expression more specific or increase the value specified in the ‘ContentSearch.LuceneQueryClauseCount’ setting." – SumitK Nov 22 '19 at 12:36
  • You seem to have a large amount of content items which is causing the exception with the startsWith filter. Try replacing the StartsWith filter line with this one: using @{Filter = "Equals"; Field = "_path"; Value = "0de95ae441ab4d019eb067441b7c2450" } where the guid needs to be the one of your site root item. – Mark Lowe Nov 25 '19 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.