cmis query performance

[Halcyon]

Hi all, 

in these last weeks my team tried to create a cmis query to execute via java application in order to fetch "documents" from alfresco without using SOLAR index.

Why no solar? Because for business requirements, a new document uploaded by end-user need to be fetched as soon as possibile and it cant wait the 15 sec of cron SOLAR reindex.

For this reason the team try to write a query like this:

SELECT PT.* FROM
gnd:protocolloType as PT
JOIN gnd:flagCancellatoAspect as CA ON PT.cmis:objectId = CA.cm:objectId
JOIN gnd:protocolloTechnicalAspect as TA ON PT.cmis:objectId = TA.cmis:objectId
JOIN gnd:idProtocolloAspect as PA ON PT.cmis:objectId = PA.cmis:objectId
WHERE
CA.gnd:cancellato = false
and ((PT.gnd:pNdgCliente = '100015'))
and ((PT.gnd:pUfficio IS NULL ))
AND ((PT.gnd:pFlagRiservato = true AND ((TA.gnd:pUseridAssegnazione is null AND TA.gnd:pUserid = 'assistenza')
OR (TA.gnd:pUseridAssegnazione = 'assistenza')))
OR (PT.gnd:pFlagRiservato <> true
AND ((TA.gnd:pIdAreaAssegnazione in (123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144) )
OR ((TA.gnd:pIdAreaAssegnazione is null
OR TA.gnd:pIdAreaAssegnazione = 0 )
AND TA.gnd:pIdArea in (123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144) ))))
order by
PT.gnd:pDataProtocollo DESC

Of couse, the api used allow to paginated the result (10 items) but the problem is that query took about 80/90 sec to fetch the data.

How we can improve the query? Or if not possible , using solr how can redure SOLR reindex latancy without create anys system issue? (is possible have a cron time during the day and another one during the night?)

Thanks a lot for any help.

[LeoMattioli]

Hi Halcyon, if you want you can reduce the SOLR 15-seconds polling wait-time using the alfresco.cron property (docs).

The CMIS query you show, if it's executed against the DB, you should check the DB execution plan of your query: maybe you can modify some DB index to accomplish a better execution.

If you are using Alfresco Enterprise, would be worth considering to leverage Search Enterprise so every doc modification is immediately sent for indexing to Elasticsearch/OpenSearch

[cesarista]

Hi,  I would say that reducing SOLR polling won't work because it needs transactional behaviour for this query. 

It is a complicated query with several joins on aspects that includes nulls, booleans.... Are you sure that this is a query that is resolved by database ? This may depend on Alfresco version but in older versions this is only being resolved by SOLR, not database.

My recomendation is to debug and to simplify the CMIS SQL query as much as possible with CMIS workbench. Normally you don't need to join over all aspects... even types (this depends on the model). Maybe you can also attempt to make several simpler queries for obtaing the desired result. But the most important is that CMIS queries were resolved by database (transactional).

Regards.

--C.