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?

Kongo

You can add an index to the SQL table on the field ItemID (if it is not already there). That might speed up things. – Kirimia

+1 well researched question. – Cordite

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

Cushat

How do you go about changing this method as it's in the Sitecore code itself? You'll have to forgive me - I'm quite a Sitecore newbie. – Hirohito

You need to change the sitecore/shell/Applications/Workbox/Workbox.xml and update CodeBeside in this file to your own class that will inherit from Sitecore.Shell.Applications.Workbox.WorkboxForm class. Then you can override whatever you need in your class. – Cushat

I faced the same issue that some clients complained the workbox is too slow to show up. I tried @MarekMusielak 's solution, but are there any measures that can check the differences? After adding the above code, some clients even said it is slower than before. The site is currently using Sitecore 6.6. And I would like to ask how to "rebuild the system index"? I can only find to rebuild search index from control panel. Shall I rebuild this? – Demaggio

I found there is a lock file in /Data/indexes/__system lucene-cb7bb949d8cbd6d1648c5f8d0b06d7bf-write.lock – Demaggio