With the exact same query? Using any ORM is almost always going to create slightly more unoptimised

JJames With the exact same query? Using any ORM is almost always going to create slight...

L

lucasp•11/30/24, 4:38 PM

yes i did this query

 const [total_records, users] = await Promise.all([
    prisma.user.count({
      where: whereClause
    }),
    prisma.user.findMany({
      take: limit || undefined,
      skip: page && limit ? (page - 1) * limit : undefined,
      where: whereClause,
      orderBy: sort ? getOrderBy(sort) : undefined,
      select: {
        id: true,
        first_name: true,
        last_name: true,
        role: {
          select: { name: true }
        },
        email: true,
        transportist: { select: { phone: true, status: true } },
        cargo_giver: { select: { phone: true, status: true } },
        created_at: true
      }
    })
  ])

 const [total_records, users] = await Promise.all([
    prisma.user.count({
      where: whereClause
    }),
    prisma.user.findMany({
      take: limit || undefined,
      skip: page && limit ? (page - 1) * limit : undefined,
      where: whereClause,
      orderBy: sort ? getOrderBy(sort) : undefined,
      select: {
        id: true,
        first_name: true,
        last_name: true,
        role: {
          select: { name: true }
        },
        email: true,
        transportist: { select: { phone: true, status: true } },
        cargo_giver: { select: { phone: true, status: true } },
        created_at: true
      }
    })
  ])

 const [total_records, users] = await Promise.all([
    prisma.user.count({
      where: whereClause
    }),
    prisma.user.findMany({
      take: limit || undefined,
      skip: page && limit ? (page - 1) * limit : undefined,
      where: whereClause,
      orderBy: sort ? getOrderBy(sort) : undefined,
      select: {
        id: true,
        first_name: true,
        last_name: true,
        role: {
          select: { name: true }
        },
        email: true,
        transportist: { select: { phone: true, status: true } },
        cargo_giver: { select: { phone: true, status: true } },
        created_at: true
      }
    })
  ])

 const [total_records, users] = await Promise.all([
    prisma.user.count({
      where: whereClause
    }),
    prisma.user.findMany({
      take: limit || undefined,
      skip: page && limit ? (page - 1) * limit : undefined,
      where: whereClause,
      orderBy: sort ? getOrderBy(sort) : undefined,
      select: {
        id: true,
        first_name: true,
        last_name: true,
        role: {
          select: { name: true }
        },
        email: true,
        transportist: { select: { phone: true, status: true } },
        cargo_giver: { select: { phone: true, status: true } },
        created_at: true
      }
    })
  ])

with prism findmany and selects it takes me 5 seconds and I changed it to using $queryRaw and it takes me 2 seconds which seems like quite a difference to me

Llucasp yes i did this query ```js const [total_records, users] = await Promise.all([ ...

J

James•11/30/24, 5:51 PM

You gotta check the raw query output to verify that. Probably not. Also orms spend time converting raw data to js objects.

F

Frederik•11/30/24, 8:53 PM

Hi folks, is there an example somewhere on how to use transactions with D1? (rolling back a sequence of inserts/updates if one failed)

E

esselungo•12/1/24, 2:22 AM

d1 really bills by rows written and not operations? is there no plan covering a bit more ops? i’m thinking of moving out of d1 completely for an outside db

Eesselungo d1 really bills by rows written and not operations? is there no plan covering a ...

I

Isaac McFadyen•12/1/24, 4:02 AM

What is an "operation" in this context?

I

Isaac McFadyen•12/1/24, 4:02 AM

And yes, D1 bills for rows read/written.

Original message was deleted

F

Frederik•12/1/24, 9:48 AM

Sorry I was not specific enough with what i meant with "sequence": with being able to use the result of a query/insert/update for the query in the sequence. This is not really possible with batch AFAIK

Original message was deleted

F

Frederik•12/1/24, 10:44 AM

thanks, i will try work my way around

M

Mas Inis•12/2/24, 2:22 AM

is D1 support branching (like neon, supabase, turso)?

MMas Inis is D1 support branching (like neon, supabase, turso)?

I

Isaac McFadyen•12/2/24, 2:45 AM

No. D1 has Time Travel (aka PITR, point-in-time recovery) but no branching.

IIsaac McFadyen No. D1 has Time Travel (aka PITR, point-in-time recovery) but no branching.

M

Mas Inis•12/2/24, 6:14 AM

Thanks!

IIsaac McFadyen No. D1 has Time Travel (aka PITR, point-in-time recovery) but no branching.

M

Max (@rozenmd)•12/2/24, 1:33 PM

(yet)

MMax (@rozenmd)(yet)

L

Larry•12/2/24, 1:49 PM

Is this on the roadmap or do you just like to tease us?

LLarry Is this on the roadmap or do you just like to tease us?

M

Max (@rozenmd)•12/2/24, 1:52 PM

regardless, it becomes a lot easier to create branch/preview DBs once you have infrastructure in place to rapidly spin up replicas of D1 DBs

D

dmaretskyi•12/2/24, 4:46 PM

Hello, I'm seeing high latency on a D1 database (around 1 sec RTT per write) on my workers project. The service is a simple CRUD api hooked up to D1 with Prisma. The database is located in WNAM while I'm calling the worker from Europe.

Is there anything I can do to mitigate the latency on writes? I could shard the data so that it's located closer to the user. D1 docs say that it's design to scale accross multiple smaller per-user databases? How is that achieved in practice? In my worker's TOML config I need to define each DB binding manually.

Ddmaretskyi Hello, I'm seeing high latency on a D1 database (around 1 sec RTT per write) on ...

J

James•12/3/24, 12:46 AM

i had the same concern, i ended up doing sql in the DO instead of D1 to generate and migrate new dbs on the fly per user.

MMax (@rozenmd)(yet)

I

Isaac McFadyen•12/3/24, 12:52 AM

Replicas first :lul:

Ddmaretskyi Hello, I'm seeing high latency on a D1 database (around 1 sec RTT per write) on ...

1

1984 Ford Laser•12/3/24, 1:16 AM

Durable Objects would be a far better suit here. More work to set up and get going from an architectural perspective, but far more malleable overall

M

Mitya•12/3/24, 12:35 PM

Hi. Today I got this, while developing locally with Wrangler's

--remote

--remote

--remote

--remote set to true. Should I be concerned about this happening in production, or is it just a dev env issue?

M

Mitya•12/3/24, 12:35 PM

(Restarting the server got rid of the error - only seen this once.)

MMitya Hi. Today I got this, while developing locally with Wrangler's `--remote` set to...

M

Max (@rozenmd)•12/3/24, 12:51 PM

It's a deploy-time issue (running wrangler dev --remote deploys to edge-preview) - retrying fixes it

We're in the process of refactoring the D1 API, which should reduce how often these errors show up

T

Tom•12/4/24, 12:55 PM

Hey, given that D1 is "SQLite based", and SQLite allows only one writer (AFAIK?, except when using WAL mode?) is D1 bound by this limitation aswell?

T

Tom•12/4/24, 12:57 PM

My company intends to use D1 in a system where has write-heavy burst loads (these are regional, and not "big" at all given cloudflare's scale though)

TTom Hey, given that D1 is "SQLite based", and SQLite allows only one writer (AFAIK?,...

L

lambrospetrou•12/4/24, 1:11 PM

Even with WAL mode SQLite only allows one writer at a time. WAL mode allows multiple readers regardless of the writer: https://www.sqlite.org/wal.html
D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

My company intends to use D1 in a system where has write-heavy burst loads

Without some numbers, and ideally benchmarking your exact use-case and the queries you will run yourself, nobody can confirm if this use-case is suitable for D1. What kind of bursts, how long do they last, how often, what is peak write qps, what are your queries...

Also location where you will be calling this might matter. A D1 database is in a single location (for now), so if you will be sending queries from all over the world, usual network latencies/delays apply).

T

Tom•12/4/24, 1:14 PM

Sorry for vague question, the burst would last ~10 minutes, they'd be long time apart (so no chance of colliding), queries would be simple multiple INSERT INTO (we intend to use D1 for our cart only, while having a whole other database that contains all data that's cached), 1sec for peak write qps

T

Tom•12/4/24, 1:23 PM

Write QPS might peak at like 700, and this peak might be for like 1 minute in this whole duration, then eases off slowly for the next ten minutes (I'm sure this qps is no problem for D1), our D1 location that we'll choose (western europe), and 99.9% of users are in this location aswell

Llambrospetrou Even with WAL mode SQLite only allows one writer at a time. WAL mode allows mult...

T

Tom•12/4/24, 1:39 PM

(A bit of more context behind this question: previously we used MariaDB (for just normal data and cart data), and our database performance was absolute horrendous.. eventually we found out that query cache was enabled, which is a killer in write-heavy loads. However, now we're completely rebuilding our front-end and figured we can just split out the cart into a separate database so our writes won't disturb our normal reads.)

D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

D1 is using SQLite Durable Objects, so only 1 query is executed at a specific point in time, but depending on your queries you can get hundreds/maybe low-thousands per second if they are small enough.

Alright, that's great to hear, I'm 100% we'll manage with such capacity. The queries shouldn't be overly complex, we'll probably find a lot of places where we can use BEGIN CONCURRENT too.

TTom (A bit of more context behind this question: previously we used MariaDB (for jus...

L

lambrospetrou•12/4/24, 2:00 PM

The devil is always in the details with benchmarks. I suggest you make an example workload of what these writes will be and try them in a D1 test database.
We have many times done millions of rows read per second and thousands of writes, but that's within a query. The problem with remote queries is the network between your worker and the database and everything in between. Once your query reaches the database, it's very very fast.

700 is not an unusually high number, we saw higher, but again, it depends on the specifics..

My suggestion is to make a test workload and try it out.

Llambrospetrou The devil is always in the details with benchmarks. I suggest you make an exampl...

T

Tom•12/4/24, 2:04 PM

Okay, thanks for answering! We'll definitely try benchmarking. (We have logs from previous years so we can benchmark pretty accurately luckily)

Original message was deleted

T

Tom•12/4/24, 2:05 PM

Oh, I see, thanks for mentioning that so we won't have to spend extra time on that

B

Beyondo•12/5/24, 4:17 AM

Is there any way to make D1 store 8-byte integers? It is currently screwing snowflake IDs.

9223372036854775807

9223372036854775807

9223372036854775807

9223372036854775807 ->

9223372036854776000

9223372036854776000

9223372036854776000

9223372036854776000

BBeyondo Is there any way to make D1 store 8-byte integers? It is currently screwing snow...

L

lambrospetrou•12/5/24, 9:29 AM

Are you sure it's D1 and not Javascript? JS only has 52-bit precision.
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number#number_encoding

Number.parseInt("9223372036854775807")
9223372036854776000

Number.parseInt("9223372036854775807")
9223372036854776000

Number.parseInt("9223372036854775807")
9223372036854776000

Number.parseInt("9223372036854775807")
9223372036854776000

Store those IDs as strings in D1 SQLite, or as their raw bytes in a BLOB column.

C

can•12/5/24, 9:47 AM

just a UI thing, when I want to insert some data from the UI, sometimes these buttons blocks the content like this. maybe a smol fix would be awesome

Llambrospetrou Are you sure it's D1 and not Javascript? JS only has 52-bit precision. https://d...

B

Beyondo•12/5/24, 12:23 PM

No, did not use javascript at all. It's a D1 issue. Just execute this query directly for example:

CREATE TABLE test_bigint (value BIGINT);
INSERT INTO test_bigint (value) VALUES (9223372036854775807);

CREATE TABLE test_bigint (value BIGINT);
INSERT INTO test_bigint (value) VALUES (9223372036854775807);

CREATE TABLE test_bigint (value BIGINT);
INSERT INTO test_bigint (value) VALUES (9223372036854775807);

CREATE TABLE test_bigint (value BIGINT);
INSERT INTO test_bigint (value) VALUES (9223372036854775807);

Then run

SELECT * FROM test_bigint;

SELECT * FROM test_bigint;

SELECT * FROM test_bigint;

SELECT * FROM test_bigint;

Output:

B

Beyondo•12/5/24, 12:26 PM

But yeah, you are right. I'd probably just go for strings and a whole other algorithm than Snowflake anyways. I simply thought D1 supported big integers "internally" only not in the JS API (or so it said in the docs)—I could've misinterpreted that.

BBeyondo No, did not use javascript at all. It's a D1 issue. Just execute this query dire...

L

lambrospetrou•12/5/24, 12:26 PM

The output you see is in the UI right? In the website, so it's parsing the number in Javascript. The D1 responses are also parsed in JS so this applies there too.

Llambrospetrou The output you see is in the UI right? In the website, so it's parsing the numbe...

B

Beyondo•12/5/24, 12:27 PM

Ohh that makes sense! I can't just put a number like that directly in the query then.. thank you for correcting me

J

justaname•12/6/24, 3:50 AM

does cloudflare themselves use d1 database?

Jjustaname does cloudflare themselves use d1 database?

M

Max (@rozenmd)•12/6/24, 7:28 AM

yes

N

nanfeng•12/6/24, 11:38 AM

Getting this error on simple query: Error: D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

N

nanfeng•12/6/24, 11:39 AM

Simple sql query returning 1 row from 10 rows is taking 748.00 ms, the worker's placement is changed to smart and spamming get request every 5s for more than 24 hours. the latency is still crazy high

Nnanfeng Getting this error on simple query: Error: D1_ERROR: D1 DB storage operation exc...

I

Isaac McFadyen•12/6/24, 1:32 PM

Mind sending the ID of your DB (you can put it in here, it's not a secret because someone would need your account login to use it)?

N

nanfeng•12/6/24, 1:33 PM

e7459e84-1172-4d65-9f2a-3f52cae573a2

I

Isaac McFadyen•12/6/24, 1:33 PM

Thanks

I

Isaac McFadyen•12/6/24, 1:42 PM

I've raised this internally. No timeline on a fix but assuming it's the issue I'm thinking of, it's often a simple fix.

K

Kiki Y•12/6/24, 4:27 PM

Hi team, this is Kiki from Cloudflare, let me PM the impacted user to get a ticket for further investigation.

K

Kiki Y•12/6/24, 5:12 PM

update, already created the ticket, will have further follow up through ticket then.

MMax (@rozenmd)yes

J

justaname•12/7/24, 4:18 AM

what is it used in at cloudflare or is it most things or also do they use the multi tenant way or?

J

Jadugar_Jaggu•12/7/24, 6:28 AM

Hey, why is my D1 database so slow? It's literally taking 20–30 seconds to execute an UPDATE query! sometime even minutes

With the exact same query? Using any ORM is almost always going to create slightly more unoptimised

Similar Threads

Similar Threads

Similar Threads