sorting in umbraco sucks

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

David Veksler 81 posts 166 karma points

Mar 09, 2016 @ 00:37
0

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 12134 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

0

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...

Please Sign in or register to post replies

Flag this post as spam?