Solve Sitecore workbox performance issue

Question

In the Sitecore workbox, (Sitecore.Shell.Applications.Workbox), in the DisplayStates(IWorkflow workflow, XmlControl placeholder) method, Sitecore uses following method to retrieve items in a particular workflowstate.

DataUri[] items = this.GetItems(state, workflow);

In our master database there are like 650,000 items to be queried. It takes like 1 ½ minutes to load the Workbox. I had a look what happens inside the “this.GetItems(state, workflow)” method using dotpeek.

Internally it constructs the following query which took 1 ½ minutes to run in the master database (select 36 items from 650,000+ items),

SELECT *
FROM VersionedFields INNER JOIN Items ON VersionedFields.ItemId = Items.Id
WHERE ItemId IN (SELECT ItemId FROM SharedFields WHERE FieldId=Workflowengine field AND Value= workflowengine)
           AND FieldId=workflow state AND Value= workflowstate value 
ORDER BY Name, Language, Version

Is there a way to improve the performance in Workbox?

Answer 1

You can use Lucene for retrieving items in particular workflow state. First you need to ensure you're indexing standard fields by adding the following setting to the Sitecore.config:

<setting name="Indexing.IndexStandardTemplateFields" value="true"/>

then you need to rebuild the system index. Finally you can update the GetItems method:

private static DataUri[] GetItems(WorkflowState state, IWorkflow workflow)
{
    using (IndexSearchContext indexSearchContext = SearchManager.GetIndex("system").CreateSearchContext())
    {
        return indexSearchContext
            .Search(new TermQuery(new Term("__workflow state", state.StateID.ToLower())), int.MaxValue)
            .FetchResults(0, int.MaxValue)
            .Select(result => result.GetObject<Item>())
            .Where(item => item != null
                && item.Access.CanRead()
                && (item.Access.CanReadLanguage() && item.Access.CanWriteLanguage())
                && (Context.IsAdministrator || item.Locking.CanLock() || item.Locking.HasLock()))
            .Select(item => new DataUri(item.ID, item.Language, item.Version))
            .ToArray();
    }
}