uzamaki21•7d ago

I currently run a cron job every 20 sec but its using bandwidth a lot how can i reduce the bandwidth

My cron job currently runs every 20 sec. It scrapes data from a site and inserts into the db. The db has 300 rows and 12 columns which is updated every 20 sec. My ideal situation is to be able to run the cron every 10 sec or even lower but seeing that i am doing something clearly wrong how can i optimise this behavior. I liked using convex because of the real time changes that can be seen in the UI when i change the db data after cron job updates it. Any help is appreciated.

36 Replies

erquhart•7d ago

Are you using indexes for any ctx.db.query calls?

uzamaki21OP•7d ago

im sorry im new to convex could you explain more what you mean by indexes

import { v } from "convex/values";
import { query } from "./_generated/server";

export const getNepseIndex = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db
      .query("nepse")
      .filter((q) => q.eq(q.field("name"), "Nepse Index"))
      .first();
  },
});

export const getStockBySymbol = query({
  args: { symbol: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.symbol))
      .first();
  },
});

export const getStockById = query({
  args: { id: v.id("stock") },
  handler: async (ctx, args) => {
    return await ctx.db.get(args.id);
  },
});

export const getAllStock = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db.query("stock").collect();
  },
});
import { v } from "convex/values";
import { query } from "./_generated/server";

export const getNepseIndex = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db
      .query("nepse")
      .filter((q) => q.eq(q.field("name"), "Nepse Index"))
      .first();
  },
});

export const getStockBySymbol = query({
  args: { symbol: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.symbol))
      .first();
  },
});

export const getStockById = query({
  args: { id: v.id("stock") },
  handler: async (ctx, args) => {
    return await ctx.db.get(args.id);
  },
});

export const getAllStock = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db.query("stock").collect();
  },
});

import { v } from "convex/values";
import { query } from "./_generated/server";

export const getNepseIndex = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db
      .query("nepse")
      .filter((q) => q.eq(q.field("name"), "Nepse Index"))
      .first();
  },
});

export const getStockBySymbol = query({
  args: { symbol: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.symbol))
      .first();
  },
});

export const getStockById = query({
  args: { id: v.id("stock") },
  handler: async (ctx, args) => {
    return await ctx.db.get(args.id);
  },
});

export const getAllStock = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db.query("stock").collect();
  },
});
import { v } from "convex/values";
import { query } from "./_generated/server";

export const getNepseIndex = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db
      .query("nepse")
      .filter((q) => q.eq(q.field("name"), "Nepse Index"))
      .first();
  },
});

export const getStockBySymbol = query({
  args: { symbol: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.symbol))
      .first();
  },
});

export const getStockById = query({
  args: { id: v.id("stock") },
  handler: async (ctx, args) => {
    return await ctx.db.get(args.id);
  },
});

export const getAllStock = query({
  args: {},
  handler: async (ctx) => {
    return await ctx.db.query("stock").collect();
  },
});

this is my get functions currently

erquhart•7d ago

Got it ctx.db.query() will always read the entire table unless you use an index. Filters only apply after everything is read in. Indexes are the way to limit the number of records being read, and therefore the read bandwidth.

erquhart•7d ago

You can read up on indexes here: https://docs.convex.dev/database/indexes/

Indexes | Convex Developer Hub

Indexes are a data structure that allow you to speed up your

uzamaki21OP•7d ago

ok thank you will read up on this is there a way to optimise write as well since im updating 300 rows every 20 sec

uzamaki21OP•7d ago

which looks somewhat like this

erquhart•7d ago

Not really, except ensuring you're only patching what's necessary. Honestly not sure how much of a diff that makes. If you want the data updated every 20 seconds, have to make the writes. Use indexes for all queries and then see where that puts your bandwidth

uzamaki21OP•7d ago

i wonder how indexes would help when i want to get all the data for specific row using indexes makes sense but how about in the querry to fetch all data

erquhart•7d ago

Yeah if you need to get all stocks for that query, no change needed. Just make sure it isn't being called more than once per cron.

uzamaki21OP•5d ago

the json is small

uzamaki21OP•5d ago

why is read more expensive than write

uzamaki21OP•5d ago

a single write takes about 68kb and read takes ~10mb @erquhart

jamwt•5d ago

that usually means you're missing an index...

uzamaki21OP•5d ago

hmmm the thing is im writing 304 rows to a table and reading the same thing so was wondering how index would help in reading all the rows im not filtering anything only 1 query and 1 mutation

jamwt•5d ago

gotcha what does your "Functions breakdown by project" section look like in usage? when you break down by bandwidth?

uzamaki21OP•5d ago

jamwt•5d ago

it's this page -- this is what it looks like for my team

uzamaki21OP•5d ago

jamwt•5d ago

that's function calls -- change the dropdown to bandwidth

uzamaki21OP•5d ago

sure

uzamaki21OP•5d ago

jamwt•5d ago

cool. can you paste the body of that mutateStock function? b/c yeah, that's doing a ton of reads

uzamaki21OP•5d ago

export const mutateStock = mutation({
  args: {
    stock: v.object({
      symbol: v.string(),
      name: v.string(),
      open: v.number(),
      high: v.number(),
      low: v.number(),
      close: v.number(),
      volume: v.number(),
      change: v.number(),
      percentChange: v.number(),
      ltp: v.number(),
    }),
  },
  handler: async (ctx, args) => {
    const stockRecord = await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.stock.symbol))
      .first();
    if (stockRecord) {
      return await ctx.db.patch(stockRecord._id, {
        ...args.stock,
        timestamp: Date.now(),
      });
    }
    return await ctx.db.insert("stock", {
      ...args.stock,
      timestamp: Date.now(),
    });
  },
});

export const mutateStock = mutation({
  args: {
    stock: v.object({
      symbol: v.string(),
      name: v.string(),
      open: v.number(),
      high: v.number(),
      low: v.number(),
      close: v.number(),
      volume: v.number(),
      change: v.number(),
      percentChange: v.number(),
      ltp: v.number(),
    }),
  },
  handler: async (ctx, args) => {
    const stockRecord = await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.stock.symbol))
      .first();
    if (stockRecord) {
      return await ctx.db.patch(stockRecord._id, {
        ...args.stock,
        timestamp: Date.now(),
      });
    }
    return await ctx.db.insert("stock", {
      ...args.stock,
      timestamp: Date.now(),
    });
  },
});

jamwt•5d ago

yeah! first query, no index that's your culprit so if you ever have this again, you can use this function detail view in order to zero in on what function is the offender and then if bandwidth is the issue... it's almost always a missing index we're working on tooling and lints and stuff to help point these issues out more easily they happen far more often that they should, we're not happy about that! we haven't done a good enough job here

uzamaki21OP•5d ago

so could you explain what the ideal fix could be done here cause i have exceded my convex plan and im in lockdown

jamwt•5d ago

this:

    const stockRecord = await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.stock.symbol))
      .first();

    const stockRecord = await ctx.db
      .query("stock")
      .filter((q) => q.eq(q.field("symbol"), args.stock.symbol))
      .first();

uzamaki21OP•5d ago

until this month

jamwt•5d ago

needs to change to .withIndex

uzamaki21OP•5d ago

ok i ll look into using index while filtering

jamwt•5d ago

let me ping @Indy , I think there's something we can do here when it's a temporary surge that goes away of bandwidth

uzamaki21OP•5d ago

thank you so much that would be a great help

jamwt•5d ago

https://discord.com/channels/1019350475847499849/1318662869830598706/1318662869830598706. <- @uzamaki21 there's a little thread I created with our ai tool

uzamaki21OP•5d ago

sure ill take a look thank you

Indy•5d ago

Hi @uzamaki21 usually if you have a spike on one day our system automatically forgives it, but it looks like you went over for a few days. Send us an email at support@convex.dev and we'll see if we can figure something out.

uzamaki21OP•5d ago

sure