Get help on Discord

Slow Sanity query with nested references and reverse lookups (250 objects, 20s)

6 replies
Last updated: Mar 27, 2023
I have a query that runs very slow. It returns 250 objects, but takes 20 secs.

*[_type=='event' && _id =='SOME_ID'] { 
      "participants": *[_id in ^.participants[]._ref] | order(firstName) {
        _id, 
        firstName,
        lastName,
        position,
        company->{
          _id,
          name,
          url,
          companyId,
          enonicId,
          "imageUrl": logo.asset->url,
          type,
          slotSets,
          contacts[]->{
             _id, 
            firstName,
            lastName,
            position,
            company,
            companyId,
            code,
            phone,
            email,
            "imageUrl": image.asset->url
          },
         "employees": *[_type=="participant" && company->_id == ^._id],
         uvps[]->{
            _id,
            title,
            synopsis,
            description,
            cluster,
            categories,
            references,
            trl,
            attachements[] {
                ...asset-> {
                    url,
                    "name": originalFilename
                }
            }
          },
        },
        bio,
        linkedin,
        code,
        phone,
        email,
        "imageUrl": image.asset->url,
        organizer->{
            _id,
            name,
            logo,
            url
        },
        "referedByCompany": *[_type=='company' && references(^._id)],
        role,
        notifications
      }
    }
Any ideas why?
AI Update

I can see several major performance issues in your query. With 250 objects taking 20 seconds, you're hitting multiple nested query anti-patterns that compound each other:

The main culprits:

  1. Nested *[_type=="participant"] lookup inside each company - This is doing a full collection scan for EVERY company in your results. If you have 250 participants, and each has a company, that's potentially 250 separate full table scans.

  2. *[_type=='company' && references(^._id)] for each participant - Another full collection scan per participant. That's 250 more expensive operations.

  3. Deep reference chains - You're resolving company->, then inside that contacts[]->, uvps[]->, and organizer->. Each level multiplies the query complexity.

How to fix it:

The query optimization guide specifically warns about these patterns. Here's what you should do:

Remove the nested queries entirely:

*[_type=='event' && _id =='SOME_ID'] { 
  "participants": *[_id in ^.participants[]._ref] | order(firstName) {
    _id, 
    firstName,
    lastName,
    position,
    company->{
      _id,
      name,
      url,
      companyId,
      enonicId,
      "imageUrl": logo.asset->url,
      type,
      slotSets,
      contacts[]->{
         _id, 
        firstName,
        lastName,
        position,
        company,
        companyId,
        code,
        phone,
        email,
        "imageUrl": image.asset->url
      },
      // REMOVE THIS - query separately
      // "employees": *[_type=="participant" && company->_id == ^._id],
      uvps[]->{
        _id,
        title,
        synopsis,
        description,
        cluster,
        categories,
        references,
        trl,
        attachements[] {
            ...asset-> {
                url,
                "name": originalFilename
            }
        }
      },
    },
    bio,
    linkedin,
    code,
    phone,
    email,
    "imageUrl": image.asset->url,
    organizer->{
        _id,
        name,
        logo,
        url
    },
    // REMOVE THIS - query separately
    // "referedByCompany": *[_type=='company' && references(^._id)],
    role,
    notifications
  }
}

Then make separate queries for the relationships:

// Get all employees for companies in one query
*[_type=="participant" && company->_id in $companyIds] {
  _id,
  "companyId": company->_id,
  firstName,
  lastName
}

// Get referring companies in one query
*[_type=='company' && references($participantIds)] {
  _id,
  name,
  "refersTo": array::compact(array::unique(
    participants[]->_id
  ))
}

Pass the company IDs and participant IDs you collected from the first query. This changes 250+ queries into just 3 total queries.

Additional optimizations:

  • Consider if you really need ALL those nested fields. Each -> and []-> adds overhead
  • The attachements[] projection could be simplified if you don't need every field
  • If you're displaying this in a UI, consider loading some data lazily (like UVPs) rather than upfront

This pattern of doing reverse lookups (*[references(^._id)]) inside projections is the #1 performance killer in GROQ. Always pull those out into separate queries and join them client-side.

Show original thread
6 replies
Here’s a thing you can probably take a look to optimize this: https://www.sanity.io/docs/high-performance-groq#2b35f6bb8ac2
One idea that I could think of is to separate 
participants
, 
employees
referedByCompany
, etc. and run them in parallel. You can pull them together with say 
Promise.all
Thnx. I'll check it out!
I wish you success!
It was the line

"employees": *[_type=="participant" && company->_id == ^._id],
that caused the delay. I moved that to another query, and now it works smoothly.
awesome. 😊

Sanity – Build the way you think, not the way your CMS thinks

Sanity is the developer-first content operating system that gives you complete control. Schema-as-code, GROQ queries, and real-time APIs mean no more workarounds or waiting for deployments. Free to start, scale as you grow.

Get started for freeExplore the demo

Was this answer helpful?