Optimizing ShotGrid Data Retrieval: Moving Beyond find_all()

When building custom tools for ShotGrid, the easiest way to fetch data is using the find_all() method. It works perfectly for fetching a few dozen tasks, but it becomes a ticking time bomb as your production scales. If you attempt to pull tens of thousands of assets or tasks in a single call, you risk memory exhaustion on the client-side, gateway timeouts, or hitting rate-limits on the API.

To build scalable integrations, you must shift from 'all-at-once' retrieval to paginated fetching. By manually managing limit and page parameters, you can process data in chunks, keeping your application memory low and responsive.

The Cost of Convenience

The find_all() method is a wrapper that handles the underlying API logic for you. However, it requires the server to assemble the entire result set in memory before sending it over the network. In a large environment with 50,000+ entities, this can lead to a MemoryError in your Python script or a database timeout on the server.

Beyond memory, the network payload size is a factor. If you don't specify the fields parameter, ShotGrid may return default attributes that you don't need, significantly increasing the data transferred. The first step in optimization is always whitelisting only the specific attributes required for your logic.

Implementing Cursor-Based Pagination

ShotGrid uses an offset-like approach for pagination. Instead of asking for everything, you ask for a specific limit (the number of items per page) and a page (the index of the page).

Here is a practical example of how to iterate through a large collection of Tasks across a project without crashing the script:

# Requires: shotgrid-python
import shotgrid

conn = shotgrid.Connect("your_url", "script_name", "api_key")
project_id = 123
limit = 100
page = 0
all_task_ids = []

while True:
    # Fetch a chunk of tasks
    tasks = conn.find(
        "Task",
        [["project", "is", project_id], ["status_list", "is", ""]],
        ["sg_id", "name"],
        limit=limit,
        page=page
    )

    # If no tasks are returned, we have reached the end
    if not tasks:
        break

    for task in tasks:
        # Process each task or store the ID
        all_task_ids.append(task['sg_id'])
        # Logic goes here to keep memory low

    # Move to the next page
    page += 1

print(f"Total tasks retrieved: {len(all_task_ids)}")

In this example, the script only ever holds 100 tasks in memory at any given time. Once the loop iteration finishes, the previous list is eligible for garbage collection, keeping the memory footprint flat regardless of whether you are processing 1,000 or 100,000 records.

Trade-offs and Consistency

While pagination solves the memory issue, it introduces the risk of 'phantom reads.' If a new task is created or one is deleted while your script is halfway through the pages, the offset might shift. This could cause your script to process the same record twice or skip a record entirely.

Furthermore, very high page numbers (e.g., page 5000) can suffer performance degradation. The database often has to scan through all preceding records to reach the requested offset. For massive datasets, it is often more efficient to filter by a created_at date or a specific ID range in chunks rather than relying solely on deep page offsets.

Verifying Your Implementation

To ensure your pagination logic is working correctly, perform a test with a small limit:

• Set your limit to 10.
• Run the loop and assert that each page returns exactly 10 records until the final page.
• Count total records returned and compare against a filtered find() call with no limit to confirm completeness.

For memory verification, profile a script using find_all() versus the paginated loop on a collection exceeding 5,000 entities. The paginated version should show a flat memory profile while the single-call version grows linearly with result size.