Solving Sitecore Desktop Performance Problem

Question

In the Sitecore DisplayStates(IWorkflow workflow, XmlControl placeholder) window (Sitecore.Shell.Applications.Workbox) in the DisplayStates(IWorkflow workflow, XmlControl placeholder) method DisplayStates(IWorkflow workflow, XmlControl placeholder) Sitecore uses the following method to retrieve items in a specific working state.

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

About 650,000 items are requested in our core database. It takes 1½ minutes to download Workbox. I looked at what happens inside the this.GetItems (state, workflow) method using dotpeek.

Internally, he builds the following query, which took 1 ½ minutes to run in the main 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 productivity in Workbox?

Answer 1

You can use Lucene to retrieve items in a specific workflow state. First you need to make sure that you index the standard fields by adding the following parameter to 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(); } }