better performing template queries? (for better recent docs)

[stk137]

So I am trying to create a better recent docs template.
One that has the last 15 most recently modified docs displayed in descendending order by date.  I also separate out forum posts from other content so I can display them differently.  Part of my current template is below.

The problem seems to be that this performs terribly (when there's more than handful of docs).  How can I make it perform much better?
AFAIK using lucene with search parameters is not an option in a template.  So am I stuck having to do the sorting and limiting in template script?
When you do something like this, it's not pulling back the content in the search results that I am sorting, is it?  Only meta-data right?

BTW (if it's not already requested) I'd like to be able to use lucene with search params in scripting and templating.

thx

        <#– recent docs –>
        <h2 style="margin-bottom:0.2em;">Most Recently Modified Docs</h2>
        <table cellpadding=2>
    
           <tr>
              <td></td>
              <td><b>Name</b></td>
              <td><b>Location</b></td>
              <td><b>Date</b></td>
           </tr>
           <#list space.childrenByXPath[".//*[subtypeOf('cm:content') and not(subtypeOf('fm:post'))]"]?sort_by(['properties','cm:modified'])?reverse as child>
              <#if child_index = 15><#break></#if>
                 <tr>
                    <td><a href="/alfresco${child.url}" target="new"><img src="/alfresco${child.icon16}" border=0></a></td>
                    <td><a href="/alfresco${child.url}" target="new">${child.properties.name}</a></td>
                    <td><a href="/alfresco${child.parent.url}" target="new">${child.displayPath?replace(fullSpacePath,".")}</a></td>
                    <td>${child.properties["cm:modified"]?datetime}</td>
                 </tr>
           </#list>
        </table>
               
<#– Recent Topics/Posts –>
        <#assign recentPosts=space.childrenByXPath[".//*[subtypeOf('fm:post')]"]?sort_by(['properties','cm:modified'])?reverse>
        <#if (recentPosts?size > 0) >
        <h2 style="margin-bottom:0.2em;">Most Recent Topics/Posts</h2>
                <table cellpadding=2>
                   <tr>
                      <td></td>
                      <td><b>Topic/Post</b></td>
                      <td><b>Date/Author</b></td>
                   </tr>
                   <#list recentPosts as child>
                      <#if child_index = 8><#break></#if>
                         <tr>
                            <td valign="top"><a href="/alfresco${child.parent.url}"><img src="/alfresco${child.parent.icon16}" border=0></a></td>
                            <td valign="top"><a href="/alfresco${child.parent.url}">${child.parent.properties.name}</a><br/>
                            <#– <a href="/alfresco${child.url}" target="new">${child.properties.name}</a><br/> –>
                            <#if child.content?length < 40>
                                <a style="font-size:.9em;" href="/alfresco${child.url}" target="new">${child.content?replace("<br/>","- ")}</a><br/>
                            <#else>
                                <a style="font-size:.9em;" href="/alfresco${child.url}" target="new">${child.content?substring(0,40)?replace("<br/>","- ")}…</a><br/>
                            </#if>
                            
                            </td>
                            <td valign="top">${child.properties["cm:modified"]?datetime}<br/>
                                <span style="font-size:.9em;">${child.properties["cm:modifier"]}</span>
                            </td>
                         </tr>
                   </#list>
                </table>
    
        </#if>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[kevinr]

The problem seems to be that this performs terribly (when there's more than handful of docs). How can I make it perform much better?
AFAIK using lucene with search parameters is not an option in a template. So am I stuck having to do the sorting and limiting in template script?
When you do something like this, it's not pulling back the content in the search results that I am sorting, is it? Only meta-data right?

Indeed, well it was only supposed to be an example

You can use a Lucene search to pull back docs rather than an XPath query - that is the performance issue.

You can use Lucene in a template, but you cannot use search parameters so the sorting will be done by FreeMarker - but that will unlikely be a perf issue once you remove the XPath!

Yes only meta-data will be pulled back for the sorting.

Thanks,

Kevin

[stk137]

…
You can use a Lucene search to pull back docs rather than an XPath query - that is the performance issue.

You can use Lucene in a template, but you cannot use search parameters so the sorting will be done by FreeMarker - but that will unlikely be a perf issue once you remove the XPath!
…
Kevin

well I have been converting my templates with Lucene instead of Xpath, but the performance is still unacceptable.

for example
I replaced

<#list space.childrenByXPath[".//*[subtypeOf('cm:content') and not(subtypeOf('fm:post'))]"]?sort_by(['properties','cm:modified'])?reverse as child> ‍

with

<#list space.childrenByLuceneSearch["PATH:"${space.qnamePath}//*" AND TYPE:"cm:content" AND NOT TYPE:"fm:post""]?sort_by(['properties','cm:modified'])?reverse as child>‍

I was hoping to use the template APIs date to get a timestamp before and after the query to get accurate numbers of how long stuff is taking, but using that date the timestamps are all the same (including milliseconds).  Is there a way to get at these numbers?

I'd like to see search params made available in templating/scripting, even though I am not sure even if I could move the sorting out of freemarker the performance would be acceptable.  It seems like all large queries perform poorly.

similary results here http://forums.alfresco.com/viewtopic.php?p=8977&sid=0ae441f8b824b23eda11fc335bb3431d

If this were a relational database, I'd set up an index on the cm:modified column (and all the user/permissions stuff necessary for joins in a single query as well).  Is there any optimizations I can do like this in Alfresco?

Is there some other strategy that would be better for showing the most recently modified documents, and separately special types like forum posts?

[stk137]

well I have been converting my templates with Lucene instead of Xpath, but the performance is still unacceptable.

correction
performance is significantly better with Lucene
thanks for the advice!

I had commented out an old Xpath query with an HTML comment instead of FreeMarker comment.  So no wonder it was no better, but rather a little worse.  I still haven't gotten to testing under load or with a truly huge repository, but pages are at least responding in an acceptably with what I have for now.

If there's a solution for before/after timestamps in FreeMarker, let me know.   That would have really helped me.

P.S. the naming of childrenByLuceneSearch is rather unfortunate.
I wish it really was the children of the current node instead of the whole repository.

[kevinr]

Glad to hear the performance is better - it should be.

You can optimize the query futher by removing the AND NOT clause and checking the type of each item in the freemarker template i.e. using an #IF to not render a row for items of fmost type.

P.S. the naming of childrenByLuceneSearch is rather unfortunate.
I wish it really was the children of the current node instead of the whole repository.

I totally agree - entirely my fault. An early API decision that we are now stuck with as changing it would break a lot of users templates! I will add an improved searching API (more like the JavaScript one) to the Templating API for a future version.

Thanks,

Kevin

[stk137]

….
You can optimize the query futher by removing the AND NOT clause and checking the type of each item in the freemarker template i.e. using an #IF to not render a row for items of fmost type.

…
Kevin

ok thanks
I may give that a try.

[andy]

Hi

Sort is available as part of the lucene query - it is not currenlty exposed via the JavaScript API.

You could reduce the size of your result set by adding a date range constraint: say you only pull back documents changed in the last 7 days rather than all docs.

Andy

[kevinr]

Indeed, see Date Range queries here:
http://wiki.alfresco.com/wiki/Search#Range_Queries

Kevin

[stk137]

Indeed, see Date Range queries here:
http://wiki.alfresco.com/wiki/Search#Range_Queries

Kevin

Actually I was one step ahead on that.  I hadn't posted it yet, so here's the current query for reference - in case anyone else is trying this.  (I can't be the only one looking for a better display or recent docs).  BTW Am I overlooking something, or does Freemarker not have a XML style date format builtin?

I am still considering removing the NOT and merging my special display of posts with other content as well.  I'll try to remember to post the template when I am done.

<!– 1000*60*60*24*30 = 2592000000ms = 1 month –>
<#assign endDate=date?string("yyyy\\-MM\\-dd") + "T00:00:00"/>
<#assign startDate=incrementDate(date,-2592000000)?string("yyyy\\-MM\\-dd") + "T00:00:00"/>
<#list space.childrenByLuceneSearch["PATH:"${space.qnamePath}//*" AND TYPE:"cm:content" AND NOT TYPE:"fm:post" AND @cm\\:modified:[${startDate} TO ${endDate}]"]?sort_by(['properties','cm:modified'])?reverse as child>‍‍‍‍‍‍

[kevinr]

FreeMarker does indeed appear to be missing an XML date formatter built-in. However it is easy enough to generate by hand as you have

Thanks,

Kevin