This problem is experienced with Alfresco 6.0 (could be in Alfresco 5.2 as well). I do not know how much the version matters to this issue.
I have a custom model defined. This mode has a custom type and it has a custom aspect with a set of properties.
We use CMIS API to communicate with the repository. Functionally, everything has been working great.
Now in order to show the information on the document objects (without the binary content), we use the following query through CMIS API. It is clear that the execution of this query through CMIS is taking long to respond.
SELECT doc.*, slo.*
FROM cmis:document AS doc
JOIN slo:documentProperties AS sloalias ON doc.cmis:objectId = sloalias.cmis:objectId
AND (sloalias.slo:mlo_link = 1451)
AND (sloalias.slo:mlo_category = 'Person')
slo:documentProperties is the custom aspect.
0a813931-e5d4-4a4a-82ad-8ccbbd1c2405 is the id of the folder under which the document would reside
Suppose we remove the calling of this query, the page loads very fast.
With this query executing, in a production system with millions of documents, it is consistently taking 20 seconds to respond.
Please note that, on my (custom) model definition, I have not done anything special with respect to indexing (the properties). So, whatever is happening must be from the default behavior.
I wonder if the improper usage IN_TREE is the cause of slowness. Because I have read somewhere saying, we can write CMIS query in such a way not to go through Solr (indexing), whether it is a good idea or not.
I also wonder if this performance issue has anything to do with other things like default auditing, or default (excessive) logging, etc.
Any help is much appreciated.