Perf difference on prepared statements

Hey, I made the original post on the forum and just want to add some additional details:
I think my assumption that it wasn't caching at all was incorrect. The responses just seem to be a lot slower...

For example my worker with:

await sql`select 'hello' as "message";`

await sql`select 'hello' as "message";`

usually responds in 20-50ms whereas:

await sql`select ${'hello'} as "message";`

await sql`select ${'hello'} as "message";`

usually responds in 200-300ms.

The reason I say my assumption was incorrect is the cached percentage in my hyperdrive dashboard is now showing a high percentage, so it must be something to do with how the caching is working.

I've tried with both

prepare: false

prepare: false

and

prepare: true

prepare: true

and neither seem to make a big difference to the times...

Any help here would be appreciated! My app is able to cache a large percentage of the data, so I could see some serious performance gains if I can work out what's wrong.

AJR•4/22/25, 1:15 PM

Hello! Let me take a look this morning and confirm what the differences are in actual protocol messages sent, there.

AJR•4/22/25, 1:41 PM

Okay, so there's some pretty big driver difference in behavior there. TLDR, there's two round trips in the second version. I'm surprised to see that make such a big difference though. 2x cache hit should still be <20ms or so. I want to check whether the second version is serving one of the two round trips from origin, I'm going to do that next.

The way the protocol messages break down is like so:

prepare: false

prepare: false

would disable caching in this scenario, so I'm pretty surprised to see that it didn't make a big difference

AJR•4/22/25, 2:12 PM

Ok, I see the issue. This is a bug causing cache misses on the client node on the first round trip, the PDH there. It's still serving from cache, but only on the endpoint node, which is adding a network hop for you. That explains the outsized perf difference you're observing.

I'll fix this and get it out in the next release. Thank you for bringing this to our attention.

alaisterOP•4/22/25, 2:14 PM

Wow thanks for diving into this one! Glad you found the problem

alaisterOP•4/22/25, 2:16 PM

Generally (even after this is fixed), would you recommend using prepared statements with hyperdrive?

AJR•4/22/25, 2:18 PM

It's a bit of a mixed bag. They provide extra security by allowing the database to help you in preventing SQL injection. They do have some perf overhead, though, as they aren't really designed to be helpful for a serverless-style access pattern with lots of short-lived connections.

AJR•4/22/25, 2:18 PM

Overall I think security is worth it, so I vote yes.

AJR•5/2/25, 1:17 PM

@alaister The fix for this has gone out. If you'd be up for it, would you mind confirming you're seeing better latencies on the prepared statement version, now?

alaisterOP•5/2/25, 3:51 PM

@AJR it seems to be working thanks! My site now loads in under 100ms every time! https://www.flightdeals.net/

flightdeals.net – Cheap Fares for Flexible Travelers

Find the best flight deals for your next trip