cmis query performance , can anyone help me

[postiglionem]

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.

Regards,

M

[afaust]

In order to determine why this query takes long, you should check with your DBA with regards to the performance of the underlying SQL query. Since your query is quite complex and checks a lot of properties, it will result in many, many joins / sub-selects, and may not perform well without some DB optimisations, e.g. more available memory for caching / join handling etc. If you want to know what kind of SQL query is executed from Alfresco for this, you might want to try using P6Spy (e.g. https://www.ziaconsulting.com/developer-help/setting-p6spy-alfresco/) to log long running queries within Alfresco, to then see / check whether it takes a long time on the DB and already have a query to manually test with your DBA.

It it technically possible to shorten the tracking interval of SOLR by modifying its tracking CRON configured in solrcore.properties, but SOLR will always have a latency of at least a second (internal hard-coded offset for transaction look-up), and even if you set the cron to run every 2 seconds, that latency will always be only the lowest possible value - if there is a lot to index, it can always temporarily be longer.