milk enjoyer•11mo ago

Extremely slow query: how to optimize?

I have been trying to optimize a query I use frequently in an app I am building with Convex. However, it consistently shows extremely slow query times (see attached). I have already tried to optimize as much as possible, but this speed is just unacceptable, hindering the user experience even for test users.

4 Replies

milk enjoyerOP•11mo ago

Here's the code:

import { queryWithUser } from "@/lib/utils/convex/auth";
import { zid } from "@/lib/utils/convex/convex-helpers/server/zod";
import { z } from "zod";
import { Id } from "../_generated/dataModel";
import { QueryCtx } from "../_generated/server";
import { _getPermittedOfAccount } from "../utils/rls";

export const get = queryWithUser({
  args: {
    ofAccountId: zid("ofAccounts").nullable(),
    limit: z.number().default(20),
  },
  handler: async (ctx, args) => {
    const { db, user } = ctx;

    const { ofAccountId, limit } = args;

    if (!ofAccountId) {
      return null;
    }

    const [mappedOfChats, ofAccount] = await Promise.all([
      _getMappedOfChats({ ctx, ofAccountId }),
      _getPermittedOfAccount({
        db,
        user,
        ofAccountId,
        permission: "chat",
      }),
    ]);

    if (!ofAccount) {
      return null;
    }

    const filteredOfChats = mappedOfChats.filter(Boolean);

    const sortedOfChats = filteredOfChats.sort((a, b) => {
      return a?.lastMessage?.createdAt! > b.lastMessage.createdAt! ? -1 : 1;
    });

    return sortedOfChats.slice(0, limit);
  },
});

import { queryWithUser } from "@/lib/utils/convex/auth";
import { zid } from "@/lib/utils/convex/convex-helpers/server/zod";
import { z } from "zod";
import { Id } from "../_generated/dataModel";
import { QueryCtx } from "../_generated/server";
import { _getPermittedOfAccount } from "../utils/rls";

export const get = queryWithUser({
  args: {
    ofAccountId: zid("ofAccounts").nullable(),
    limit: z.number().default(20),
  },
  handler: async (ctx, args) => {
    const { db, user } = ctx;

    const { ofAccountId, limit } = args;

    if (!ofAccountId) {
      return null;
    }

    const [mappedOfChats, ofAccount] = await Promise.all([
      _getMappedOfChats({ ctx, ofAccountId }),
      _getPermittedOfAccount({
        db,
        user,
        ofAccountId,
        permission: "chat",
      }),
    ]);

    if (!ofAccount) {
      return null;
    }

    const filteredOfChats = mappedOfChats.filter(Boolean);

    const sortedOfChats = filteredOfChats.sort((a, b) => {
      return a?.lastMessage?.createdAt! > b.lastMessage.createdAt! ? -1 : 1;
    });

    return sortedOfChats.slice(0, limit);
  },
});

const _getMappedOfChats = async (input: {
  ctx: QueryCtx;
  ofAccountId: Id<"ofAccounts">;
}) => {
  const { ctx, ofAccountId } = input;
  const { db } = ctx;
  const ofChats = await db
    .query("ofChats")
    .withIndex("by_of_account_id_and_last_read_message_id", (q) =>
      q.eq("ofAccountId", ofAccountId),
    )
    .order("desc")
    .collect();

  const mappedOfChats = await Promise.all(
    ofChats.map(async (chat) => {
      const { withUserId, lastMessageId } = chat;

      const [[withUser, avatarUrl], lastMessage] = await Promise.all([
        (async () => {
          const withUser = await db.get(withUserId);

          if (!withUser) {
            return [withUser, null];
          }
          const avatarUrl = withUser.avatarStorageId
            ? await ctx.storage.getUrl(withUser.avatarStorageId)
            : null;

          return [withUser, avatarUrl];
        })(),
        lastMessageId ? db.get(lastMessageId) : null,
      ]);

      if (!withUser || !lastMessage) {
        return null;
      }

      return { chat, withUser, lastMessage, avatarUrl };
    }),
  );

  return mappedOfChats;
};

const _getMappedOfChats = async (input: {
  ctx: QueryCtx;
  ofAccountId: Id<"ofAccounts">;
}) => {
  const { ctx, ofAccountId } = input;
  const { db } = ctx;
  const ofChats = await db
    .query("ofChats")
    .withIndex("by_of_account_id_and_last_read_message_id", (q) =>
      q.eq("ofAccountId", ofAccountId),
    )
    .order("desc")
    .collect();

  const mappedOfChats = await Promise.all(
    ofChats.map(async (chat) => {
      const { withUserId, lastMessageId } = chat;

      const [[withUser, avatarUrl], lastMessage] = await Promise.all([
        (async () => {
          const withUser = await db.get(withUserId);

          if (!withUser) {
            return [withUser, null];
          }
          const avatarUrl = withUser.avatarStorageId
            ? await ctx.storage.getUrl(withUser.avatarStorageId)
            : null;

          return [withUser, avatarUrl];
        })(),
        lastMessageId ? db.get(lastMessageId) : null,
      ]);

      if (!withUser || !lastMessage) {
        return null;
      }

      return { chat, withUser, lastMessage, avatarUrl };
    }),
  );

  return mappedOfChats;
};

I can confirm that _getPermittedOfAccount is not the problem as all the other functions that use it do not suffer from the same speed issues. My hypothesis is that the storage calls might be extremely slow, but in that case how would I optimize if I do want all the avatar pictures?

jamwt•11mo ago

End of the day for me, so I might dig in to this tomorrow. But I wanted to make sure you saw the console.time stuff we added just now in convex 1.9. https://news.convex.dev/announcing-convex-1-9/

Convex News

Announcing Convex 1.9

Big Import and Export Improvements When we released ZIP file imports and exports in Convex 1.7, it wasn't long before industrious developers started hitting the limitations. We’ve got some big improvements for you. * File storage can now be included in Snapshot Export * Snapshot import via npx convex import

jamwt•11mo ago

Could help narrow it down at least!

milk enjoyerOP•11mo ago

Ok cool, I'll try that and report back if I find something

Extremely slow query: how to optimize?

Did you find this page helpful?