All Collections
Developing with ftrack Studio
API Endpoint
Speed and Performance Tips for API Queries
Speed and Performance Tips for API Queries
L
Written by Louis Roberge
Updated over a week ago

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.

Tip: 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"])

Tip: If you’re using the Python API, you can define which projections should be used by default if none are specified.

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('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('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.

Tip: The limit clause pairs well with the offset clause to build paged queries.

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. Balance is required.

Suppose you know the id of your demo project and want to load all of its tasks; you could do this:

query = f"Task where project.id is '{project_id}'"

But that relationship could be avoided by doing the following:

query = f"Task where project_id is '{project_id}'"

Note the use of the Task's native project_id attribute rather than using the project attribute and traversing a relationship to get to the Project’s id attribute.

Depending on your use case, your query will likely be more efficient if you can structure your code so that you have on hand specific object ids that avoid you having to use a relationship.

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.

0

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.

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.

Speaking of JavaScript

JavaScript in a browser is excellent at doing things asynchronously. This isn’t strictly ftrack related but can be a great help in making your interfaces more responsive. You should, however, be wary of a few things.

  • Avoid batching queries in promise chains that are too wide or deep and resolving them with Promise.all()

  • Please don't use await with promises, as it will block waiting for results, and the whole asynchronous processing advantage will be lost.

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.

Oh! One last thing…

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!

Did this answer your question?