Solve Sitecore workbox performance issue

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?

Solution

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();
    }
}