Slow query

Hey, this very simple query is running surprisingly slowly (150-300 ms):

export const getAllCategories = query({
  handler: async (ctx) => {
    const categories = await ctx.db.query('category').collect();
    return categories;
  },
});

export const getAllCategories = query({
  handler: async (ctx) => {
    const categories = await ctx.db.query('category').collect();
    return categories;
  },
});

For reference, the category table only contains 20 documents of this form:

{
  color: "#FFD9D9",
  emoji: "💼",
  enabled: true,
  slug: "career",
}

{
  color: "#FFD9D9",
  emoji: "💼",
  enabled: true,
  slug: "career",
}

10 Replies

Hmza•7mo ago

How are you calling it? I mean on frontend if you are

Michal Srb•7mo ago

Is that time shown in Convex dashboard, or time you're observing on the frontend? (the latter would be impacted by where in the world you are)

djbalinOP•7mo ago

@Hmza I'm simply calling it like this in our frontend: const categories = useQuery(api.category.queries.getAllCategories); @Michal Srb This is the time shown in the Convex dashboard logs. I also tried running the same query using the "Custom test query" tool in the dashboard as shown in the attached photo. The time shown in the dashboard logs for this invocation method is ~100 ms, still quite slow for such a simple query. I tried querying some of our other larger tables (~500-1000 documents) with the dashboard tool and simply .collect'ing them, and execution times there (as shown by logs) are ~300 ms. This is on our dev deployment btw, could that impact it? Anyway, the numbers are just very much higher right now than we are used to seeing!

sshader•7mo ago

Yeah this sounds slower than it should be. If you want to send us the deployment name (looks like happy-animal-123 and should be in the dashboard URL) either here or over DM, we can poke around on our end

djbalinOP•7mo ago

Update: Queries are still very slow in all of our development deployments + production deployment (several hundred ms for querying just a few hundred documents). They are also slow in one of our other (older) Convex projects with this dev deployment url: https://hushed-koala-696.convex.cloud. The execution time also seems to fluctuate wildly (see getAllCategories in the screenshot)

oscklm•7mo ago

Hey just tagging along on here, working with djbalin. This is a bit urgent for us, as we are about to launch to the app store end of this week. And with such a big sudden performance drop on our functions, we are actually feeling this very much when testing and using our app. Nothing has changed much in how we use convex, during the last 6-8 months other than the recent implementation of ConvexAuth, which would be very surprising to me, if it had anything to do with this. The issue has started very suddenly, never experienced any sudden performance drop on functions like this before on any other projects or deployments. Let me know if there is anything you guys need in order to help us troubleshoot this further. Love to help in anyway possible.

sshader•7mo ago

Looking into this today -- added some more tracing on our side to your deployments to see if anything shows up there. To get more info about what you're experiencing -- sounds like this is happening across multiple different deployments. Do all query functions seem slow, or is there one in particular that's slower than expected? Sounds like even for multiple calls to the same function you're getting pretty variable times? If there's a particular public function + deployment you don't mind us testing with (e.g. calling it over HTTP), that can help us investigate a little faster (instead of asking you to trigger your functions as we add more tracing / make any changes)

djbalinOP•7mo ago

Thanks @sshader. We did some more troubleshooting: strangely enough, only queries on some tables appear to run this slowly. See the screenshot: Both queries perform the exact same logic pasted below. Tested it with another table that also has good speed (sub 20ms). So it seems like its only our video table? Our video table for sure is the most complex table in our schema (lots of indexes defined), but ctx.db.get(video, videoId) should just use the index on the document ID and therefore not be affected by the size of the table?

export const getOneById = query({
  args: {
    id: v.id('video'),
  },
  handler: async (ctx, { id }) => {
    return await ctx.db.get(id);
  },
});

export const getOneById = query({
  args: {
    id: v.id('video'),
  },
  handler: async (ctx, { id }) => {
    return await ctx.db.get(id);
  },
});

export const getOne = query({
  args: {
    id: v.id('image'),
  },
  handler: async (ctx, { id }) => {
    return await ctx.db.get(id);
  },
});

export const getOne = query({
  args: {
    id: v.id('image'),
  },
  handler: async (ctx, { id }) => {
    return await ctx.db.get(id);
  },
});

sshader•7mo ago

Ooh thanks for the info. That gives me an idea. Would you mind sending me (maybe over DM) a deployment name + function that you'd be fine with me running for testing purposes? (alternatively, I can ask y'all to run these, but might be quicker to cut down the back and forth)

djbalinOP•7mo ago

Thanks sshader, I sent you a direct message ☺️

Slow query

Did you find this page helpful?