topic Re: better performing template queries? (for better recent docs) in Alfresco Archive

better performing template queries? (for better recent docs)

stk137 — Thu, 23 Aug 2007 20:39:37 GMT

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 t

Re: better performing template queries? (for better recent docs)

kevinr — Mon, 03 Sep 2007 11:07:43 GMT

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

Re: better performing template queries? (for better recent docs)

stk137 — Tue, 04 Sep 2007 21:42:37 GMT

…
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?

Re: better performing template queries? (for better recent docs)

stk137 — Tue, 04 Sep 2007 23:26:58 GMT

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.

Re: better performing template queries? (for better recent docs)

kevinr — Wed, 05 Sep 2007 09:16:01 GMT

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

Re: better performing template queries? (for better recent docs)

stk137 — Thu, 06 Sep 2007 20:56:41 GMT

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

Re: better performing template queries? (for better recent docs)

andy — Fri, 07 Sep 2007 10:25:29 GMT

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

Re: better performing template queries? (for better recent docs)

kevinr — Fri, 07 Sep 2007 12:31:24 GMT

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

Kevin

Re: better performing template queries? (for better recent docs)

stk137 — Fri, 07 Sep 2007 21:09:14 GMT

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

Re: better performing template queries? (for better recent docs)

kevinr — Mon, 10 Sep 2007 11:44:01 GMT

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

Re: better performing template queries? (for better recent docs)

stk137 — Thu, 27 Sep 2007 17:26:17 GMT

(Better Recent Docs Template continued)

well I am not done with the template because I have run into a couple problems that I haven't found solutions for. But here's where it is for now.

The current problems are
1.) child.displayPath causes UnexpectedRollbackException
http://forums.alfresco.com/viewtopic.php?t=8869&highlight=
2.) I don't of know a way to safely call child.parent.url
http://forums.alfresco.com/viewtopic.php?t=8780&highlight=
(some of that has been commented out below for now, remove those to see the full design)

(Neither or these errors occur if you're an admin or the query doesn't return any docs which pose authentication issues with getting path or parent.)

It's at least a better query/list than the example included with Alfresco. It get's the last months of changes, sorts them, and displays the top 20. The time span for the query and number of items to display can be set in assignments at the top. I also split out forum posts to link to the topic instead of the individual post. Now if the issues with displaying and linking to location are resolved I'll update to fix that.



<#ftl strip_whitespace=true />
<#setting locale="en_US"/>
<#setting datetime_format="medium_short">

<#if space?exists && space.hasPermission("Read")>

  <#assign spacePath = space.displayPath + "/" + space.name />
  <#assign fullSpacePath = space.displayPath + "/" + space.name + "/" />

  <#assign dateRangeBackMilliseconds = -1000*60*60*24*30 />
  <#assign endDate=date?string("yyyy\\-MM\\-dd") + "T00:00:00"/>
  <#assign startDate=incrementDate(date,dateRangeBackMilliseconds)?string("yyyy\\-MM\\-dd") + "T00:00:00"/>
  <#assign recentContentMaxCount = 20 />




  <#– recent content –>
  <h2 style="margin-bottom:0.2em;">Recently Created/Modified Content</h2>
  <script>var AlfNodeInfoMgr = new Alfresco.PanelManager("NodeInfoBean.sendNodeInfo", "noderef");</script>
  <table class="recordSet">
    <thead>
    <tr>
      <th style="padding:2px;text-align:left" class="recordSetHeader"><b>Content Item</b></th>
      <th style="padding:2px;text-align:left" class="recordSetHeader"><b>Location</b><#– <span style="font-weight:normal;">(${fullSpacePath})</span>–></th>
      <th style="padding:2px;text-align:left" class="recordSetHeader"><b>Date / Author</b></th>
    </tr>
    </thead>
    <tbody>
    <#list space.childrenByLuceneSearch["PATH:\"${space.qnamePath}//*\" AND TYPE:\"cm:content\" AND @cm\\:modified:[${startDate} TO ${endDate}]"]?sort_by(['properties','cm:modified'])?reverse as child>
      <#if child_index = recentContentMaxCount><#break></#if>
      <#assign rowstyle="recordSetRow" />
      <#if (child_index % 2 = 1)>
      <#assign rowstyle="recordSetRowAlt" />
      <#else>
      <#assign rowstyle="recordSetRow" />
      </#if>
      <#if child.type != "{http://www.alfresco.org/model/forum/1.0}post">
        <#– content other than forum posts –>
        <tr class="${rowstyle}">
          <td style="padding:2px;text-align:left;padding-right:10px;"><a href="/alfresco${child.url}" target="_blank"><img src="/alfresco${child.icon16}" border=0></a> <a href="/alfresco${child.url}" target="_blank">${child.properties.name}</a>
            <span onclick="AlfNodeInfoMgr.toggle('${child.nodeRef}',this);"><img id="browse:col1-img" src="/alfresco/images/icons/popup.gif" height="16" width="16" class="popupImage" /></span></td>
          <td style="padding:2px;text-align:left;padding-right:10px;">
            ${child.displayPath}
            <#–
            <#if child.displayPath=spacePath>
              <a href="/alfresco${child.parent.url}">.</a>
            <#else>
              <a href="/alfresco${child.parent.url}">${child.displayPath?replace(fullSpacePath,"")}</a>
            </#if>
            –>
          </td>
          <td style="padding:2px;text-align:left"><nobr>${child.properties["cm:modified"]?datetime}</nobr>
             / ${child.properties["cm:modifier"]}
          </td>
        </tr>
      <#else>
        <#– forum posts –>
        <tr class="${rowstyle}">
          <td style="padding:2px;text-align:left;padding-right:10px;"><a href="/alfresco${child.parent.url}"><img src="/alfresco${child.parent.icon16}" border=0></a> <a href="/alfresco${child.parent.url}">post to &lsquo;${child.parent.properties.name}&rsquo;</a>
          <span onclick="AlfNodeInfoMgr.toggle('${child.nodeRef}',this);"><img id="browse:col1-img" src="/alfresco/images/icons/popup.gif" height="16" width="16" class="popupImage" /></span></td>
          <td style="padding:2px;text-align:left;padding-right:10px;">
            ${child.displayPath}
            <#–
            <a href="/alfresco${child.parent.parent.url}">${child.parent.displayPath?replace(fullSpacePath,"")}</a>
            –>
          </td>
          <td style="padding:2px;text-align:left"><nobr>${child.properties["cm:modified"]?datetime}</nobr>
             / ${child.properties["cm:modifier"]}
          </td>
        </tr>
      </#if>

    </#list>
  </tbody>
  </table>


</#if>

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: better performing template queries? (for better recent docs)

alexandra — Sat, 29 Dec 2007 03:47:51 GMT

Thanks for a great script. I had been looking for something like this to start leveraging the metadata in the repository. I started out by modifying the example script and also noted the bad performance. It was definiately an improvment with your new one.

I wonder if you have any luck in identifying the strange error you had.

For users in Europe that prefer 24H time representation instead of AM/PM you can replace the datetime_format with something like this.

<#setting datetime_format="dd MMM yy, HH:mm">

I have a question about how to do filtering in the query…

One of the problems of Mac OS X is that it creates metadata files on the filesystem that starts with a . (dot) for example:

._example.doc

which is the metadata companion to the real document called:

example.doc

Do you happen to know how to exclude files beginning with . from the search results?

Re: better performing template queries? (for better recent docs)

kevinr — Thu, 17 Jan 2008 13:51:07 GMT

Since "." is a token separator in Lucene it will be tricky to filter by in the query. The best route is to simply test each file cm:name property to see if it beings with "." using the string comparison operators in FreeMarker.

Kevin