topic Re: About Transactional queries with Alfresco One in Alfresco Archive

About Transactional queries with Alfresco One

spilby — Wed, 21 Oct 2015 15:02:39 GMT

Hi,we have just upgrade to Alfresco 5.0.2 (we had 4.1.6 version) to reduce the response time of our searches, because with millions of documents it becomes too low.The main change is with the transactional queries, that search on DB instead of directly on Solr. But I need to confirm if I understand

Re: About Transactional queries with Alfresco One

afaust — Thu, 22 Oct 2015 08:07:39 GMT

Hello,

TRANSACTIONAL_IF_POSSIBLE will try to execute all your searches on the DB first but if the query is too complex to be executed there or transactional queries aren't supported (hint: optional patch), it will silently fall back to SOLR search, ensuring that users get a normal result instead of an error (result may be empty but still beats an error).

The property in alfresco-global.properties is required for the transactional query functionality (as it states in the doc) but optional for Alfresco as a whole since Alfresco will work without transactional queries (due to TRANSACTIONAL_IF_POSSIBLE). Alfresco provides all the necessary indices which you enable with that property (or rather, disable the ignoring of the indices).

There are various restrictions to what transactional metadata queries support and what not. See the documentation for details.
In general, do not use PATH, SITE, ANCESTOR, OR, any d:content, d:boolean or d:any (among others) properties in your query or it will not be executable against the DB. Also, any property checks must be expressed in a form that means "identical value check" as querying the DB does not provide the same tokenization / similarity capabilities as the SOLR index. E.g. instead of myroperty:"value" you'd have to use =myroperty:"value" and "value" must be written in the proper case the value is stored in the DB.

In my experience, transactional queries is nothing that can be easily used / understood by end users. It is more a tool for developers to ensure their technical queries can be executed even with SOLR not being available / trustworthy, e.g. due to an outage, out-of-date indices, reindexing. The transactionality requirement is also more relevant for developers than for end users.

Regards
Axel

Re: About Transactional queries with Alfresco One

spilby — Thu, 22 Oct 2015 10:06:00 GMT

Oks Axel, thanks for your responses!

Only two clarifications about it.

1. "Alfresco provides all the necessary indices which you enable with that property". These indexes, are the indexes that I specify on model.xml with index enabled=true? Ergo, if I add index enabled=true on 4 metadates, then Alfresco create these 4 indexes on DB.

If not, if are indexes only for DB and not are the same that the model indexes, I must specify on some place which metadates I want to index? I supose that Alfresco don't index all the metadates, because it's not efficient.

And I understand too that put or not index enabled=true don't affect if I use transactional queries, because these indexes only serve to SOLR, not to DB.

I must check what is faster. Search on DB, or search on SOLR a metadata indexed. Could you give me an example of how add a new index for a custom property that I add to model.xml? For example, the property doc:myDoc that I write on the next point.

2. "Do not use PATH, SITE, ANCESTOR, OR, any d:content, d:boolean or d:any (among others) properties in your query or it will not be executable against the DB".

Imagine that I create some properties like this…


 <type name="doc:myDoc">
   <title>Document</title>
   <parent>cm:content</parent>
   <properties>
      <property name="doc:level">
          <title>Level</title>               
          <type>d:text</type>
               <mandatory>true</mandatory>
                    <index enabled="true">
         <atomic>true</atomic>
         <stored>false</stored>
         <tokenised>both</tokenised>
          </index>
                </property>
           …   
        </properties>
 </type>   
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If I want to search with a query, like for example @doc\\:level:"value", this is a d:content query that don't works on DB? And I can't put the =@ to find an exact value if I want to search transactionally on DB? Maybe I don't understant it ok, sorry.

Re: About Transactional queries with Alfresco One

afaust — Fri, 23 Oct 2015 08:14:06 GMT

1) Index configuration in the model is irrelevant for transactional queries. Alfresco adds DB table indices and due to the nature of the Alfresco schema, they affect the entire table of properties. There is no way to differentiate which properties should be supported and which shouldn't. The only technical differentiation is the type of property - e.g. d:boolean are generally not supported due low efficiency of DB indices (on some DBs at least) for this type of column.

2) You can't transactionally search for d:content because the actual textual content values are not stored in the database. They are stored in the respective files on disk and it would be disastrous if the transactional query would inspect those…

Regards
Axel

Re: About Transactional queries with Alfresco One

spilby — Fri, 23 Oct 2015 12:08:32 GMT

Ok, a lot of thanks again Axel! Your responses are always useful!