sorting in umbraco sucks

David Veksler 81 posts 166 karma points

Mar 09, 2016 @ 00:37

sorting in Umbraco sucks

I think this is a common scenario:

I have a site with about 40K documents. The website has a homepage which shows the most recently added content.

To show the latest content, I sort by a "publication date" property. Obviously, you want to do this in Examine, to avoid instantiating all 40,000 nodes. However, it appears to be impossible to sort by a custom property with Examine.

Here is how the Examine unit tests do it. This does not work for a custom property.

So this does not sort anything:

var latestArticles =
                ExamineManager.Instance.CreateSearchCriteria(BooleanOperation.Or)
                    .NodeTypeAlias("Article").Or().NodeTypeAlias("Feature").And().OrderBy(new SortableField("publicationDate", SortType.Double)).Compile();

Sorting the IPublishedContent list works, but this means that my homepage takes several minutes to load and requires 8GB of memory.

It seems I have no option by to bypass Umbraco entirely and do the sorting in a stored procedure.

Has anyone else dealt with this?

Copy Link

David Veksler 81 posts 166 karma points

Mar 09, 2016 @ 01:41

Here is the stored procedure version, which is unfortunately still quite slow:

CREATE PROCEDURE ArticlesGetLatest  
    @PageSize int = 100,
    @PageNumber int = 1
AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

    select 
node.id
--,node.text,node.createDate,dataDate

from umbraconode node 
inner join cmsDocument doc on node.id = doc.nodeId
inner join cmsPropertyData prop on prop.contentNodeId = node.id and prop.propertytypeid = 121  -- prop 121 is publishedDate
inner join cmsContentVersion v on v.ContentId = node.id and VersionDate = (select MAX(VersionDate) from cmsContentVersion where ContentId = node.id)
where node.parentID = 1124 AND doc.published = 1 -- parent 1121 is the articles index node
and 
prop.versionId = (select top 1 cmsContentVersion.VersionId from cmsContentVersion 
where cmsContentVersion.ContentId = node.id order by cmsContentVersion.VersionDate desc)
order by prop.dataDate desc

OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);


END
GO

Copy Link

Dave Woestenborghs 3504 posts 12135 karma points MVP 9x admin c-trib

Mar 09, 2016 @ 08:11

Hi David,

For sorting to work with examine you need to enable it in the examine configuration for it

See https://our.umbraco.org/documentation/Reference/Config/ExamineSettings/ for more information

Example :

<IndexSet SetName="CWSIndexSet"
              IndexPath="~/App_Data/CWSIndexSetTest"
              IndexParentId="-1">
      <IndexAttributeFields>
        <add Name="id" EnableSorting="true" Type="Number" />
        <add Name="nodeName" EnableSorting="true" />
        <add Name="updateDate" EnableSorting="true" Type="DateTime" />
        <add Name="writerName" />
        <add Name="path" />
        <add Name="nodeTypeAlias" />
        <add Name="parentID" />
      </IndexAttributeFields>
      <IndexUserFields>
        <add Name="headerText" />
        <add Name="bodyText" />
        <add Name="metaDescription" />
        <add Name="metaKeywords" />
        <add Name="bodyTextColOne" />
        <add Name="bodyTextColTwo" />
        <add Name="xmlStorageTest" />
      </IndexUserFields>
      <IncludeNodeTypes>
        <add Name="CWS_Home" />
        <add Name="CWS_Textpage" />
        <add Name="CWS_TextpageTwoCol" />
        <add Name="CWS_NewsEventsList" />
        <add Name="CWS_NewsItem" />
        <add Name="CWS_Gallery" />
        <add Name="CWS_EventItem" />
        <add Name="Image" />
      </IncludeNodeTypes>
      <ExcludeNodeTypes />
    </IndexSet>

Dave

Copy Link

David Veksler 81 posts 166 karma points

Mar 28, 2016 @ 18:24

Thanks Dave. That worked. Is there a benefit to using a new index set?

After some experimentation, this appears to work moderately fast:

 <IndexSet SetName="ExternalIndexSet" IndexPath="~/App_Data/TEMP/ExamineIndexes/{machinename}/External/">
    <IndexUserFields>
      <add Name="publicationDate" EnableSorting="true" />

...

 var latestArticles =
                ExamineManager.Instance.CreateSearchCriteria()
                    .ParentId((int) CmsTopLevelContentNodes.Articles)
                    .Compile();

            var umbracoHelper = new UmbracoHelper(context);
            var articlesByDate = umbracoHelper.TypedSearch(latestArticles).OrderByDescending(x => x["publicationDate"])
                    .Skip(pageSize * page)
                    .Take(pageSize);

Copy Link

Dan Diplo 1554 posts 6205 karma points MVP 6x c-trib

Mar 29, 2016 @ 14:03

Presumably you are using a cached partial so it doesn't need to perform this operation every page load?

Copy Link

is working on a reply...

This forum is in read-only mode while we transition to the new forum.

You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Flag this post as spam?

sorting in Umbraco sucks