Performance
The ftrack API is a powerful tool for developers to access and manipulate data stored in ftrack Studio. However, when using the API, it is crucial to ensure your requests are as efficient as possible lest your application underperforms or, worse, the scripts affect your workspace.
Over time, we have seen three large categories of inefficiencies in tooling created using the ftrack API.
- Executing many small queries which increases the time spent waiting on network latency.
- Writing complex, deeply linked queries that take more resources to compute on the server because of their interconnectedness across entity types and consequently takes much more time.
- Unfocused querying of large amounts of data leads to large datasets needing to be computed and sent over the network.
With these common pitfalls in mind, here are some tips to help you optimize your API requests for performance.
Projections
Projections refer to the attributes you want to include in the returned data set of the query you’re building. These projections live in the select portion of your query. They can be attributes of the queried entity type, or you can select across relationships to optimize the loading of related data.
Learn more about projections in our query syntax documentation.
If you know what attributes you are interested in ahead of time, you can include them in your query string as projections to fetch them immediately. This will help reduce the number of API calls you need. However, I would like to point out that you should not request any attributes you do not need. Including a large number of attributes can add unnecessary overhead to your request.
# Do not
task = session.query("Task").first()
# This next line will need to do a second API call
# to fetch the attribute data for you.
print(task["description"])
# Do
task = session.query("select description from Task").first()
# Now the task already has the description attribute loaded.
print(task["description"])
If you’re using the Python API, you can define which projections should be used by default if none are specified.
Please take a look at our documentation on default projections.
As stated, you can also query data through relationships in your projections, like so:
query = "select parent.name, description from Task"
task = session.query(query).first()
print(task["parent"]["name"], task["description"])
This query returns the name attribute of the parent object to which this Task is linked, along with the Task's description attribute.
Restrict queries to what’s important
The where clause of ftrack queries allows you to cull returned information to the records you’re interested in. This avoids having to pull down extraneous information and then having to filter it in your code, saving you some effort and avoiding wasting time downloading the extra data from the server.
For example, if you wanted to restrict the tasks you’re loading to only compositing tasks, instead of loading all your tasks and ignoring those that don’t have the right name, you could do this:
tasks = session.query('select name from Task where name is "compositing"').all()
You can also use relationships in your filtering criteria. For example, if you knew a project name and wanted to load only that project’s tasks, you could do this:
tasks = session.query('select name from Task where project.name is "demo"').all()
The above would be much more efficient than querying all tasks and filtering out those in your demo project in code.
Furthermore, you can manually limit the number of records returned to you. This strategy can be advantageous when paired with sorting, for example, to restrict records to only a specific number when building a report or for getting just one record based on a specific interesting attribute.
query = 'Project order by total_cost desc limit 5'
expensive_projects = session.query(query).all()
The above example uses a custom attribute that stores the total cost of a project and finds the top 5 most expensive projects. Using the limit clause, you avoid having to load all the projects when you’re just interested in a subset.
The limit clause pairs well with the offset clause to build paged queries.
Please take a look at our query syntax documentation.
Being Mindful of Relationships
As seen in one of the examples in the previous section, using relationships in projections and filter criteria is a powerful tool. However, each stride in a relationship is work that the server needs to do, and that can slow down the execution of your query.
Let's look at an example.
A Task has both a native project_id attribute and a project relationship. Both can be used to filter tasks based on projects, but there is a performance difference.
Using the project relationship get's us what we want:
query = f"Task where project.id is '{project_id}'"
But that relationship could be avoided by using the native project_id attribute:
query = f"Task where project_id is '{project_id}'"
Expensive Attributes
Some attributes are more expensive than others to project or use in filter criteria. For example, projections of children and parent can slow down query times due to some extra work the application must do.
Let’s say a portion of your project’s general tree structure consists of Project Season Episode Sequence Shot Task, and you are interested in loading tasks that belong to a specific season. Using this relationship with many strides in your filter criteria could be expensive to compute:
query = f"Task where parent.parent.parent.parent.id is {parent_id}"
You could do this instead:
query = f'Task where ancestors any (id is {parent_id})'
When traversing links to find related objects, it is often better to query ancestors than parent, and descendants rather than children. Furthermore, this technique is more versatile as you’re not limiting which relative level of the hierarchy you’re searching in.
When retrieving numerous attributes from multiple entities, it is possible to encounter a timeout or 502 error from the server. In such cases, you can set the limit of the query result.
Consolidate
There are multiple occasions when you can consolidate your queries. For example, let’s say you had many object ids of the same object type for which you want to load data; instead of looping through them and loading the objects, you should consolidate everything into one larger query.
Instead of doing:
# Iterate over the ids
for single_id in all_ids:
# Every iteration goes to the server to fetch the object
task = session.query(f"select description from Task where id is \"{single_id}\"").one()
# Process the object
do_something(task)
You could do:
# Build the query
# select description from Task where id in ('xxx', 'etc', '...')
all_ids_string = ', '.join(['"'+single_id+'"' for single_id in all_ids])
query = f"select description from Task where id in ({all_ids_string})"
# Run the query loading all objects and iterate over them
tasks = session.query(query).all()
for task in tasks:
# Process each object
do_something(task)
By consolidating like this, you’ll have a single round trip to the server to fetch all your information.
This same concept of consolidation can apply to your ftrack JavaScript API code, where one consolidated query also often comes with fewer promises or more shallow promise chains that are easier to resolve and troubleshoot.
In Closing
By following these tips, you can ensure that your API requests are as efficient as possible. Optimizing for performance is integral to using the ftrack API and can help you get the most out of the platform.
When creating your ftrack API session, don’t connect to the event hub if you’re not interacting with the event system. You’ll just be asking your ftrack server to do unnecessary work, which will drain resources from other scripts or users interacting with your site.
And with that, happy coding!