topic Re: SQL run long time in Alfresco Forum

SQL run long time

sysoncloud — Wed, 30 Aug 2017 08:28:52 GMT

Hi,I have a server running Alfresco 3.4.d community, DBA monitor one SQL run very slow, the max time it cost 3.8 minutes, how to analysis the possible bottleneck or any idea? thank you in advance.select assoc.id as id, parentNode.id as parentNodeId, parentStore.protocol as parentNodeProtocol, pare

Re: SQL run long time

afaust — Mon, 04 Sep 2017 17:54:12 GMT

This query can be inefficient / expensive in the following cases:

your database server has limited resources (e.g. for caching) and opts to do a table scan instead of running an index check
your data is extremely skewed and you have a single (few) node(s) with an extreme amount of children (i.e. too many files in a folder)

A DBA should be able to handle this, e.g. assign more resources or identify the node with an untypical amount of children.

Re: SQL run long time

janv — Tue, 05 Sep 2017 16:54:30 GMT

Hi,

For reference, which exact DB version are you using ?

We have made a number of performance improvements when listing children &/or child associations (for a given parent node) including in more recent 5.x releases.

Please also consider an upgrade to a newer version of ACS Community to benefit from these improvements. This would obviously require due planning, backup & testing (via suggested Community upgrade path from 3.x -> 4.x -> 5.x).

Regards,

Jan