Performance enhancements 3.24.2

(Peter Sheppard) #1

Is anyone able to elaborate on the Get Child Assets/Get Lineage performance enhancements mentioned in this week's newsletter?


It sounds like something has been moved around in the query, but it would be interesting to see exactly what's changed to the query.

(Chris Smith) #2

Hi,

[quote]

Is anyone able to elaborate on the Get Child Assets/Get Lineage performance enhancements mentioned in this week's newsletter?



It sounds like something has been moved around in the query, but it would be interesting to see exactly what's changed to the query.

[/quote]



Instead of being a big join:


    
    sq_ast_lnk_tree inner join sq_ast_lnk inner join sq_ast inner join sq_ast_perm left join sq_vw_ast_role


The permissions are done in a subquery in the join:
    
    sq_ast_lnk_tree inner join sq_ast_lnk inner join sq_ast,
    (permissions check query) perm_check
    where perm_check.assetid=a.assetid ....


It seems to make quite a difference with checking big trees and lots of permissions - and also we could move the 'HAVING' clause inside the permission check which cuts down on the number of ids it's grouping together and processing.

We were also able to remove a 'DISTINCT' in another part of the query and remove some extra tables in the query when it's checking shadow assets.