Any tips on improving performance of INSERT

hello! I have an edge function where one of the key tasks is inserting into the DB. It takes a long time, and was wondering if there are any tips from the community on how to make INSERT more performant. Ty

Olyno•8/29/22, 2:30 AM

Hi :vmathi: This is kinda complicated to help you without any code. Can you share us the code you're using to insert please? @rbkayz

rbkayzOP•8/29/22, 5:23 AM

Hello @Olyno. Thank you for taking time to respond

Here is the code:

const { data: newMessage, error: messageError } = await supabase
.from("tbl_message")
.insert([
{
message_id: crypto.randomUUID(),
sender_address: sender_address,
subject: subject,
parent_message_id: parent_message_id,
content: encode(encrypted_content),
salt: encode(saltexport),
iv: encode(iv),
message_hash: hashHex,
},
]);

If I run explain analyze on this query on the studio, the execution time is 1.750 ms, which is good.

However, when I run it in an edge function, this takes ~5 sec. Any idea how to speed that up please?

Olyno•8/29/22, 5:30 AM

Maybe I'm wrong, but the 5 seconds on edge functions would include the cold start, which is not the case with a direct query. Your query does almost 2 seconds. You add 3 seconds of cold start from the edge and it should give you this amount.

Unfortunately i don't think you can optimize. The only thing you can optimize is the network (nearest to your users) and the cold start of the runtime

Olyno•8/29/22, 5:30 AM

(the cold start is the time the runtime make to start itself and then your script)

rbkayzOP•8/29/22, 5:42 AM

Ok, so I figured something out. I realized I was doing multiple writes to the db sequentially. instead, i'm now trying to write it all at one go.

Can you help me with one thing @Olyno ? If i do multiple row inserts, and some fail (due to RLS), while others succeed, how can I know which ones failed, and which ones succeeded?

Olyno•8/29/22, 5:45 AM

2nd guess of me, but maybe in the messageErrormessageError variable?

rbkayzOP•8/29/22, 5:54 AM

ah ok, let me try

rbkayzOP•8/29/22, 8:21 AM

So, if there's any error in insert, it rolls back all the changes. I'm trying to setup a partial write.

Any way to reduce the cold start time? @Olyno Not that it's a big problem. just want to know

garyaustin•8/29/22, 12:47 PM

Before you conclude this is a cold start issue have you made back to back edge requests to your function to see if follow on request is faster? I just glanced on the web and don’t find quickly any clear answer on Deno cold starts, but see 1 second type numbers in a few articles, with one outlier mentioning 3.5, but with the way Supabase bundles all the needed code I would not expect 3’s.

Your explain is showing less than 2msec (not 2sec) for the insert without RLS. Explain does not include api or network time.

Each call from your Edge function to database api is going to between 100 and 200msec likely for the network time. So multiply that by number of calls in your edge function.

If you have RLS going on you could add significantly to that 2msec depending on how complex.

Any tips on improving performance of INSERT

Similar Threads

Similar Threads

Similar Threads