How to optimize queries

Flow Production Tracking
,
1

Hi everyone,
I’m experiencing an “issue” in the company I’m working right now, basically we have a custom tool where there are some queries which are used to set this tool before launching it, in order to have a quite complete look of the show/project that an artist wants to working with.

Those queries are basically 4 in total and they are composed by:

  1. single query for retrieving all the project assets
    a. for each asset query all the tasks linked to it
  2. single query for retrieving all the project shots
    a. for each shot query all the tasks linked to it

we have even done a couple of tests where we tried to benchmarking those queries and the results are:

  1. single query for retrieving all the project assets ----> 0.24 sec
    a. for each asset query all the tasks linked to it -----> 1.8 sec
  2. single query for retrieving all the project shots ----> 0.22 sec
    a. for each shot query all the tasks linked to it -----> 18.5 sec

for a total of 20/21 seconds.

I can tell you that in the test project (the one I’ve used for testing this tool) there are 10 assets and at least 120 shots where each asset has 7 tasks and each shot has 19 tasks.

And for add a bit more specifics about code situation; the queries for retrieving the assets/shots have those filters and fields:

# assets
fields = ['code', 'id', 'sg_asset_type', 'project']
filters = [["project", "is", {'type': 'Project', 'id': project_id}]]

# shots
fields = ['code', 'id', 'sg_status_list', 'sg_sequence', 'project']
filters = [["project", "is", {'type': 'Project', 'id': project_id}]]

and the queries for the tasks have those parameters;

fields = ['content', 'id', 'entity', 'project', 'sg_status_list', 'step']
filters = [['entity', 'is', {'type': entity_type, 'id': entity_id}]]

where the entity_type and entity_id are respectively “Shot/Asset” and the “shot[‘id’]/asset[‘id’]”

So now the “issue” I was talking about is regarding the slowness of the entire process, and I would like to improve them in order to get the launch of this tool quicker.

Is there anything I can do for improve those queries? Or I have already done the possible and then we need just to wait some seconds before the tool gets the right info for set itself up?
Because at the moment what concern me are the bigger projects where we have something like 685 shots and 175 assets, and this amount of entity can be really slow.

Thanks in advance guys for your support.

2

Hello,

You should regroup your Task queries as much as possible without making them too heavy (try on for Asset Tasks and one for Shot Tasks, for example) and sort the Tasks later in python.
you can filter out all useless statuses and such things to make the queries lighter.

Cheers

3

Thank you so much @Alefeve , now it is super fast, I basically queried all the task from the entire project with the correct field I needed, then with python I could serialize all the assets + all the shots with their tasks linked.

Thanks again.