When collecting all datasets, for every single dataset we ran a query to determine whether the user has access to it via hierarchy. The overhead from running many SQL queries became large enough on the production instance to be the second slowest part of loading datasets.
Aim
Optimize dataset accessibility collection.
Implementation
A new query was written, which using CTEs would collect all of the user's datasets from the user's groups and join this set with the branches of the hierarchy, This query was implemented into the django permission class IsDatasetAllowed and into it's permitted_datasets static method. The clients of IsDatasetAllowed were also updated to use this new query.
Background
When collecting all datasets, for every single dataset we ran a query to determine whether the user has access to it via hierarchy. The overhead from running many SQL queries became large enough on the production instance to be the second slowest part of loading datasets.
Aim
Optimize dataset accessibility collection.
Implementation
A new query was written, which using CTEs would collect all of the user's datasets from the user's groups and join this set with the branches of the hierarchy, This query was implemented into the django permission class
IsDatasetAllowedand into it'spermitted_datasetsstatic method. The clients of IsDatasetAllowed were also updated to use this new query.