Drizzle Team•9mo ago

Huge Performance difference between select and query

I was wondering if there should really be a more than 250x performance difference between the same query using select with leftJoin and query

    const rows = await db
      .select()
      .from(transactions)
      .leftJoin(
        events,
        eq(events.transactionHash, transactions.transactionHash),
      )
      .where(eq(transactions.contract, contract));

    const rows = await db
      .select()
      .from(transactions)
      .leftJoin(
        events,
        eq(events.transactionHash, transactions.transactionHash),
      )
      .where(eq(transactions.contract, contract));

and

    const txn = await db.query.transactions.findMany({
      where: (t) => eq(t.contract, contract),
      with: { events: true },
    });

    const txn = await db.query.transactions.findMany({
      where: (t) => eq(t.contract, contract),
      with: { events: true },
    });

for the query we get duration: 41788.126207999994 ms, amount: 5278 and for the select + left join duration: 144.189916000003ms, amount: 5278 So the same query returns the same results but one is so much slower. Am I missing something or is this expected?

3 Replies

The Safe Fire!•9mo ago

oh i was wondering this too, i have only done the query but it felt way too slow to be legit my guess is uses json_array and i dont know how good that really is here

waterchillerOP•9mo ago

Yea I compared it to using my DB tool and it was so slow. I thought it was just a normal join and stuff behind the scenes but maybe not

The Safe Fire!•9mo ago

yeah i thought it was being cool with joins so i didnt have to

Gaming

Programming

Huge Performance difference between select and query