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