SQL run long time

[sysoncloud]

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, 
parentStore.identifier as parentNodeIdentifier, 
parentNode.uuid as parentNodeUuid, 
childNode.id as childNodeId, 
childStore.protocol as childNodeProtocol, 
childStore.identifier as childNodeIdentifier, 
childNode.uuid as childNodeUuid, 
assoc.type_qname_id as type_qname_id, 
assoc.child_node_name_crc as child_node_name_crc, 
assoc.child_node_name as child_node_name, 
assoc.qname_ns_id as qname_ns_id, 
assoc.qname_localname as qname_localname, 
assoc.is_primary as is_primary, 
assoc.assoc_index as assoc_index 
from 
alf_child_assoc assoc 
join alf_node parentNode on (parentNode.id = assoc.parent_node_id) 
join alf_store parentStore on (parentStore.id = parentNode.store_id) 
join alf_node childNode on (childNode.id = assoc.child_node_id) 
join alf_store childStore on (childStore.id = childNode.store_id) 
where 
parentNode.id = $1 and 
childNode.type_qname_id in ($2,$3,$4,$5,$6,$7,$8,$9,$10) 
order by assoc.assoc_index ASC, assoc.id ASC‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[afaust]

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.

[janv]

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